MySQL - 无法添加或更新子行：外键约束失败

MySQL - Cannot add or update a child row: a foreign key constraint fails

提问by Paul Mennega

This seems to be a common error, but for the life of me I can't figure this out.

这似乎是一个常见的错误，但对于我的生活，我无法弄清楚这一点。

I have a set of InnoDB user tables in MySQL that are tied together via foreign key; the parent usertable, and a set of child tables that store email addresses, actions, etc. These are all tied to the parent usertable by a foreign key, uid, with all of the parent and child keys being int(10).

我在 MySQL 中有一组 InnoDB 用户表，它们通过外键绑定在一起；父user表，以及一组存储电子邮件地址、操作等的子表。这些都user通过外键关联到父表uid，所有的父键和子键都是int(10)。

All of the child tables have a uidvalue with a foreign key constraint pointing to user.uid, and set to ON DELETE CASCADEand ON UPDATE CASCADE.

所有子表都有一个uid值，外键约束指向user.uid，并设置为ON DELETE CASCADE和ON UPDATE CASCADE。

When I delete a user from user, all of the child constrained entries are removed. However, when I attempt to update a user.uidvalue, it results in the following error, rather than cascading the uidchange to the child tables:

当我从 中删除用户时user，所有子约束条目都将被删除。但是，当我尝试更新user.uid值时，会导致以下错误，而不是将uid更改级联到子表：

#1452 - Cannot add or update a child row: a foreign key constraint fails (`accounts`.`user_email`, CONSTRAINT `user_email_ibfk_2` FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE)

I have a feeling I must be missing something obvious here. Removing the key constraint with user_emailand attempting to update the value in userresults in the same error but for the next alphabetical userchild table, so I don't believe it is a table-specific error.

我有一种感觉，我一定在这里遗漏了一些明显的东西。删除键约束user_email并尝试更新值会user导致相同的错误，但对于下一个按字母顺序排列的user子表，所以我不认为这是特定于表的错误。

EDIT:

编辑：

Adding in the results from SHOW ENGINE INNODB STATUS:

添加来自SHOW ENGINE INNODB STATUS以下内容的结果：

------------------------
LATEST FOREIGN KEY ERROR
------------------------
121018 22:35:41 Transaction:
TRANSACTION 0 5564387, ACTIVE 0 sec, process no 1619, OS thread id 2957499248 updating or deleting, thread declared inside InnoDB 499
mysql tables in use 1, locked 1
17 lock struct(s), heap size 2496, 9 row lock(s), undo log entries 2
MySQL thread id 3435659, query id 24068634 localhost root Updating
UPDATE `accounts`.`user` SET `uid` = '1' WHERE `user`.`uid` = 306
Foreign key constraint fails for table `accounts`.`user_email`:
,
  CONSTRAINT `user_email_ibfk_2` FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE
Trying to add in child table, in index `uid` tuple:
DATA TUPLE: 2 fields;
...
A bunch of hex code

But in parent table `accounts`.`user`, in index `PRIMARY`,
the closest match we can find is record:
...
A bunch of hex code

采纳答案by Paul Mennega

On an unrelated task, I recently brought up our MySQL database in MySQL Workbench, and when viewing the table relations for the above tables, I noticed 'duplicate' and/or spurious relations that I had somehow missed before (they weren't showing up in PHPMyAdmin FWIW). Removing these extra relations cleared up the issue immediately.

在一项不相关的任务中，我最近在MySQL Workbench 中启动了我们的 MySQL 数据库，当查看上述表的表关系时，我注意到我之前不知何故错过的“重复”和/或虚假关系（它们没有出现在 PHPMyAdmin FWIW 中）。删除这些额外的关系立即解决了问题。

回答by Sidupac

I solved my 'foreign key constraint fails' issues by adding the following code to the start of the SQL code (this was for importing values to a table)

我通过将以下代码添加到 SQL 代码的开头解决了我的“外键约束失败”问题（这是为了将值导入表）

SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;
SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;
SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;
SET NAMES utf8;
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO';
SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0; 

Then adding this code to the end of the file

然后将此代码添加到文件末尾

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;
SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;
SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;
SET SQL_NOTES=@OLD_SQL_NOTES; 

回答by walrii

Since you haven't given table definitions, it's hard to guess. But it looks like you are attempting to modify the foreign key in the child table. AFAIK, this is illegal, you can modify it from the parent, but not the child table.

由于您没有给出表定义，因此很难猜测。但看起来您正在尝试修改子表中的外键。AFAIK，这是非法的，您可以从父表修改它，但不能从子表修改。

Consider this example:

考虑这个例子：

CREATE TABLE parent (
  parent_id INT NOT NULL,
  parent_data int,

  PRIMARY KEY (parent_id)
) ENGINE=INNODB;

CREATE TABLE child1 (
  child1_id INT,
  child1_data INT,
  fk_parent_id INT,

  INDEX par_ind1 (fk_parent_id),

  FOREIGN KEY (fk_parent_id)
    REFERENCES parent(parent_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE=INNODB;

CREATE TABLE child2 (
  child2_id INT,
  child2_data INT,
  fk_parent_id INT,

  INDEX par_ind2 (fk_parent_id),

  FOREIGN KEY (fk_parent_id)
    REFERENCES parent(parent_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE=INNODB;

INSERT INTO parent
  (parent_id, parent_data)
  VALUES
  (1, 11),
  (2, 12);

INSERT INTO child1
  (child1_id, child1_data, fk_parent_id)
  VALUES
  (101, 1001, 1),
  (102, 1002, 1),
  (103, 1003, 1),
  (104, 1004, 2),
  (105, 1005, 2);

INSERT INTO child2
  (child2_id, child2_data, fk_parent_id)
  VALUES
  (106, 1006, 1),
  (107, 1007, 1),
  (108, 1008, 1),
  (109, 1009, 2),
  (110, 1010, 2);

Then this is allowed:

那么这是允许的：

UPDATE parent
  SET parent_id = 3 WHERE parent_id = 2;

SELECT * FROM parent;
SELECT * FROM child1;
SELECT * FROM child2;

But this is not, because it modifies the parent fk from the child table:

但这不是，因为它修改了子表中的父 fk：

UPDATE child1
  SET fk_parent_id = 4 WHERE fk_parent_id = 1;

It gets an error very similar to your error:

它得到一个与您的错误非常相似的错误：

Cannot add or update a child row: a foreign key constraint fails (`db_2_b43a7`.`child1`, CONSTRAINT `child1_ibfk_1` FOREIGN KEY (`fk_parent_id`) REFERENCES `parent` (`parent_id`) ON DELETE CASCADE ON UPDATE CASCADE):

回答by Varun

I had faced same issue while creating foreign constraints on table. the simple way of coming out of this issue are first take backup of your parent and child table then truncate child table and again try to make a relation. hope this will solve the problem.

我在表上创建外部约束时遇到了同样的问题。解决这个问题的简单方法是首先备份您的父表和子表，然后截断子表，然后再次尝试建立关系。希望这能解决问题。

回答by robin850

Even though this is pretty old, just chiming in to say that what is useful in @Sidupac's answer is the FOREIGN_KEY_CHECKS=0.

尽管这已经很老了，但只是插嘴说@Sidupac 的答案中有用的是FOREIGN_KEY_CHECKS=0.

This answer is not an option when you are using something that manages the database schema for you (JPA in my case) but the problem may be that there are "orphaned" entries in your table (referencing a foreign key that might not exist).

当您使用为您管理数据库模式的东西（在我的情况下为 JPA）时，此答案不是一个选项，但问题可能是您的表中存在“孤立”条目（引用可能不存在的外键）。

This can often happen when you convert a MySQL table from MyISAM to InnoDB since referential integrity isn't really a thing with the former.

当您将 MySQL 表从 MyISAM 转换为 InnoDB 时，经常会发生这种情况，因为引用完整性实际上与前者无关。

回答by Anna

Such an error on update may be caused by the difference in character set and collation so make sure they are the same for both tables.

更新时的此类错误可能是由字符集和排序规则的差异引起的，因此请确保两个表的它们相同。

回答by sulaiman

I've faced this issue and the solution was making sure that all the data from the child field are matching the parent field

我遇到过这个问题，解决方案是确保子字段中的所有数据都与父字段匹配

for example, you want to add foreign key inside (attendance) table to the column (employeeName)

例如，您想将（出勤）表内的外键添加到列（employeeName）

where the parent is (employees) table, (employeeName) column

其中父是 (employees) 表，(employeeName) 列

all the data in attendance.employeeName must be matching employee.employeeName

出勤.employeeName中的所有数据必须匹配employee.employeeName

回答by Anupam datta

I had the same problem but when I looked closely I found that, it was causing because I was trying to put the foreign key values into the tables before that key was assigned its primary key value. e.g. I had two tables "customers" and "films", "cust_id" and "film_id" were primary key respectively. "customer" had one-to-many relation with "films" so I had "cust_id" as foreign key in "films" tables. But I was trying to put values to "films" table first, so I got that problem.

我遇到了同样的问题，但是当我仔细观察时，我发现这是因为我试图在为该键分配其主键值之前将外键值放入表中。例如，我有两个表“customers”和“films”，“cust_id”和“film_id”分别是主键。“客户”与“电影”有一对多关系，因此我将“cust_id”作为“电影”表中的外键。但是我试图首先将值放入“电影”表中，所以我遇到了这个问题。

回答by Kelli

Hope this will assist anyone having the same error while importing CSV data into related tables. In my case the parent table was OK, but I got the error while importing data to the child table containing the foreign key. After temporarily removing the foregn key constraint on the child table, I managed to import the data and was suprised to find some of the values in the FK column having values of 0 (obviously this had been causing the error since the parent table did not have such values in its PK column). The cause was that, the data in my CSV column preceeding the FK column contained commas (which I was using as a field delimeter). Changing the delimeter for my CSV file solved the problem.

希望这将有助于在将 CSV 数据导入相关表时遇到相同错误的任何人。在我的情况下，父表没问题，但是在将数据导入包含外键的子表时出现错误。在临时删除子表上的 foregn 键约束后，我设法导入了数据，并惊讶地发现 FK 列中的一些值为 0 的值（显然这导致了错误，因为父表没有其 PK 列中的此类值）。原因是，我的 CSV 列中 FK 列之前的数据包含逗号（我将其用作字段分隔符）。更改我的 CSV 文件的分隔符解决了这个问题。