Bogus foreign key constraint fail
On demand, now as an answer...
When using MySQL Query Browser or phpMyAdmin, it appears that a new connection is opened for each query (bugs.mysql.com/bug.php?id=8280), making it neccessary to write all the drop statements in one query, eg.
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE my_first_table_to_drop;
DROP TABLE my_second_table_to_drop;
SET FOREIGN_KEY_CHECKS=1;
Where the SET FOREIGN_KEY_CHECKS=1 serves as an extra security measure...
Two possibilities:
- There is a table within another schema ("database" in mysql terminology) which has a FK reference
- The innodb internal data dictionary is out of sync with the mysql one.
You can see which table it was (one of them, anyway) by doing a "SHOW ENGINE INNODB STATUS" after the drop fails.
If it turns out to be the latter case, I'd dump and restore the whole server if you can.
MySQL 5.1 and above will give you the name of the table with the FK in the error message.
Disable foreign key checking
SET FOREIGN_KEY_CHECKS=0
from this blog:
You can temporarily disable foreign key checks:
SET FOREIGN_KEY_CHECKS=0;
Just be sure to restore them once you’re done messing around:
SET FOREIGN_KEY_CHECKS=1;