错误1452(23000):无法添加或更新子行:外键约束失败 [英] ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails

问题描述

当我执行以下SQL命令时:

When I execute the following SQL command:

INSERT INTO test_usershosts (RID,userid,hid,Usr,Pass) 
VALUES (NULL,1,1,"user","pass");

我收到以下错误消息:

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)

我的桌子:

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.

推荐答案

得到该异常的原因是因为您正在表test_usershosts上插入一条记录，而userID的值不在表上test_users.表test_hosts上也没有与hid相同的值.

The reason why you are getting that exception is because you are inserting a record on table test_usershosts which the value of the userID is not present on table test_users. Same as the value of hid is not also present on table test_hosts.

表test_usershosts取决于表:test_users和test_hosts.因此，请确保在表test_usershosts上插入记录时，hid和userid的值已在父表上存在:test_users和test_hosts.

Table test_usershosts is dependent on tables: test_users and test_hosts. So be sure that when inserting records on table test_usershosts, the values for hid and userid already exists on the parent tables: test_users and 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');

• SQLFiddle演示
• SQLFiddle Demo

我看到在表test_users和test_hosts上不需要AUTO_INCREMENT选项，因为您要在两个表上执行的每个查询中都提供值.

I see that AUTO_INCREMENT option on tables: test_users and test_hosts, are not needed since you are supplying values on every query you are executing on the two tables.

这篇关于错误1452(23000):无法添加或更新子行:外键约束失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！