Disabling MySQL Foreign Key Constraints Checks
August 27th, 2009
No comments
Today I had to update lots of rows in a table with a lot of foreign key constraints. The first try was to delete all records and insert the updated data resulting in:
Cannot delete or update a parent row: a foreign key constraint fails (`foo/bar`, CONSTRAINT `FKED8DCCEF810F075A` FOREIGN KEY (`widget_id`) REFERENCES `widget` (`id`))
Instead of dropping all constraints, inserting the data and adding them again afterwards, there’s another more convenient method:
You can disable the foreign key checks with:
SET FOREIGN_KEY_CHECKS = 0
Enabling them again afterwards:
SET FOREIGN_KEY_CHECKS = 1