Django "Cannot add or update a child row: a foreign key constraint fails"

Question

I have a model Coupon, and a model Photo with a ForeignKey to it:

class Photo(models.Model):
    coupon = models.ForeignKey(Coupon,
                               related_name='description_photos')
    title = models.CharField(max_length=100)
    image = models.ImageField(upload_to='images')

I set up inlines in the admin so now I'm able to add photos to a coupon from the admin.

I attempt to add one, and upload is successful, but then I get Django's debug page with this error:

IntegrityError at /admin/coupon/coupon/321/
(1452, 'Cannot add or update a child row: a foreign key constraint fails (`my_project`.`coupon_photo`, CONSTRAINT `coupon_id_refs_id_90d7f06` FOREIGN KEY (`coupon_id`) REFERENCES `coupon_coupon` (`id`))')

What is this and how can I solve this problem?

(If it matters, this is a MySQL database.)

EDIT: I tried it on an Sqlite3 database that has a slightly different dataset, and it worked, so perhaps there's loose data in my current DB? How can I find it and delete it?

I found that south created a new table as innodb but all my other tables are myisam. LOL, will know for next time ;) — rix, Apr 08 '14 at 16:43

score 86 · Accepted Answer · answered May 30 '11 at 19:39

86

Some of my tables were in InnoDB and some were in MyISAM... I changed everything to MyISAM and the problem was solved.

answered May 30 '11 at 19:39

Ram Rachum

84,019
84
236
374

3

Argh!!! One hour of my life wasted because of this error... and because of not finding this answer soon enough. Thank you! – webjunkie Jun 25 '12 at 15:55
This happened to me too! However---I'm still at a loss as to why some were created with MyISAM and other InnoDB. Do you know how this can happen? – jkeesh Mar 11 '13 at 21:37
5

Use `SHOW CREATE TABLE mytable;` for checking database engine. – Seppo Erviälä Mar 26 '13 at 16:08
I have the same error and follow your answer I check my DB all my tables are in innoDB but I still got this error. Does any one here have the same problem like I do? – gacon Oct 30 '13 at 09:33
1

`ALTER TABLE t1 ENGINE = InnoDB;` to change the engine – Pierre de LESPINAY Jan 02 '14 at 11:50

score 85 · Answer 2 · answered Mar 14 '12 at 10:08

85

DATABASES = {
'default': {
    ...         
    'OPTIONS': {
         "init_command": "SET foreign_key_checks = 0;",
    },
 }
}

(According to the official doc) In previous versions of Django, fixtures with forward references (i.e. relations to rows that have not yet been inserted into the database) would fail to load when using the InnoDB storage engine. This was due to the fact that InnoDB deviates from the SQL standard by checking foreign key constraints immediately instead of deferring the check until the transaction is committed. This problem has been resolved in Django 1.4.

answered Mar 14 '12 at 10:08

mmrs151

3,924
2
34
38

6

Thank you so much! I had this problem and was baffled. Im using Django 1.5 with INNODB and South, and I didnt want to hand-change schema stuff! – JREAM Aug 09 '13 at 15:40
This is really weird. I had the same error but only when running the tests and I am using Django 1.8. – Sardathrion - against SE abuse Aug 14 '15 at 06:53
1

Worked for me on Django 1.8. Thanks. – Tim S. Oct 01 '15 at 00:15
Same error with ManyToMany relation in a django 3.2 innodb – BugliL Mar 15 '22 at 16:53

score 18 · Answer 3 · answered Sep 27 '11 at 11:52

To avoid this happening what you can also do is set your STORAGE_ENGINE in your settings.py

for django >= 1.2

DATABASES = {
    'default': {
        ...
        'STORAGE_ENGINE': 'MyISAM / INNODB / ETC'
    }
}

for django <= 1.2

DATABASE_STORAGE_ENGINE = "MyISAM / INNODB / ETC"

Please note this is only valid for MySQL

GrvTyagi · Answer 4 · 2018-03-23T02:14:58.043

8

Sometime the reason for this error is trying to save child table first and then save parent.
The solution for this is using.

DATABASES = {
 'default': {
  ...         
 'OPTIONS': {
     "init_command": "SET foreign_key_checks = 0;",
     },
  }
}

2). Check your database operation flow and make it parent ---> child

edited Mar 23 '18 at 02:14

answered Apr 13 '15 at 17:49

GrvTyagi

4,231
1
33
40

how to make it parent ---> child – Henning Lee Dec 03 '19 at 23:12

score 6 · Answer 5 · answered Apr 16 '12 at 16:42

I ran into this same issue: mmrs151's solution works, but NB that, for Django <= 1.2 (i.e. before multiple database support), the setting looks like this:

DATABASE_OPTIONS = {"init_command": "SET foreign_key_checks = 0;"}

Worth noting that Ram Rachum seems to have worked around rather than solved the problem: MyISAM doesn't support transactions at all...

score -4 · Answer 6 · answered Jul 11 '13 at 12:01

-4

Another option is to drop the contraint in your MySQL table:

alter table <TABLE_NAME> drop foreign key <CONTRAINT_NAME>;

answered Jul 11 '13 at 12:01

Nikolay Georgiev

1,047
1
12
22

Django "Cannot add or update a child row: a foreign key constraint fails"

6 Answers6

Linked