Home/ Questions/How to solve: ''Cannot add or update a child row: a foreign key constraint fails'' error.
Next
Answered
viviankuphal
  • 3
Asked: 2022-06-19T08:52:26+00:00 In:

How to solve: ”Cannot add or update a child row: a foreign key constraint fails” error.

  • 3

. Advertisement .

..3..

. Advertisement .

..4..

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 Cities table:

+----+------------+
| 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!

a foreign key constraint fails
Share

1 Answer

  1. Best Answer
    Expert

    The cause:

    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.

    UPDATE `Friends` SET city_id = 5 WHERE `firstName` = 'John'; 
-- ERROR 1452 (23000): Cannot add or update a child row

    Solution:

    You can fix this error by adding a value into the reference table.

    INSERT INTO `Cities` VALUES (5, 'Liverpool');

-- Cities table:
+----+------------+
| id | city_name |
+----+------------+
| 1 | York |
| 2 | Manchester |
| 3 | London |
| 4 | Edinburgh |
| 5 | Liverpool |
    
+----+------------+

    Now you can take city_id value of 5 into the Friends table by inserting this row:

    INSERT INTO `Friends` (`firstName`, `city_id`) VALUES ('Susan', 5);

-- Query OK, 1 row affected (0.00 sec)

    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:

    SHOW GLOBAL VARIABLES LIKE 'FOREIGN_KEY_CHECKS';

-- +--------------------+-------+
-- | Variable_name | Value |
-- +--------------------+-------+
-- | foreign_key_checks | ON |
-- +--------------------+-------+

    Now you can stop the variable for the current or global session by:

    -- the current session must be set:
SET FOREIGN_KEY_CHECKS=0;

-- global session must be set:
SET GLOBAL FOREIGN_KEY_CHECKS=0;

    Now with no a foreign key constraint fails you can either UPDATE or INSERT rows in your table:

    INSERT INTO `Friends` (`firstName`, `city_id`) VALUES ('Natalia', 8);
-- Query OK, 1 row affected (0.01 sec)

UPDATE `Friends` SET city_id = 17 WHERE `firstName` = 'John';
-- Query OK, 1 row affected (0.00 sec)
-- Rows matched: 1 Changed: 1 Warnings: 0

    You also can set the FOREIGN_KEY_CHECKS running again by giving it value 1:

    -- set for the current session:
SET FOREIGN_KEY_CHECKS=1;

-- set globally:
SET GLOBAL FOREIGN_KEY_CHECKS=1;

    If you follow my suggestions, you can solve your error effectively. Good lucks!!!

    • 2

Leave an answer

You must login to add an answer.

Need An Account,