MySQL : ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

While I’ve been working with MySQL host more frequently, I’d to drop a database due to storage space constraint. When I’m executing the drop command,it showing an error that

“ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails”

.

Some strange error but not severe since MySQL database is still working.

Solution : This is due to foreign key reference. Few of the tables in test database was linked with other table on the other database. So MySQL engine will not allow us to drop the database and throws me an exception.

Strange !! You may need to set SET FOREIGN_KEY_CHECKS=0; and will be able to drop the database. What this command will do is, it will disable the foreign keys checks against the query we’ve been executed.

Note : Do not keep the SET FOREIGN_KEY_CHECKS=0; in any of production server. I execute this query on off peak hours to minimize the impact and set it to enabled once after the database was removed.

mysql> SET FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)
mysql> drop database test;
Query OK, 1 row affected (0.14 sec)

mysql> SET FOREIGN_KEY_CHECKS=1;
Query OK, 0 rows affected (0.00 sec)

نوشته های مشابه