Activities

November 2016
M T W T F S S
« Aug   Jan »
 123456
78910111213
14151617181920
21222324252627
282930  

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)

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>