MySQL ERROR 1452 (23000): 无法添加或更新子行：外键约束失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13891879/
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
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
提问by Himal
When I execute the following SQL command:
当我执行以下 SQL 命令时：
INSERT INTO test_usershosts (RID,userid,hid,Usr,Pass)
VALUES (NULL,1,1,"user","pass");
I'm getting the following error message:
我收到以下错误消息：
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`dm`.`test_usershosts`, CONSTRAINT `test_usershosts_ibfk_1` FOREIGN KEY (`
userid`) REFERENCES `test_users` (`userid`) ON DELETE CASCADE ON UPDATE CASCADE)
My tables:
我的表：
CREATE TABLE IF NOT EXISTS `test_users` (
`userid` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(10) NOT NULL DEFAULT '',
`password` varchar(50) NOT NULL DEFAULT '',
PRIMARY KEY (userid)
) ENGINE=InnoDB;
INSERT INTO `test_users` (`userid`, `username`, `password) VALUES
(1120, 'guest', '12250170a9624f336ca24');
CREATE TABLE IF NOT EXISTS `test_hosts` (
`hid` int(11) NOT NULL AUTO_INCREMENT,
`UID` int(11) NOT NULL,
`name` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (hid)
) ENGINE=InnoDB;
INSERT INTO `test_hosts` (`hid`, `UID`, `name`) VALUES (30, 10, 'MU');
CREATE TABLE IF NOT EXISTS `test_usershosts` (
`RID` int(11) NULL AUTO_INCREMENT,
`userid` int(11) ,
`hid` int(11) ,
`Usr` varchar(100) ,
`Pass` varchar(100) ,
PRIMARY KEY (RID),
INDEX (userid),
INDEX (hid),
FOREIGN KEY (userid) REFERENCES test_users (userid)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (hid) REFERENCES test_hosts (hid)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;
I've looked into many similar cases, but couldn't find any solution. Any help would be appreciated.
我调查了许多类似的案例，但找不到任何解决方案。任何帮助，将不胜感激。
回答by John Woo
The reason why you are getting that exception is because you are inserting a record on table test_usershostswhich the value of the userIDis not present on table test_users. Same as the value of hidis not also present on table test_hosts.
您收到该异常的原因是因为您在 table 上插入了一条记录，而 tabletest_usershosts上userID不存在的值test_users。与hid表中不存在的值相同test_hosts。
Table test_usershostsis dependent on tables: test_usersand test_hosts. So be sure that when inserting records on table test_usershosts, the values for hidand useridalready exists on the parent tables: test_usersand test_hosts.
表test_usershosts依赖于表：test_users和 test_hosts。所以，要确保在表中插入记录时test_usershosts，该值hid与userid存在于父表已经：test_users和 test_hosts。
Try executing this query and surely it will be inserted.
尝试执行此查询，它肯定会被插入。
INSERT INTO test_usershosts (RID,userid,hid,Usr,Pass)
VALUES (NULL,1120,30,'user','pass');
I see that AUTO_INCREMENToption on tables: test_usersand test_hosts, are not needed since you are supplying values on every query you are executing on the two tables.
我AUTO_INCREMENT在表上看到该选项：test_usersand test_hosts，因为您在两个表上执行的每个查询都提供值，所以不需要。
回答by w770115
In test_usersguest's UserIDis 1120and
in test_hostshid = 30
在test_users客人的UserIDis1120和test_hostshid = 30
you must change
你必须改变
INSERT INTO test_usershosts (RID,userid,hid,Usr,Pass) VALUES (NULL,1,1,"user","pass");
to:
到：
INSERT INTO test_usershosts (RID,userid,hid,Usr,Pass) VALUES (NULL,1120,30,"user","pass");
回答by ramiro
in my case in case it serves someone. I was struggling to find the solution what usually happens when making a primary key is also marked as unique. but in my case it was only marked as primary and it was not marked as unique, until I noticed that the option of unica was "open" and I clicked it, until then I let myself make the relationship.
在我的情况下，它为某人服务。我一直在努力寻找解决方案，当主键也被标记为唯一时，通常会发生什么。但在我的例子中，它只被标记为主要的，并没有被标记为唯一的，直到我注意到 unica 的选项是“打开的”并点击它，直到那时我让自己建立关系。
Then for the record ... check that your primary keys are also unique.
然后作为记录......检查您的主键是否也是唯一的。
