MySQL 虚假外键约束失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3334619/
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
Bogus foreign key constraint fail
提问by álvaro González
I get this error message:
我收到此错误消息：
ERROR 1217 (23000) at line 40: Cannot delete or update a parent row: a foreign key constraint fails
第 40 行的 ERROR 1217 (23000)：无法删除或更新父行：外键约束失败
... when I try to drop a table:
...当我尝试删除一张桌子时：
DROP TABLE IF EXISTS `area`;
... defined like this:
...定义如下：
CREATE TABLE `area` (
`area_id` char(3) COLLATE utf8_spanish_ci NOT NULL,
`nombre_area` varchar(30) COLLATE utf8_spanish_ci NOT NULL,
`descripcion_area` varchar(100) COLLATE utf8_spanish_ci NOT NULL,
PRIMARY KEY (`area_id`),
UNIQUE KEY `nombre_area_UNIQUE` (`nombre_area`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci;
The funny thing is that I already dropped all other tablesin the schema that have foreign keys against area. Actually, the database is empty except for the areatable.
有趣的是，我已经删除了架构中具有针对area. 实际上，除了area表之外，数据库是空的。
How can it possibly have child rows if there isn't any other object in the database? As far as I know, InnoDB doesn't allow foreign keys on other schemas, does it?
如果数据库中没有任何其他对象，它怎么可能有子行？据我所知，InnoDB 不允许在其他模式上使用外键，是吗？
(I can even run a RENAME TABLE area TO something_elsecommand :-?)
（我什至可以运行RENAME TABLE area TO something_else命令：-？）
采纳答案by MarkR
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.
- 在另一个模式（mysql 术语中的“数据库”）中有一个表，它有一个 FK 引用
- innodb 内部数据字典与 mysql 不同步。
You can see which table it was (one of them, anyway) by doing a "SHOW ENGINE INNODB STATUS" after the drop fails.
在删除失败后，您可以通过执行“SHOW ENGINE INNODB STATUS”来查看它是哪个表（无论如何是其中之一）。
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.
MySQL 5.1 及更高版本将在错误消息中为您提供带有 FK 的表的名称。
回答by Karlis Rode
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.
使用 MySQL 查询浏览器或 phpMyAdmin 时，似乎为每个查询打开了一个新连接（bugs.mysql.com/bug.php?id=8280），因此有必要在一个查询中编写所有 drop 语句，例如。
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=1serves as an extra security measure...
当SET FOREIGN_KEY_CHECKS=1作为一个额外的安全措施？
回答by Flakron Bytyqi
Disable foreign key checking
禁用外键检查
SET FOREIGN_KEY_CHECKS=0
回答by HymanD
回答by M_ Fa
hopefully its work
希望它的工作
SET foreign_key_checks = 0;
DROP TABLE table name;
SET foreign_key_checks = 1;
SET foreign_key_checks = 0; 删除表table name；SET foreign_key_checks = 1;
回答by yeyo
On Rails, one can do the following using the rails console:
在 Rails 上，可以使用以下命令执行以下操作rails console：
connection = ActiveRecord::Base.connection
connection.execute("SET FOREIGN_KEY_CHECKS=0;")
回答by Vadim Pluzhinsky
Maybe you received an error when working with this table before. You can rename the table and try to remove it again.
也许您之前在使用此表时收到错误消息。您可以重命名该表并再次尝试将其删除。
ALTER TABLE `area` RENAME TO `area2`;
DROP TABLE IF EXISTS `area2`;
回答by Abdulrahman K
i found an easy solution, export the database, edit it what you want to edit in a text editor, then import it. Done
我找到了一个简单的解决方案，导出数据库，在文本编辑器中编辑您想要编辑的内容，然后导入它。完毕
回答by Aadil Masavir
Cannot delete or update a parent row: a foreign key constraint fails (table1.user_role, CONSTRAINT FK143BF46A8dsfsfds@#5A6BD60FOREIGN KEY (user_id) REFERENCES user(id))
无法删除或更新父行：外键约束失败 ( table1. user_role, CONSTRAINT FK143BF46A8dsfsfds@#5A6BD60FOREIGN KEY ( user_id) REFERENCES user( id))
What i did in two simple steps . first i delete the child row in child table like
我用两个简单的步骤做了什么。首先我删除子表中的子行
mysql> delete from table2 where role_id = 2 && user_id =20;
mysql> delete from table2 where role_id = 2 && user_id =20;
Query OK, 1 row affected (0.10 sec)
查询正常，1 行受影响（0.10 秒）
and second step as deleting the parent
第二步是删除父级
delete from table1 where id = 20;
从表 1 中删除，其中 id = 20；
Query OK, 1 row affected (0.12 sec)
查询正常，1 行受影响（0.12 秒）
By this i solve the Problem which means Delete Child then Delete parent
通过这个我解决了问题，这意味着删除子项然后删除父项
i Hope You got it. :)
我希望你明白了。:)
