ERROR 1452 (23000): Unable to add or update child row: foreign key constraint not implemented

Question

When I execute the following SQL command:

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

The following error message appears:

 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 examined many similar cases, but did not find a solution. Any help would be appreciated.

Answer 1

The reason you get this exception is because you are inserting an entry into the test_usershosts table test_usershosts value is not in the test_users table. The same as the hid value is also not present in the test_hosts table.

The table test_usershosts depends on the tables: test_users and test_hosts . Therefore, make sure that when inserting records into the test_usershosts table test_usershosts values ​​for hid and userID already exist in the parent tables: test_users and test_hosts .

Try this query and of course it will be inserted.

 INSERT INTO test_usershosts (RID,userid,hid,Usr,Pass) VALUES (NULL,1120,30,'user','pass'); 

• SQLFiddle Demo

I see that the AUTO_INCREMENT option for tables: test_users and test_hosts not needed, because you set values ​​for each query that you execute in two tables.

Answer 2

There are 1120 in test_users guest UserID and hid = 30 in test_hosts

you have to change

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

in

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

Answer 3

in my case, if he serves someone. I struggled to find a solution that usually occurs when creating a primary key, also referred to as unique. but in my case it was marked as primary, and it was not marked as unique until I noticed that the unica option was “open”, and I clicked it until I allowed myself to make a relationship.

Then for the record ... make sure your primary keys are also unique.