When you’re using MySQL with InnoDB for unit-testing, you may stumbled into this problems when try loading fixtures in test case.
1 | IntegrityError: (1452, ‘Cannot add or update a child row: a foreign key constraint fails (`DATABASE`.`TABLE_NAME`, CONSTRAINT `COLUMN_id_refs_id_ea62e552` FOREIGN KEY (`room_id`) REFERENCES `ANOTHER_TABLE` (`id`))’) |
You may solve this problem with this quickfix solution:
1. Change your database ENGINE into MyISAM when running unit-testing
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | options = "INNODB" import sys if ‘tests’ in sys.argv: options = "MYISAM" DATABASES = { ‘default’: { ‘ENGINE’: ‘django.db.backends.mysql’, ‘NAME’: ‘tripvillas_dev’, ‘USER’: ‘root’, ‘PASSWORD’: ‘trip’, ‘HOST’: ”, ‘PORT’: ”, ‘OPTIONS’: {‘init_command’: ‘SET storage_engine=’ + options}, }, } |
2. Using SQLITE when running unit testing
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | import sys if ‘test’ in sys.argv: import os PROJECT_PATH = os.path.dirname(os.path.abspath(__file__)) DATABASES = { ‘default’: { ‘ENGINE’: ‘django.db.backends.sqlite3’, # ‘NAME’: os.path.join(PROJECT_PATH, ‘mytesting.db’), ‘PASSWORD’: ”, ‘HOST’: ”, ‘PORT’: ”, }, } |
3. SET_FOREIGN_CHECKS=0
Yes, when you running “SET_FOREIGN_CHECKS=0;” in your MySQL, it will make INNODB ignoring foreign checks when storing new record.
1 | ‘OPTIONS’: {‘init_command’: ‘SET set_foreign_checks=0;’} |
That’s all just quickfix but not “REALLY” see what the caused. Well, actually there no problem with foreign key records in Django 1.3.
This problem may occured because incorrect position of data in fixtures. You should know that django loaddata will parse and executing your fixtures LINE BY LINE.
For instance, if you have models like :
1 2 3 4 5 | class Insurance(models.Model): company=models.ForeignKey(Company) class Company(models.Model): name=models.Charfield() |
And you have Has many to many relation models. You can export them into FIXTURES and running without problem.
But yes, the problem will came out if you have wrong ordering data, because when you load fixtures, django will read all data on each line and executed them.
This is the exported data sample (in YAML):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | – fields: company: [6d54eaa83bf34b6d9b1b34ecef069e60, c22d619375d14941a40b0749f8a9c391] model: finance.insurance pk: ‘10017060’ – fields: name: First Company model: finance.company pk: c22d619375d14941a40b0749f8a9c391 – fields: name: Second Company model: finance.company pk: 6d54eaa83bf34b6d9b1b34ecef069e60 |
When you execute or load fixtures with this way, then it will lead you to “IntegrityError: (1452, ‘Cannot add or update a child row: a foreign key constraint fails”. This is happen because once Insurance created, it will try to find Company Foreign keys.
To solve this problems, re-arrage the position:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | – fields: name: First Company model: finance.company pk: c22d619375d14941a40b0749f8a9c391 – fields: name: Second Company model: finance.company pk: 6d54eaa83bf34b6d9b1b34ecef069e60 – fields: company: [6d54eaa83bf34b6d9b1b34ecef069e60, c22d619375d14941a40b0749f8a9c391] model: finance.insurance pk: ‘10017060’ |
This this fixtures will be loaded successfully, because when Insurance created, it will get the ForeignKey. Remember, you doesn’t need to export the has many table relation at this cases.
Using “natural keys” (param –natural) to represent any foreign key and many-to-many relationship with a model that provideds a natural.
At bottom line here, Django 1.3 shouldn’t have Foreign Key problems when loading fixtures 🙂