Toggling foreign key checks in MySQL

Recently in my project, we needed to import database dumps of a legacy database in MySQL, which was involved in a nested relationship where one tuple could be the parent record of another tuple. We created the appropriate domain structure and checked that the foreign key references were created accordingly. All well and good.

So we started to restore the dump for the database. But soon problems started arising when certain records had references to records that were not yet created i.e, that is some records were being assigned parents that did not exist. We needed a way to turn of foreign key checking for some time. So after taking a look at the MySQL documentation (http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html), I stumbled across a way to turn off foreign key checking by running the following command:

[code]mysql> SET FOREIGN_KEY_CHECKS = 0
mysql> SOURCE fakeDB
mysql> SET FOREIGN_KEY_CHECKS = 1[/code]

So there it was. We had happily imported our legacy database with all the foreign key checks intact.

If you’re not sure about the status of foreign key checking in your MySQL instance, you can use the following command to check the status:
[code]mysql> select * from INFORMATION_SCHEMA.GLOBAL_VARIABLES where VARIABLE_NAME like ‘FOREIGN_KEY_CHECKS’;[/code]

Hope this helps.