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
