Can't drop table: A foreign key constraint fails

Question

In MySQL I want to drop a table.
I tried a lot things but I keep getting the error that the table named bericht can't be dropped. This is the error I'm getting:

#1217 - Cannot delete or update a parent row: a foreign key constraint fails

How do I drop this table?

Answer 1

This should do the trick:

SET FOREIGN_KEY_CHECKS=0; DROP TABLE bericht; SET FOREIGN_KEY_CHECKS=1; 

As others point out, this is almost never what you want, even though it's whats asked in the question. A more safe solution is to delete the tables depending on bericht before deleting bericht. See CloudyMarble answer on how to do that. I use bash and the method in my post to drop all tables in a database when I don't want to or can't delete and recreate the database itself.

The #1217 error happens when other tables has foreign key constraints to the table you are trying to delete and you are using the InnoDB database engine. This solution temporarily disables checking the restraints and then re-enables them. Read the documentation for more. Be sure to delete foreign key restraints and fields in tables depending on bericht, otherwise you might leave your database in a broken state.

Answer 2

Try this:

SELECT *  FROM information_schema.KEY_COLUMN_USAGE  WHERE REFERENCED_TABLE_NAME = 'YourTable'; 

This should deliver you which Tables have references to the table you want to drop, once you drop these references, or the datasets which reference datasets in this table you will be able to drop the table