Mysql 错误 1452 - 无法添加或更新子行：外键约束失败

Mysql error 1452 - Cannot add or update a child row: a foreign key constraint fails

提问by Zim

I'm having a bit of a strange problem. I'm trying to add a foreign key to one table that references another, but it is failing for some reason. With my limited knowledge of MySQL, the only thing that could possibly be suspect is that there is a foreign key on a different table referencing the one I am trying to reference.

我遇到了一个奇怪的问题。我正在尝试向一个引用另一个表的表添加外键，但由于某种原因它失败了。由于我对 MySQL 的了解有限，唯一可能怀疑的是在另一个表上有一个外键引用了我试图引用的表。

I've done a SHOW CREATE TABLEquery on both tables, sourcecodes_tagsis the table with the foreign key, sourcecodesis the referenced table.

我SHOW CREATE TABLE对两个表都做了查询，sourcecodes_tags是带有外键的表，sourcecodes是被引用的表。

CREATE TABLE `sourcecodes` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `user_id` int(11) unsigned NOT NULL,
 `language_id` int(11) unsigned NOT NULL,
 `category_id` int(11) unsigned NOT NULL,
 `title` varchar(40) CHARACTER SET utf8 NOT NULL,
 `description` text CHARACTER SET utf8 NOT NULL,
 `views` int(11) unsigned NOT NULL,
 `downloads` int(11) unsigned NOT NULL,
 `time_posted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 KEY `user_id` (`user_id`),
 KEY `language_id` (`language_id`),
 KEY `category_id` (`category_id`),
 CONSTRAINT `sourcecodes_ibfk_3` FOREIGN KEY (`language_id`) REFERENCES `languages` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `sourcecodes_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `sourcecodes_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

CREATE TABLE `sourcecodes_tags` (
 `sourcecode_id` int(11) unsigned NOT NULL,
 `tag_id` int(11) unsigned NOT NULL,
 KEY `sourcecode_id` (`sourcecode_id`),
 KEY `tag_id` (`tag_id`),
 CONSTRAINT `sourcecodes_tags_ibfk_1` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

This is the code that generates the error:

这是产生错误的代码：

ALTER TABLE sourcecodes_tags ADD FOREIGN KEY (sourcecode_id) REFERENCES sourcecodes (id) ON DELETE CASCADE ON UPDATE CASCADE

回答by nos

Quite likely your sourcecodes_tagstable contains sourcecode_idvalues that no longer exists in your sourcecodestable. You have to get rid of those first.

您的sourcecodes_tags表很可能包含表中sourcecode_id不再存在的值sourcecodes。你必须先摆脱那些。

Here's a query that can find those IDs:

这是一个可以找到这些 ID 的查询：

SELECT DISTINCT sourcecode_id FROM 
   sourcecodes_tags tags LEFT JOIN sourcecodes sc ON tags.sourcecode_id=sc.id 
WHERE sc.id IS NULL;

回答by Prakash

I had the same issue with my MySQL database but finally, I got a solution which worked for me.
Since in my table everything was fine from the mysql point of view(both tables should use InnoDB engine and the datatype of each column should be of the same type which takes part in foreign key constraint).
The only thing that I did was to disable the foreign key check and later on enabled it after performing the foreign key operation.
Steps that I took:

我的 MySQL 数据库也有同样的问题，但最后，我得到了一个对我有用的解决方案。
因为在我的表中，从 mysql 的角度来看一切都很好（两个表都应该使用 InnoDB 引擎，并且每列的数据类型应该是参与外键约束的相同类型）。
我所做的唯一一件事就是禁用外键检查，然后在执行外键操作后启用它。
我采取的步骤：

SET foreign_key_checks = 0;

alter table tblUsedDestination add constraint f_operatorId foreign key(iOperatorId) references tblOperators (iOperatorId); Query
OK, 8 rows affected (0.23 sec) Records: 8  Duplicates: 0  Warnings: 0

SET foreign_key_checks = 1;

回答by Ryan Ward

Use NOT INto find where constraints are constraining:

使用NOT IN以找到约束制约：

SELECT column FROM table WHERE column NOT IN 
(SELECT intended_foreign_key FROM another_table)

so, more specifically:

所以，更具体地说：

SELECT sourcecode_id FROM sourcecodes_tags WHERE sourcecode_id NOT IN 
(SELECT id FROM sourcecodes)

EDIT: INand NOT INoperators are known to be much faster than the JOINoperators, as well as much easier to construct, and repeat.

编辑：众所周知，IN和NOT IN运算符比JOIN运算符快得多，并且更容易构造和重复。

回答by Shankar Damodaran

Truncate the tables and then try adding the FK Constraint.

截断表格，然后尝试添加 FK 约束。

I know this solution is a bit awkward but it does work 100%. But I agree that this is not an ideal solution to deal with problem, but I hope it helps.

我知道这个解决方案有点尴尬，但它确实 100% 有效。但我同意这不是处理问题的理想解决方案，但我希望它有所帮助。

回答by zmonteca

For me, this problem was a little different and super easy to check and solve.

对我来说，这个问题有点不同，而且非常容易检查和解决。

You must ensure BOTH of your tables are InnoDB. If one of the tables, namely the reference table is a MyISAM, the constraint will fail.

您必须确保两个表都是 InnoDB。如果其中一个表，即引用表是 MyISAM，则约束将失败。

    SHOW TABLE STATUS WHERE Name =  't1';

    ALTER TABLE t1 ENGINE=InnoDB;

回答by Michael Helwig

I had the same problem today. I tested for four things, some of them already mentioned here:

我今天遇到了同样的问题。我测试了四件事，其中一些已经在这里提到：

1. Are there any values in your child column that don't exist in the parent column (besides NULL, if the child column is nullable)

2. Do child and parent columns have the same datatype?

3. Is there an index on the parent column you are referencing? MySQL seems to require this for performance reasons (http://dev.mysql.com/doc/refman/5.5/en/create-table-foreign-keys.html)

4. And this one solved it for me: Do both tables have identical collation?

1. 您的子列中是否存在父列中不存在的任何值（除了 NULL，如果子列可以为空）

2. 子列和父列是否具有相同的数据类型？

3. 您引用的父列上是否有索引？MySQL 似乎出于性能原因需要这样做（http://dev.mysql.com/doc/refman/5.5/en/create-table-foreign-keys.html）

4. 这个为我解决了这个问题：两个表是否具有相同的排序规则？

I had one table in UTF-8 and the other in iso-something. That didn't work. After changing the iso-table to UTF-8 collation the constraints could be added without problems. In my case, phpMyAdmin didn't even show the child table in iso-encoding in the dropdown for creating the foreign key constraint.

我有一张 UTF-8 表，另一张是 iso-some 表。那没有用。将 iso-table 更改为 UTF-8 排序规则后，可以毫无问题地添加约束。就我而言，phpMyAdmin 甚至没有在用于创建外键约束的下拉列表中以 iso 编码方式显示子表。

回答by fyrye

This also happens when setting a foreign key to parent.id to child.column if the child.column has a value of 0 already and no parent.id value is 0

如果 child.column 的值已经为 0 并且没有 parent.id 的值为 0，则在将 parent.id 的外键设置为 child.column 时也会发生这种情况

You would need to ensure that each child.column is NULL or has value that exists in parent.id

您需要确保每个 child.column 为 NULL 或具有存在于 parent.id 中的值

And now that I read the statement nos wrote, that's what he is validating.

现在我读了 nos 写的声明，这就是他正在验证的内容。

回答by Milad Rahimi

It seems there is some invalid value for the column line 0 that is not a valid foreign key so MySQL cannot set a foreign key constraint for it.

似乎列第 0 行的某些无效值不是有效的外键，因此 MySQL 无法为其设置外键约束。

You can follow these steps:

您可以按照以下步骤操作：

1. Drop the column which you have tried to set FK constraint for.

2. Add it again and set its default value as NULL.

3. Try to set a foreign key constraint for it again.

1. 删除您尝试为其设置 FK 约束的列。

2. 再次添加并将其默认值设置为 NULL。

3. 尝试再次为其设置外键约束。

回答by Mostafa -T

I'd the same problem, I checked rows of my tables and found there was some incompatibility with the value of fields that I wanted to define a foreign key. I corrected those value, tried again and the problem was solved.

我遇到了同样的问题，我检查了表的行，发现与我想定义外键的字段值有些不兼容。我更正了这些值，再次尝试，问题解决了。

回答by VHanded

I end up delete all the data in my table, and run alter again. It works. Not the brilliant one, but it save a lot time, especially your application is still in development stage without any customer data.

我最终删除了表中的所有数据，然后再次运行 alter。有用。不是很好，但它节省了很多时间，尤其是您的应用程序仍处于开发阶段，没有任何客户数据。