I'm getting an error after adding a foreign key field "Cannot add or update a child row: a foreign key constraint fails"

[Jumpfroggy]

I have a model:

class Contact(Model):
    name = CharField(max_length=1000)
    phone = CharField(max_length=100, blank=True)
    email = EmailField(max_length=1000, blank=True)

This has been working fine.  Now I add a foreign key field to the auth User model.

    staff_worker = ForeignKey(User, related_name='contacts', null=True, blank=True, default=None)

And I create & apply the migration
    
    python manage.py schemamigration appname new_migration_name
    python manage.py migrate

If I go into the admin editor and save the model with staff_worker == None, it works.  However, if I set a staff worker to a user in my system and save, I get this:

    (1452, 'Cannot add or update a child row: a foreign key constraint fails (`appname`.`appname_contact`, CONSTRAINT `staff_worker_id_refs_id_7672fcdc` FOREIGN KEY (`staff_worker_id`) REFERENCES `auth_user` (`id`))')
    
Here's the debug log from when south created the migrations:

    DEBUG south execute "ALTER TABLE `appname_contact` ADD COLUMN `staff_worker_id` integer NULL;" with params "[]"
    DEBUG south execute "ALTER TABLE `appname_contact` ADD CONSTRAINT `staff_worker_id_refs_id_7672fcdc` FOREIGN KEY (`staff_worker_id`) REFERENCES `auth_user`(`id`);" with params "[]"
    DEBUG south execute "CREATE INDEX `appname_contact_4f345f81` ON `appname_contact` (`staff_worker_id`);" with params "[]"
    DEBUG south execute "SET FOREIGN_KEY_CHECKS=1;" with params "[]"
    DEBUG south execute "ALTER TABLE `appname_contact` ADD COLUMN `staff_worker_id` integer NULL;" with params "[]"
    DEBUG south execute "ALTER TABLE `appname_contact` ADD CONSTRAINT `staff_worker_id_refs_id_7672fcdc` FOREIGN KEY (`staff_worker_id`) REFERENCES `auth_user`(`id`);" with params "[]"
    DEBUG south execute "CREATE INDEX `appname_contact_4f345f81` ON `appname_contact` (`staff_worker_id`);" with params "[]"

Does anyone have an idea why this is failing?  Thanks.

I'm running on Python 2.6.5, Django 1.2.4, South 0.7.3, Mysql 5.1.17.

[Andrew Godwin]

Sorry about the delayed response here, I've been really busy recently.

So, the error is saying that there's no staff worker with the ID you
specified. I presume you're using the admin interface, which means that
it's likely there _is_ a staff worker with the right ID - are you also
using the InnoDB backend to MySQL?

It would also be worth seeing what records are in the staff worker
table, and what the query Django is trying to run is (including the
parameters) - if you're in debug mode, you can usually get this from
"Local Vars" in the last few frames in the traceback.

Andrew

[Jumpfroggy]

No problem Andrew, thanks for the belated response. I ran into the
problem again with a different model, so I'll post the relevant data
for this new example:

(For those tuning in, please ignore the code from my first post)

models.py:

class Contact(Model):
...

class Questionaire(Model):
contact = OneToOneField('Contact',
related_name='questionaire')
...

forms.spy:

class QuestionaireForm(forms.ModelForm):
class Meta:
model = models.Questionaire

views.py:

form = QuestionaireForm(request.POST)
if form.is_valid():
questionaire = form.save(commit=False)
questionaire.contact = contact
questionaire.save()

This causes the following exception:

IntegrityError at /admin/questionaire/154

(1452, 'Cannot add or update a child row: a foreign key constraint

fails (`[app name]`.`[app name]_questionaire`, CONSTRAINT
`contact_id_refs_id_27ceb291` FOREIGN KEY (`contact_id`) REFERENCES
`[app name]_contact` (`id`))')


Picked out of the trace:

[virtalenv folder]\lib\site-packages\django\db\backends\mysql
\base.py in execute

79. codes_for_integrityerror = (1048,)
80.
81. def __init__(self, cursor):
82. self.cursor = cursor
83.
84. def execute(self, query, args=None):
85. try:
> 86. return self.cursor.execute(query, args) ...
87. except Database.IntegrityError, e:
88. raise utils.IntegrityError, utils.IntegrityError(*tuple(e)),
sys.exc_info()[2]
89. except Database.OperationalError, e:
90. # Map some error codes to IntegrityError, since they seem to
be
91. # misclassified and Django would prefer the more logical
place.
92. if e[0] in self.codes_for_integrityerror:

Local vars:

Variable Value
args (154, [other values])
e IntegrityError(1452, 'Cannot add or update a child
row: a foreign key constraint fails (`[app name]`.`[app
name]_questionaire`, CONSTRAINT `contact_id_refs_id_27ceb291` FOREIGN
KEY (`contact_id`) REFERENCES `[app name]_contact` (`id`))')
query 'INSERT INTO `[app name]_questionaire` (`contact_id`,
[other fields]) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'
self <django.db.backends.mysql.base.CursorWrapper object
at 0x01667B50>

And looking though mysql manually:

> python manage.py dbshell

mysql> select count(*) from [app name]_contact where id = 154;
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from [app name]_questionaire;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)

mysql> INSERT INTO `[app name]_questionaire` (`contact_id`, [other
fields]) VALUES (154, [other values]);
ERROR 1452 (23000): Cannot add or update a child row: a foreign
key constraint fails (`[app name]`.`[app name]_questionaire`,
CONSTRAINT `contact_id_refs_id_27ceb291` FOREIGN KEY (`contact_id`)
REFERENCES `[app name]_contact` (`id`))
mysql>

So attempting that insert statement (which looks correct to me) causes
the error when input directly to mysql. Either a) there's a bug in
south creating constraints, b) there's a bug in mysql, or c) I don't
really know what I'm doing and screwed up here.

Any help is appreciated. Thanks!

As a quick fix, I might drop the constraint just to avoid the mysql
error.

[Andrew Godwin]

Never discount option b) when working with MySQL, I've it happen to me
before...

That constraint _does_ look valid to me. I'd suggest you try the same
thing on a fresh database, using syncdb --all to force even migrated
apps to use syncdb, and not south, and see if it fails there.

If it does, it's Not My Problem. If it works, as it probably will do, it
would be great if you could get the description of the same/similar
constraint in the new database, post both it and the old one back here,
and we can ponder what's going on.

Andrew

[Jonathan Hartley]

You have elided the [app_name] in the above code, but for me, a very similar sounding problem occurred precisely because this app_name was wrong.

A model M was moved from app A to app B.

On a column which referenced model M as a foreign key, South added a new foreign key constraint referencing the new B_M table, but didn't remove the old constraint referencing the A_M table.

I'm currently trying to figure out whether I should fix this by adding a new migration to simply delete that one erroneous constraint.

[James]

Jonathan,
That could very well be it. I know at one point I've split one django
site into two. Since it was so long ago, I can't remember off the top
of my head if this is the situation here, but I'm guessing it is.
That actually makes a lot of sense, since dropping some constraints
"fixes" the issue.

It seems like renaming the constraints would be the best fix, since
then everything would be as south expects it to be. Might be a simple
python/SQL script to do this.

I'm far away from this problem now, having simply dropped the
constraints and moved on. But this will be really useful for the next
time I run into it.

And if anyone is still running into it, this would be my strategy for fixing it:
-find a way to list all constraints in mysql
-compare those to the naming scheme that south expects to see if the
wrong app_name is being used
-make a script that will rename all constraints with the old app_name to the new

-James