3

I am getting a weird error. I have a table animals, that I am trying to drop. I cannot drop the table because of a foreign key constraint fails. I know that I must drop the foreign keys before I drop the table. The problem is this table has no foreign keys. I have already dropped them. SQLyog reflects that they have been dropped. But I can still find them in the info schema. It as if I drop the key half way or something.

How can I drop this table without dropping the database? I need to rekey this table and I just want to drop it and recreate it.

EDIT: Here is the error message from SQL:

Cannot delete or update a parent row: a foreign key constraint fails
DoNotArrestMe
  • 1,285
  • 1
  • 9
  • 20
wesleywmd
  • 605
  • 1
  • 6
  • 20

1 Answers1

16

It doesn't matter if your table animals has foreign keys or not. You would be able to drop the table anyway if it had foreign keys.

What matters is that there are other tables with foreign keys referencing animals.

Here's how you can check:

SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE (REFERENCED_TABLE_SCHEMA, REFERENCED_TABLE_NAME) = ('mydatabase', 'animals');

Re your comment:

I've never heard of a case where foreign keys were invisible but still preventing the table from being dropped. But I suppose it's possible.

You can drop your table if you do it this way:

mysql> SET FOREIGN_KEY_CHECKS=0;
mysql> DROP TABLE animals;
mysql> SET FOREIGN_KEY_CHECKS=1;

However, I can't predict what may go wrong if the InnoDB tablespace is in a weird state already.

Ultimately, what you may have to do to clean this up is to dump all your InnoDB tables using mysqldump, then shut down the MySQL daemon, remove the InnoDB tablespace files, and then restart and reimport your dumped data. That will eliminate any possibility of tablespace corruption.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • I get this concept. The weird part is that the query you provided (ran a very simple one before posting) returns an empty set. As if there are not dependencies. I am still not able to drop the table. – wesleywmd Feb 03 '14 at 19:54
  • Must have been a corruption some where. I dumped, shut down, restarted , and imported just like you described and I am now able to drop the table. i am no stranger to mysql so I understand how it is suppose to work but this threw me through a loop. Thank you. – wesleywmd Feb 03 '14 at 20:56