. Advertisement .
. Advertisement .
Hello, everyone. I am having a trouble with: ”Cannot add or update a child row: a foreign key constraint fails”, but I don’t know how to fix it. I have the following
+----+------------+ | id | city_name | +----+------------+ | 1 | York | | 2 | Manchester | | 3 | London | | 4 | Edinburgh | +----+------------+
I want to save my friends’ information who live in these differrent countries, so I create a table as bellow:
CREATE TABLE `Friends` ( `firstName` varchar(255) NOT NULL, `city_id` int unsigned NOT NULL, PRIMARY KEY (`firstName`), CONSTRAINT `friends_ibfk_1` FOREIGN KEY (`city_id`) REFERENCES `Cities` (`id`) )
Then I want to insert the 5th value of the
city_id column, I get an warning message:
INSERT INTO `Friends` (`firstName`, `city_id`) VALUES ('John', 5);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test_db`.`friends`, CONSTRAINT `friends_ibfk_1` FOREIGN KEY (`city_id`) REFERENCES `cities` (`id`))
Does anyone have suggestions for me to solve this error? Please write below. Thanks!
After I research your problem for hours, I found that you are attempting to put into the table an value which doesn’t exist in the referencing table. Therefore, you get the: ”Cannot add or update a child row: a foreign key constraint fails” error.
You also get this error if you attempt to update the Friends row with a city_id value which is invalid.
You can fix this error by adding a value into the reference table.
Now you can take
Friendstable by inserting this row:
Another way to solve your error is disabling the FOREIGN_KEY_CHECKS in server which you are using. To check whether the variable is running or not, you can do as follow:
Now you can stop the variable for the current or global session by:
Now with no
a foreign key constraint failsyou can either
INSERTrows in your table:
You also can set the FOREIGN_KEY_CHECKS running again by giving it value 1:
If you follow my suggestions, you can solve your error effectively. Good lucks!!!