After upgrade to 2.8.2: <class 'sqlite3.IntegrityError'> foreign key constraint failed

[Scott Hunter]

I have a site which, after upgrading to 2.8.2 (and clearing out the sessions), when I try update a record via a smartgrid or via admin, I get the error in the title.  I tried the same action on the copy of the site I had made just before upgrading, without any problems.

This is from OSX 10.9.

[Paolo Valleri]

Web2py 2.8.2 comes with foreign_keys=ON enabled by default (see http://www.sqlite.org/foreignkeys.html)

If you get the issue you mentioned I guess you are violating a foreign key constrain.

Please post your model (or the tables definitions involved) so that we can figure out what is wrong

Paolo

[Scott Hunter]

Aha -- I had reference fields, one of which was empty, and they used the default validator, which does not allow for that.  I specified the validators for those fields, and that did the trick.  

Shouldn't this have been reported as an error in the form ("Field Required", or somesuch), and not have generated a ticket?

And shouldn't the fact that this is a new requirement for reference fields (or at least a new default setting) be included in the release notes for this version?

Thanks!

[Vch H]

I have  this problem too.

Where I should specified validators?

[Scott Hunter]

This was how I modified one of the "offending" Field definitions:

    Field('f_club2', type='reference t_club',
          requires=IS_EMPTY_OR(IS_IN_DB(db,db.t_club.id,"%(f_desc)s")),
          label=T('ClubCode2')),

Basically, I wrapped a IS_EMPTY_OR around what the manual says is the default validator.

[Alex Glaros]

Is there any indicator regarding which exact foreign key constraint it is, or do I have to check each table and field in the problem controller?

thanks,

Alex Glaros

[Vch H]

How  I can change "foreign_keys=ON" to "OFF" for some application?

[Loïc]

The workaround for me was to specify adapter_args=dict(foreign_keys=False) in the DAL connection.
For example :
db = DAL('sqlite://storage.sqlite', pool_size=1, lazy_tables=True, migrate=True, adapter_args=dict(foreign_keys=False))

[Loïc]

Hi Scott

I don't fully understand you answer. Could you please show us what was your buggy code, and how you solved it? I'm still stuck with this problem...

Thanks a lot