89

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?

CloudyMarble
  • 36,908
  • 70
  • 97
  • 130
roy
  • 901
  • 1
  • 6
  • 4
  • 2
    Find out what other table is referencing it via foreign key, and first drop the FK constraint on that table, then drop this table. – Michael Berkowski Jun 19 '12 at 12:27
  • You need to remove the requirement by other tables on the one you dont want any more – BugFinder Jun 19 '12 at 12:27
  • 1
    Do any of these answers help you? http://stackoverflow.com/questions/1905470/cannot-delete-or-update-a-parent-row-a-foreign-key-constraint-fails http://stackoverflow.com/questions/3334619/cannot-delete-or-update-a-parent-row-a-foreign-key-constraint-fails – Douglas Ludlow Jun 19 '12 at 12:35

6 Answers6

187

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.

Rune Kaagaard
  • 6,643
  • 2
  • 38
  • 29
  • 10
    Although it is correct, it is an amazingly BAD PRACTICE to do this. As was pointed out by multiple users (including Rune Kaagaard), you should find the references, analyze and drop them as needed. – zozo Feb 22 '16 at 09:20
  • 4
    In the scenario where one is removing a table then creating it again (perhaps with some changes, but where the FK column remains), then this is a great solution. Once the table is created again the foreign keys in the other tables still work. Aside: one could instead modify the table instead of DROP followed by CREATE - which is perhaps the more correct solution. However, if using a script to create tables, I think it is sensible to want to use the DROP / CREATE method. – MD004 Feb 28 '17 at 19:21
  • 4
    I had a situation where no tables were referencing the table I was trying to delete, but got the same error. `select * from information_schema.KEY_COLUMN_USAGE where referenced_table_name = 'table_to_delete';` returned an empty set. I assume this was a problem with InnoDB engine. This solution worked in my case too. – Nikola Novak Apr 11 '17 at 08:26
  • 1
    This can be the best answer. I have a situation in which two tables both have foreign key references to each other. Neither will be allowed to be dropped before the other. So turning off foreign key checks makes sense. It's only during dev that I need to do it, not on production. – Halfstop Dec 29 '18 at 03:38
  • 4
    people saying it's bad practice but it really depends on the use case, so stop using big words for no reason... – Barbz_YHOOL Oct 17 '19 at 19:45
  • 1
    @Barbz_YHOOL Exactly. I wanted to delete all my tables in a database. This is the best way. – user2924019 Aug 24 '21 at 13:13
90

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

CloudyMarble
  • 36,908
  • 70
  • 97
  • 130
15

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/

EC Chaitanya
  • 179
  • 1
  • 6
13

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

DMK
  • 2,448
  • 1
  • 24
  • 35
mihaisimi
  • 1,911
  • 13
  • 15
2

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.

Kip Russel
  • 51
  • 1
  • 6
1

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.

Marius Cucuruz
  • 107
  • 1
  • 5