Error 1452 (23000): cannot add or update a child row: a foreign key constraint fails

Question

Problem :

When I try to execute the below SQL command :

INSERT INTO mytest_usershosts (RID,userid,hid,Usr,Pass) 

VALUES (NULL,1,1,"user","pass");

I am facing the below error message :

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f

ails (`dm`.`mytest_usershosts`, CONSTRAINT `mytest_usershosts_ibfk_1` FOREIGN KEY (`

userid`) REFERENCES `mytest_users` (`userid`) ON DELETE CASCADE ON UPDATE CASCADE)

 

I have already looked into many similar cases, but I am unable to find any solution.

Is there anyone who have faced the same issue and knows the fix for the issue?

Answer 1

The reason behind you facing that error is that you are trying to insert the record on the table mytest_usershosts but the value of the userID is not present on your table mytest_users. Same for the value of hid it is also not present on table mytest_hosts.

Table mytest_usershosts is dependent on the tables: mytest_users and mytest_hosts. So please make sure that while inserting the records on the table mytest_usershosts, the values for hid and userid should already be available on the following parent tables: mytest_users and mytest_hosts.

Now try executing below query and surely it will be inserted.

INSERT INTO mytest_usershosts (RID,userid,hid,Usr,Pass)

VALUES (NULL,1120,30,'user','pass');

Also the  AUTO_INCREMENT option on the tables: mytest_users and mytest_hosts, are needless as you are supplying the values on every query that you are executing on the two tables.