Cannot delete or update a parent row: a foreign key constraint fails

There may be situation where you are unable to drop tables or whole database in MySQL due to existing foreign keys. In another words, you run ‘Drop table tableName’ or ‘Drop database dbName’ and you receive the following error:

Cannot delete or update a parent row: a foreign key constraint fails

The trick to delete the table or database is to first disable foreign key constrain check as following:

SET foreign_key_checks = 0;
-- Drop tables
drop table ...
-- Drop views
drop view ...
SET foreign_key_checks = 1;

This way, by setting ‘foreign_key_checks’ to zero turns off foreign key constrains check. After dropping tables or database, make sure to turn back foreign key check

Leave a Reply

Your email address will not be published. Required fields are marked *