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

User · Answer

I realize this is stale for a while and an answer had been selected, but how about the alternative to allow the foreign key to be NULL and then choose ON DELETE SET NULL.

Basically, your table should be changed like so:

ALTER TABLE 'bericht' DROP FOREIGN KEY 'your_foreign_key';

ALTER TABLE 'bericht' ADD CONSTRAINT 'your_foreign_key' FOREIGN KEY ('column_foreign_key') REFERENCES 'other_table' ('column_parent_key') ON UPDATE CASCADE ON DELETE SET NULL;

Personally I would recommend using both "ON UPDATE CASCADE" as well as "ON DELETE SET NULL" to avoid unnecessary complications, however your set up may dictate a different approach.

Hope this helps.

User · Answer

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

User · Answer

But fortunately  with the MySQL FOREIGN KEY CHECKS variable  you don t have to worry about the order of your DROP TABLE statements at all  and you can write them in any order you like -- even the exact opposite -- like this  SET FOREIGN KEY CHECKS   0  drop table if exists customers  drop table if exists orders  drop table if exists order details  SET FOREIGN KEY CHECKS   1   For more clarification  check out the link below  http   alvinalexander com blog post mysql drop-mysql-tables-in-any-order-foreign-keys

User · Answer

This probably has the same table to other schema the reason why you re getting that error   You need to drop first the child row then the parent row

User · Answer

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

User · Answer

Use show create table tbl name to view the foreign keys  You can use this syntax to drop a foreign key   ALTER TABLE tbl name DROP FOREIGN KEY fk symbol  There s also more information here  see Frank Vanderhallen post   http   dev mysql com doc refman 5 5 en innodb-foreign-key-constraints html

[mysql] Can't drop table: A foreign key constraint fails

Examples related to mysql

Examples related to sql

Examples related to drop-table