WJBlog

Disabling Foreign Key Constraints When Loading MySQL Data

MySQL’s InnoDB storage engine supports the use of foreign key constraints, which are very useful but require you to manage your data in a much more rigorous manner than might otherwise be employed when not taking advantage of this feature. For instance, when migrating data from a database which does not use foreign keys to one which does, you’ll often encounter an error in which MySQL complains about a foreign key constraint not being met. This is easily resolved by temporarily disabling foreign key checks before beginning the import, and then re-enabling them when the import is complete.

To disable foreign key checks, add the following line to the top of your import file:

SET foreign_key_checks = 0

You’ll of course want to re-enable foreign key constraint checks following the import, so add the following line to the end of your import file:

SET foreign_key_checks = 1