How to temporarily disable Foreign Key Checks or Constraints in MySQL

While importing a database that has tables with foreign keys defined we get to see error “#1452 – Cannot add or update a child row: a foreign key constraint fails“.

Even while trying to delete some tables from such a database, we come across situation where we face foreign key constraint error: “#1217 – Cannot delete or update a parent row: a foreign key constraint fails“.

To avoid such errors first disable the foreign key checks, then do your job of importing the database or deleting the table and finally enable the foreign key checks.

In case of database import, you can add the command to disable the foreign key checks at the top of your .sql import file and the command to enable the foreign key checks at the end, save the file and import it.

 

The sql command to disable foreign key checks is:


mysql>  SET FOREIGN_KEY_CHECKS = 0;

 

To  enable the foreign key checks use the following command:


mysql> SET FOREIGN_KEY_CHECKS = 1;

Leave a Comment

Back to top