3

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.

Cerin
  • 60,957
  • 96
  • 316
  • 522

3 Answers3

3

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` ;
ghbarratt
  • 11,496
  • 4
  • 41
  • 41
0

Try to delete the Foreign key constraints from your table.

try to do this as shown in image u could see that there are foreign key constraints in yellow shading

just right click on that key section and delete the foreign key like this. inside red section the image and after that click okk to delete foreign key and that's it now you can delete the column by using simple AlTER table table_name drop column column_name;

thank you.

Rahul Gupta
  • 23
  • 7
0

I'd try dropping the foreign key constraint first

you might also want to try the command "show engine innodb status" - it may indicate if there is some leftover problem from when the tables had data.

Mike Sokolov
  • 6,914
  • 2
  • 23
  • 31