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

ERROR 1452: Cannot add or update a child row: a foreign key constraint fails

提问by user3289677

I have created tables in MySQL Workbench as shown below :

我在 MySQL Workbench 中创建了表，如下所示：

ORDRE table:

订单表：

CREATE TABLE Ordre (
  OrdreID   INT NOT NULL,
  OrdreDato DATE DEFAULT NULL,
  KundeID   INT  DEFAULT NULL,
  CONSTRAINT Ordre_pk PRIMARY KEY (OrdreID),
  CONSTRAINT Ordre_fk FOREIGN KEY (KundeID) REFERENCES Kunde (KundeID)
)
  ENGINE = InnoDB;

PRODUKT table:

产品表：

CREATE TABLE Produkt (
  ProduktID          INT NOT NULL,
  ProduktBeskrivelse VARCHAR(100) DEFAULT NULL,
  ProduktFarge       VARCHAR(20)  DEFAULT NULL,
  Enhetpris          INT          DEFAULT NULL,
  CONSTRAINT Produkt_pk PRIMARY KEY (ProduktID)
)
  ENGINE = InnoDB;

and ORDRELINJE table:

和ORDRELINJE 表：

CREATE TABLE Ordrelinje (
  Ordre         INT NOT NULL,
  Produkt       INT NOT NULL,
  AntallBestilt INT DEFAULT NULL,
  CONSTRAINT Ordrelinje_pk PRIMARY KEY (Ordre, Produkt),
  CONSTRAINT Ordrelinje_fk FOREIGN KEY (Ordre) REFERENCES Ordre (OrdreID),
  CONSTRAINT Ordrelinje_fk1 FOREIGN KEY (Produkt) REFERENCES Produkt (ProduktID)
)
  ENGINE = InnoDB;

so when I try to insert values into ORDRELINJEtable i get:

所以当我尝试将值插入ORDRELINJE表中时，我得到：

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (srdjank.Ordrelinje, CONSTRAINT Ordrelinje_fkFOREIGN KEY (Ordre) REFERENCES Ordre(OrdreID))

错误代码：1452。无法添加或更新子行：外键约束失败 ( srdjank. Ordrelinje, CONSTRAINT Ordrelinje_fkFOREIGN KEY ( Ordre) REFERENCES Ordre( OrdreID))

I've seen the other posts on this topic, but no luck. Am I overseeing something or any idea what to do?

我看过关于这个主题的其他帖子，但没有运气。我是否在监督某事或知道该怎么做？

回答by Rahul

Taken from Using FOREIGN KEY Constraints

取自使用 FOREIGN KEY 约束

Foreign key relationships involve a parent table that holds the central data values, and a child table with identical values pointing back to its parent. The FOREIGN KEY clause is specified in the child table.

It will reject any INSERT or UPDATE operation that attempts to create a foreign key value in a child table if there is no a matching candidate key value in the parent table.

外键关系涉及一个包含中心数据值的父表，以及一个具有相同值的子表指向其父表。FOREIGN KEY 子句在子表中指定。

如果父表中没有匹配的候选键值，它将拒绝任何试图在子表中创建外键值的 INSERT 或 UPDATE 操作。

So your error Error Code: 1452. Cannot add or update a child row: a foreign key constraint failsessentially means that, you are trying to add a row to your Ordrelinjetable for which no matching row (OrderID) is present in Ordretable.

因此，您的错误Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails基本上意味着，您正在尝试向Ordrelinje表中添加一行，而表中不存在匹配的行（OrderID）Ordre。

You must first insert the row to your Ordretable.

您必须先将该行插入到您的Ordre表中。

回答by Ankit Bansal

You are getting this constraint check because Ordretable does not have reference OrdreIDprovided in insert command.

您正在接受此约束检查，因为Ordre表没有OrdreID在插入命令中提供引用。

To insert value in Ordrelinje, you first have to enter value in Ordretable and use same OrdreIDin Orderlinjetable.

要插入的价值Ordrelinje，您必须先输入值Ordre表，并使用相同OrdreID的Orderlinje表。

Or you can remove not null constraint and insert a NULL value in it.

或者您可以删除非空约束并在其中插入 NULL 值。

回答by Humphrey

You must delete data in the child table which does not have any corresponding foreign key value to the parent table primary key .Or delete all data from the child table then insert new data having the same foreign key value as the primary key in the parent table . That should work . Here also a youtube video

您必须删除子表中没有与父表主键对应的外键值的数据。或者删除子表中的所有数据，然后在父表中插入与主键具有相同外键值的新数据. 那应该行得通。这里还有一个youtube视频

回答by Mr-Faizan

The Problem is with FOREIGN KEY Constraint. By Default (SET FOREIGN_KEY_CHECKS = 1). FOREIGN_KEY_CHECKS option specifies whether or not to check foreign key constraints for InnoDB tables. MySQL - SET FOREIGN_KEY_CHECKS

问题在于外键约束。默认情况下（SET FOREIGN_KEY_CHECKS = 1）。FOREIGN_KEY_CHECKS 选项指定是否检查 InnoDB 表的外键约束。MySQL - 设置 FOREIGN_KEY_CHECKS

We can set foreign key check as disable before running Query. Disable Foreign key.

我们可以在运行查询之前将外键检查设置为禁用。禁用外键。

Execute one of these lines before running your query, then you can run your query successfully. :)

在运行查询之前执行这些行之一，然后您可以成功运行查询。:)

1) For Session (recommended)

1) 对于 Session (推荐)

SET FOREIGN_KEY_CHECKS=0;

2) Globally

2) 全球

SET GLOBAL FOREIGN_KEY_CHECKS=0;

回答by Madhur Bhaiya

This error generally occurs because we have some values in the referencingfield of the child table, which do not exist in the referenced/candidatefield of the parent table.

出现这个错误一般是因为我们在子表的引用字段中有一些值，而在父表的被引用/候选字段中不存在这些值。

Sometimes, we may get this error when we are applying Foreign Key constraints to existing table(s), having data in them already. Some of the other answers are suggesting to delete the data completely from child table, and then apply the constraint. However, this is not an option when we already have working/production data in the child table. In most scenarios, we will need to update the data in the child table(instead of deleting them).

有时，当我们将外键约束应用于现有表时，我们可能会收到此错误，其中已经有数据。其他一些答案建议从子表中完全删除数据，然后应用约束。但是，当我们在子表中已经有工作/生产数据时，这不是一个选项。在大多数情况下，我们需要更新子表中的数据（而不是删除它们）。

Now, we can utilize Left Jointo find all those rows in the child table, which does not have matching values in the parent table. Following query (missing from other answers), would be helpful to fetch those non-matching rows:

现在，我们可以利用Left Join在子表中查找所有这些行，这些行在父表中没有匹配的值。以下查询（其他答案中缺少）将有助于获取那些不匹配的行：

SELECT child_table.* 
FROM child_table 
LEFT JOIN parent_table 
  ON parent_table.referenced_column = child_table.referencing_column 
WHERE parent_table.referenced_column IS NULL

Now, you can generally do one (or more) of the following steps to fix the data.

现在，您通常可以执行以下一个（或多个）步骤来修复数据。

1. Based on your "business logic", you will need to update/match these unmatching value(s), with the existing values in the parent table. You may sometimes need to set them nullas well.
2. Delete these rows having unmatching values.
3. Add new rows in your parent table, corresponding to the unmatching values in the child table.

1. 根据您的“业务逻辑”，您需要使用父表中的现有值更新/匹配这些不匹配的值。您有时可能还需要设置它们null。
2. 删除这些具有不匹配值的行。
3. 在父表中添加新行，对应于子表中不匹配的值。

Once the data is fixed, we can apply the Foreign key constraint using ALTER TABLEsyntax.

数据固定后，我们可以使用ALTER TABLE语法应用外键约束。

回答by Arbahud Rio Daroyni

in the foreign key table has a value that is not owned in the primary key table that will be related, so you must delete all data first / adjust the value of your foreign key table according to the value that is in your primary key

在外键表中有一个不属于主键表的值将被关联，因此您必须先删除所有数据/根据主键中的值调整外键表的值

回答by Matt Mcketty

I was getting this issue even though my parent table had all the values I was referencing in my child table. The issue seemed to be that I could not add multiple child references to a single foreign key. In other words if I had five rows of data referenced the same foreign key, MySQL was only allowing me to upload the first row and giving me the error 1452.

即使我的父表具有我在子表中引用的所有值，我也遇到了这个问题。问题似乎是我无法向单个外键添加多个子引用。换句话说，如果我有五行数据引用了同一个外键，MySQL 只允许我上传第一行并给我错误 1452。

What worked for me was typing the code "SET GLOBAL FOREIGN_KEY_CHECKS=0". After that I closed out of MySQL and then restarted it and I was able to upload all of my data with no errors. I then typed "SET GLOBAL FOREIGN_KEY_CHECKS=1" to set the system back to normal although I'm not entirely sure what FOREIGN_KEY_CHECKS does. Hope this helps!

对我有用的是输入代码“SET GLOBAL FOREIGN_KEY_CHECKS=0”。之后我关闭了 MySQL，然后重新启动它，我能够上传我的所有数据而没有错误。然后我输入“SET GLOBAL FOREIGN_KEY_CHECKS=1”将系统设置为正常，尽管我不完全确定 FOREIGN_KEY_CHECKS 的作用。希望这可以帮助！

回答by frz

This can be fixed by inserting the respective records in the Parent table first and then we can insert records in the Child table's respective column. Also check the data type and size of the column. It should be same as the parent table column,even the engine and collation should also be the same. TRY THIS! This is how I solved mine. Correct me if am wrong.

这可以通过首先在父表中插入相应的记录，然后我们可以在子表的相应列中插入记录来解决。还要检查列的数据类型和大小。它应该与父表列相同，甚至引擎和排序规则也应该相同。尝试这个！我的就是这样解决的。如果错了，请纠正我。

回答by mamal

you should add data from REFERENCES KEY in PRIMARY TABLE to FOREIGN KEY in CHILD TABLE
it means do not add random data to foreign key? just use data from primary key that is accessable

您应该将 PRIMARY TABLE 中的 REFERENCES KEY 中的数据添加到 CHILD TABLE 中的 FOREIGN KEY
这意味着不要向外 键添加随机数据？只使用可访问的主键中的数据

description of data in foreign key

外键数据描述

回答by Saket Choubey

Your ORDRELINJEtable is linked with ORDERtable using a foreign key constraint constraint Ordrelinje_fk foreign key(Ordre) references Ordre(OrdreID)according to which Ordre int NOT NULL,column of table ORDRELINJEmust match any Ordre int NOT NULL,column of ORDERtable.

根据表的哪一列必须与表的任何列匹配，您的ORDRELINJE表ORDER使用外键约束与表链接。constraint Ordrelinje_fk foreign key(Ordre) references Ordre(OrdreID)Ordre int NOT NULL,ORDRELINJEOrdre int NOT NULL,ORDER

Now what is happening here is, when you are inserting new row into ORDRELINJEtable, according to fk constraint Ordrelinje_fkit is checking ORDERtable if the OrdreIDis present or not and as it is not matching with any OrderId, Compiler is complaining for foreign key violation. This is the reason you are getting this error.

现在这里发生的事情是，当您向ORDRELINJE表中插入新行时，根据 fk 约束，Ordrelinje_fk它正在检查ORDER表是否OrdreID存在，并且由于它与任何 OrderId 都不匹配，编译器抱怨外键违规。这就是您收到此错误的原因。

Foreign key is the primary key of the other table which you use in any table to link between both. This key is bound by the foreign key constraint which you specify while creating the table. Any operation on the data must not violate this constraint. Violation of this constraint may result in errors like this.

外键是另一个表的主键，您可以在任何表中使用它来链接两者。此键受您在创建表时指定的外键约束。对数据的任何操作都不得违反此约束。违反此约束可能会导致这样的错误。

Hope I made it clear.

希望我说清楚了。