How to drop tables with cyclic foreign keys in MySQL
I have two tables, Parent and Child. The column Parent.favorite_child has a foreign key constraint pointing to Child.id. And the column Child.parent has a foreign key constraint pointing to Parent.id.
Now here's the weird part. I'm trying to drop my Child table, which contains no records, but MySQL is giving me the error:
ERROR 1217: Cannot delete or update a parent row: a foreign key constraint fails SQL Statement: drop table `mydatabase`.`Child`
Why is MySQL throwing this error? There are literally no records in the Child table with which anything could be pointing to or from.
You need to first drop the foreign key on the parent table before you can delete the child table:
ALTER TABLE `Parent` DROP FOREIGN KEY `Parent_ibfk_1` ;
- A Linux, Apache, MySQL, PHP (LAMP) stack is a combination of open source software that is typically installed together to enable a server to host dynamic […]
- MySQL is an the most popular open-source database management system, in most of hosting company its has been bundle by LAMP(Linux, Apache, MySQL, PHP/Python/Perl) stack. Another alternative […]
- MySQL is the world’s most popular open-source database. Despite its powerful features, MySQL is simple to set up and easy to use. Below are some instructions […]