Hello, this has been annoying me all day - any suggestions welcome. I am receiving an IntegrityError from web2py when trying to insert a record into a table named Foods. The table has a foreign key to Recipes, but I would like to add a row without a recipe_id.
I receive this sql error: <class 'gluon.contrib.pymysql.err.IntegrityError'>((1452, u'Cannot add or update a child row: a foreign key constraint fails (`pymeals`.`foods`, CONSTRAINT `foods_ibfk_1` FOREIGN KEY (`recipe_id`) REFERENCES `recipes` (`id`) ON DELETE CASCADE)')) This is my table +-----------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(255) | YES | | NULL | | | recipe_id | int(11) | YES | MUL | NULL | | +-----------+--------------+------+-----+---------+----------------+ I have defined the tables in web2py using the following code: db.define_table('recipes', Field('name') ) db.define_table('foods', Field('name'), Field('recipe_id', db.recipes, requires=None, default=None) Traceback (most recent call last): File "c:\web2py\gluon\restricted.py", line 192, in restricted exec ccode in environment File "c:/web2py/applications/pymeals/controllers/foods.py", line 14, in <module> File "c:\web2py\gluon\globals.py", line 137, in <lambda> self._caller = lambda f: f() File "c:/web2py/applications/pymeals/controllers/foods.py", line 10, in add return dict(form=crud.create(db.foods)) File "c:\web2py\gluon\tools.py", line 3120, in create formname=formname, File "c:\web2py\gluon\tools.py", line 3061, in update detect_record_change = self.settings.detect_record_change): File "c:\web2py\gluon\sqlhtml.py", line 1205, in accepts self.vars.id = self.table.insert(**fields) File "c:\web2py\gluon\dal.py", line 4703, in insert return self._db._adapter.insert(self,self._listify(fields)) File "c:\web2py\gluon\dal.py", line 838, in insert raise e IntegrityError: (1452, u'Cannot add or update a child row: a foreign key constraint fails (`pymeals`.`foods`, CONSTRAINT `foods_ibfk_1` FOREIGN KEY (`recipe_id`) REFERENCES `recipes` (`id`) ON DELETE CASCADE)') I suspect the problem to be that it tries to insert 0 into recipe_id. The recipes table is empty at the moment, so that obviously doesn't exist. When I add a recipe with id=0, I don't receive any errors. When I remove the recipe with id=0 then the error returns. Function argument list (self=<gluon.dal.MySQLAdapter object at 0x030F1970>, table=<Table {'ALL': <gluon.dal.SQLALL object at 0x030...>, 'id': <gluon.dal.Field object at 0x030FDB90>}>, fields=[(<gluon.dal.Field object at 0x030FDB70>, 0), (<gluon.dal.Field object at 0x030FDA10>, 'Lettuce')]) I also attempted to get help here, but so far, no responses :( http://stackoverflow.com/questions/6635428/integrityerror-when-adding-null-value-to-foreign-key-using-web2py