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

Question

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!

lyytutoria · Accepted Answer

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

Sign Up

Sign In

Forgot Password

ITtutoria Latest Questions

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

How to avoid the error “cannot delete or update a parent row: a foreign key constraint fails”?