MySQL 无法添加或更新子行：外键约束失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5005388/
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
Cannot add or update a child row: a foreign key constraint fails
提问by Tom
table 1
表格1
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| UserID | int(11) | NO | PRI | NULL | auto_increment |
| Password | varchar(20) | NO | | | |
| Username | varchar(25) | NO | | | |
| Email | varchar(60) | NO | | | |
+----------+-------------+------+-----+---------+----------------+
table2
表2
+------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+----------------+
| UserID | int(11) | NO | MUL | | |
| PostID | int(11) | NO | PRI | NULL | auto_increment |
| Title | varchar(50) | NO | | | |
| Summary | varchar(500) | NO | | | |
+------------------+--------------+------+-----+---------+----------------+
Error:
错误：
com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException:
Cannot add or update a child row: a foreign key constraint fails
(`myapp/table2`, CONSTRAINT `table2_ibfk_1` FOREIGN KEY (`UserID`)
REFERENCES `table1` (`UserID`))
What have I done wrong? I read http://www.w3schools.com/Sql/sql_foreignkey.aspand I don't see what's wrong.
我做错了什么？我阅读了http://www.w3schools.com/Sql/sql_foreignkey.asp，但我不知道有什么问题。
回答by Brian Driscoll
You're getting this error because you're trying to add/update a row to table2that does not have a valid value for the UserIDfield based on the values currently stored in table1. If you post some more code I can help you diagnose the specific cause.
你是因为你想添加/更新一行来得到这个错误table2不具有的一个有效的值UserID基于当前存储在值域table1。如果您发布更多代码，我可以帮助您诊断具体原因。
回答by Rami C
It means that you're trying to insert into table2a UserIDvalue that doesn't exist in table1.
这意味着您正在尝试插入table2一个UserID不存在于table1.
回答by Sandeep Giri
A simple hack can be to disable foreign key checks before performing any operation on the table. Simply query
一个简单的技巧是在对表执行任何操作之前禁用外键检查。简单查询
SET FOREIGN_KEY_CHECKS=0
This will disable foreign key matching against any other tables. After you are done with the table enable it again
这将禁用与任何其他表的外键匹配。完成表格后再次启用它
SET FOREIGN_KEY_CHECKS=1
This works for me a lot of times.
这对我有用很多次。
回答by NickZoic
I discovered another weird case: If you accidentally create a foreign key from an InnoDB table to a MyISAM table, MySQL throws this error at time of insert even if the data is otherwise valid.
我发现了另一个奇怪的情况：如果你不小心创建了一个从 InnoDB 表到 MyISAM 表的外键，即使数据是有效的，MySQL 在插入时也会抛出这个错误。
回答by Justin
You're getting this error because there are some value int table2.UserIDthat is not exists on table1.UserID(I guess that you have setted table2.UserIDvalue manualy before you created this foreign key).
One example for this scene: table1.UserIDget values 1,2,3 and table2.UserIDget values 4 (add by manual). So when you make a foreign key, they can't find UserID = 4from table1and the error will ocurse.
To fix this error, just remove UserID = 4from table2or you can empty both of them and then create the foreign key and.
Good luck!
您收到此错误是因为有些值 inttable2.UserID不存在table1.UserID（我猜您table2.UserID在创建此外键之前已手动设置了值）。
此场景的一个示例：table1.UserID获取值 1、2、3 并table2.UserID获取值 4（手动添加）。因此，当您创建外键时，他们找不到UserID = 4fromtable1并且会发生错误。
要修正这个错误，只是删除UserID = 4从table2或可以清空他们两个，然后创建外键。
祝你好运！
回答by Sbudah
This took me a while to figure out. Simply put, the table that references the other table already has data in it and one or more of its values does not exist in the parent table.
这花了我一段时间才弄明白。简单地说，引用另一个表的表中已经有数据，并且父表中不存在其一个或多个值。
e.g. Table2 has the following data:
例如表2有以下数据：
UserID PostID Title Summary
5 1 Lorem Ipsum dolor sit
Table1
表格1
UserID Password Username Email
9 ******** JohnDoe [email protected]
If you try to ALTER table2 and add a foreign key then the query will fail because UserID=5 doesn't exist in Table1.
如果您尝试 ALTER table2 并添加外键，则查询将失败，因为 Table1 中不存在 UserID=5。
回答by Attaque
If you have inserted a row into table 1 before creating the foreign key in table 2, then you will get a foreign key constraint error, because the auto increment value is 2 in table 1 and 1 in table 2. To solve this you have to truncate table 1 and set the auto increment value back to 1. Then you can add table 2.
如果在表 2 中创建外键之前已将行插入表 1，那么您将收到外键约束错误，因为表 1 中的自动增量值为 2，表 2 中为 1。要解决此问题，您必须截断表 1 并将自动增量值设置回 1。然后您可以添加表 2。
回答by Aman Maurya
Make sure you have set database engine to InnoDB because in MyISAM foreign key and transaction are not supported
确保您已将数据库引擎设置为 InnoDB，因为在 MyISAM 中不支持外键和事务
回答by Ujjwal Chaudhary
Just a little bit fix:Make the JoinColumn 'nullable = true' in Table1 and 'UserID' field 'insertable=false' and 'nullable=true' in Table2.
只是一点点修复：使表 1 中的 JoinColumn 'nullable = true' 和表 2 中的 'UserID' 字段 'insertable=false' 和 'nullable=true'。
In Table1 Entity:
在表 1 实体中：
@OneToMany(targetEntity=Table2.class, cascade = CascadeType.ALL)
@JoinColumn(name = "UserID", referencedColumnName = "UserID", nullable = true)
private List<Table2> table2List;
In Table2 Entity:
在表 2 实体中：
@Column(insertable = false, nullable = true)
private int UserID;
回答by Blaztix
I just had the same problem the solution is easy.
我刚刚遇到了同样的问题，解决方案很简单。
You are trying to add an id in the child table that does not existin the parent table.
您正在尝试在子表中添加父表中不存在的 id 。
check well, because InnoDB has the bug that sometimes increases the auto_increment column without adding values, for example, INSERT ... ON DUPLICATE KEY
检查好了，因为InnoDB有一个bug，有时会增加auto_increment列而不添加值，例如， INSERT ... ON DUPLICATE KEY
