{"id":3020,"date":"2018-03-19T19:21:04","date_gmt":"2018-03-19T15:51:04","guid":{"rendered":"http:\/\/www.serveridol.com\/?p=3008"},"modified":"2018-03-19T19:21:04","modified_gmt":"2018-03-19T15:51:04","slug":"mysql-error-1217-23000-cannot-delete-or-update-a-parent-row-a-foreign-key-constraint-fails","status":"publish","type":"post","link":"https:\/\/afaghhosting.net\/blog\/mysql-error-1217-23000-cannot-delete-or-update-a-parent-row-a-foreign-key-constraint-fails\/","title":{"rendered":"MySQL : ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails"},"content":{"rendered":"

While I\u2019ve been working with MySQL host more frequently, I\u2019d to drop a database due to storage space constraint. When I\u2019m executing the drop command,it showing an error that<\/p>\n

\n
“ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails”<\/div>\n<\/div>\n

.<\/p>\n

Some strange error but not severe since MySQL database is still working.<\/p>\n

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.<\/p>\n

Strange !! <\/strong> You may need to set SET FOREIGN_KEY_CHECKS=0;<\/strong> 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\u2019ve been executed.<\/p>\n

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.<\/p>\n

\n
\n

mysql> SET FOREIGN_KEY_CHECKS=0;
\nQuery OK, 0 rows affected (0.00 sec)
\nmysql> drop database test;
\nQuery OK, 1 row affected (0.14 sec)<\/p>\n

mysql> SET FOREIGN_KEY_CHECKS=1;
\nQuery OK, 0 rows affected (0.00 sec)<\/p>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

While I\u2019ve been working with MySQL host more frequently, I\u2019d to drop a database due to storage space constraint. When I\u2019m 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 …<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[34,36],"tags":[],"class_list":["post-3020","post","type-post","status-publish","format-standard","hentry","category-server","category-36"],"_links":{"self":[{"href":"https:\/\/afaghhosting.net\/blog\/wp-json\/wp\/v2\/posts\/3020","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/afaghhosting.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/afaghhosting.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/afaghhosting.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/afaghhosting.net\/blog\/wp-json\/wp\/v2\/comments?post=3020"}],"version-history":[{"count":0,"href":"https:\/\/afaghhosting.net\/blog\/wp-json\/wp\/v2\/posts\/3020\/revisions"}],"wp:attachment":[{"href":"https:\/\/afaghhosting.net\/blog\/wp-json\/wp\/v2\/media?parent=3020"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/afaghhosting.net\/blog\/wp-json\/wp\/v2\/categories?post=3020"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/afaghhosting.net\/blog\/wp-json\/wp\/v2\/tags?post=3020"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}