mysql 外键错误 #1452
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5198600/
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
mysql foreign key error #1452
提问by Webnet
ALTER TABLE `groups` ADD FOREIGN KEY ( `company_id` ) REFERENCES `summaries`.`companies` (
`id`
) ON DELETE CASCADE ;
MySQL said:
#1452 - Cannot add or update a child row: a foreign key constraint fails (`summaries/#sql-164a_33c`, CONSTRAINT `#sql-164a_33c_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE)
companies.idis primary auto increment int(11)
companies.id是 primary auto increment int(11)
company_idis index int(11)
company_id是 index int(11)
I don't understand the error message. Can anyone shed some light on this?
我不明白错误信息。任何人都可以对此有所了解吗？
回答by Ike Walker
That means you have at least one row in the child table that references a non-existent row in the parent table.
这意味着您在子表中至少有一行引用了父表中不存在的行。
If you are absolutely sure that you are okay with having a data integrity issue like that, you can add the foreign key by disabling foreign key checks before you run the ALTER TABLEcommand:
如果您完全确定您可以接受这样的数据完整性问题，则可以通过在运行ALTER TABLE命令之前禁用外键检查来添加外键：
SET FOREIGN_KEY_CHECKS = 0;
回答by mcmlxxxvi
I just had this problem, although in a somewhat more specific scenario.
我刚刚遇到了这个问题，尽管在更具体的情况下。
In my case, I had added, to an existing table, a column that I needed to be both nullable and act as a foreign key (i.e., for non-null entries to be bound by a foreign key constraint).
在我的例子中，我已经在现有表中添加了一个列，我需要该列既可以为空又可以作为外键（即，非空条目受外键约束约束）。
The referenced column looked like this:
引用的列如下所示：
+-------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
+-------------+-------------+------+-----+---------+----------------+
and the referencing one like this:
和这样的引用：
+-------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| bed_id | int(10) | YES | | NULL | |
+-------------+-------------+------+-----+---------+----------------+
Turned out that I had forgotten to specify DEFAULT NULLwhen adding the referencing column to the existing table and so it was automatically filled with zeros, which failed the foreign key constraint.
原来我忘记DEFAULT NULL在将引用列添加到现有表时指定，因此它自动填充了零，这使外键约束失败。
I changed them to NULL:
我将它们更改为 NULL：
update devices set bed_id = NULL where bed_id = 0;
and then successfully added the foreign key constraint. Hope this helps someone
然后成功添加了外键约束。希望这有助于某人
回答by RolandoMySQLDBA
It looks like it tried to copy the groups table to a temp table in the summaries database.
看起来它试图将组表复制到摘要数据库中的临时表。
MySQL tried to put the requested constraints on the temp table first. There may possibly be one or more rows in the groups table (hence the temp table also) whose company_id is no longer present in the summaries.companies table.
MySQL 尝试首先将请求的约束放在临时表上。在groups 表（因此也是临时表）中可能有一行或多行其company_id 不再出现在summaries.companies 表中。
To verfiy : Try running a LEFT JOIN between groups and summaries.companies WHERE companies.id is NULL. If you get back any rows from that LEFT JOIN, you found the bad rows in the groups table.
验证：尝试在组和 summaries.companies 之间运行 LEFT JOIN，其中 company.id 为 NULL。如果你从那个 LEFT JOIN 中取回任何行，你就会在组表中找到坏行。
Give it a Try !!!
试一试 ！！！
回答by adridev
Checks that "companies" table is not empty,if is empty and you have no data at moment.
检查“公司”表是否为空，如果为空并且您目前没有数据。
set SET FOREIGN_KEY_CHECKS = 0;
like Ike said you before.
就像艾克之前说的那样。
