MySQL 无法删除表：外键约束失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11100911/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can't drop table: A foreign key constraint fails
提问by roy
In MySQL I want to drop a table.
I tried a lot things but I keep getting the error that the table named berichtcan't be dropped. This is the error I'm getting:
在 MySQL 中，我想删除一个表。
我尝试了很多东西，但我不断收到bericht无法删除名为表的错误。这是我得到的错误：
#1217 - Cannot delete or update a parent row: a foreign key constraint fails
#1217 - 无法删除或更新父行：外键约束失败
How do I drop this table?
怎么放下这张桌子？
回答by Rune Kaagaard
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 berichtbefore 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.
正如其他人指出的那样，这几乎从来都不是您想要的，即使这是问题中提出的问题。更安全的解决方案是在删除bericht之前删除表bericht。有关如何执行此操作，请参阅 CloudyMarble 答案。当我不想或无法删除和重新创建数据库本身时，我使用 bash 和我帖子中的方法删除数据库中的所有表。
The #1217error 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 documentationfor 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.
在#1217当其他表有外键约束您试图删除，并且您使用的是InnoDB数据库引擎表发生错误。此解决方案暂时禁用检查约束，然后重新启用它们。阅读文档了解更多信息。请务必根据 删除表中的外键限制和字段bericht，否则可能会使数据库处于损坏状态。
回答by CloudyMarble
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
这应该为您提供哪些表具有对您要删除的表的引用，一旦删除这些引用，或者引用此表中的数据集的数据集，您将能够删除该表
回答by mihaisimi
Use show create table tbl_nameto view the foreign keys
使用show create table tbl_name查看外键
You can use this syntax to drop a foreign key:
您可以使用此语法删除外键：
ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol
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
这里还有更多信息（参见 Frank Vanderhallen 帖子）：http: //dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html
回答by Kip Russel
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.
您需要先删除子行，然后再删除父行。
回答by Marius Cucuruz
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 NULLand then choose ON DELETE SET NULL.
我意识到这已经过时了一段时间并且已经选择了答案，但是允许外键为NULL然后选择ON DELETE SET NULL的替代方法如何。
Basically, your table should be changed like so:
基本上，你的表应该像这样改变：
ALTER TABLE 'bericht'
DROP FOREIGN KEY 'your_foreign_key';
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;
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.
我个人建议同时使用“ON UPDATE CASCADE”和“ON DELETE SET NULL”以避免不必要的并发症，但是您的设置可能会规定不同的方法。
Hope this helps.
希望这可以帮助。
