ERROR 1452 happens when you try to execute a data manipulation query into a table that has one or more failing foreign key constraints.
The cause of this error is the values you’re trying to put into the table are not available in the referencing (parent) table.
Let’s see an example of this error with two MySQL tables.
Suppose you have a
Cities table that contains the following data:
+----+------------+ | id | city_name | +----+------------+ | 1 | York | | 2 | Manchester | | 3 | London | | 4 | Edinburgh | +----+------------+
Then, you create a
Friends table to keep a record of people you know who lives in different cities.
You reference the
id column of the
Cities table as the
FOREIGN KEY of the
city_id column in the
Friends table as follows:
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`) )
In the code above, a
friends_ibfk_1 is created for the
city_id column, referencing the
id column in the
CONSTRAINT means that only values recoded in the
id column can be inserted into the
(To avoid confusion, I have omitted the
id column from the
Friends table. In real life, You may have an
id column in both tables, but a
FOREIGN KEY constraint will always refer to a different table.)
When I try to insert
5 as the value of the
city_id column, I will trigger the error as shown below:
INSERT INTO `Friends` (`firstName`, `city_id`) VALUES ('John', 5);
The response from MySQL:
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`))
As you can see, the error above even describes which constraint you are failing from the table.
Based on the
Cities table data above, I can only insert numbers between
4 for the
city_id column to make a valid
INSERT INTO `Friends` (`firstName`, `city_id`) VALUES ('John', 1); -- Query OK, 1 row affected (0.00 sec)
The same error will happen when I try to update the
Friends row with a
city_id value that’s not available.
Take a look at the following example:
UPDATE `Friends` SET city_id = 5 WHERE `firstName` = 'John'; -- ERROR 1452 (23000): Cannot add or update a child row
There are two ways you can fix the
ERROR 1452 in your MySQL database server:
- You add the value into the referenced table
- You disable the
FOREIGN_KEY_CHECKSin your server
The first option is to add the value you need to the referenced table.
In the example above, I need to add the
id value of
5 to the
INSERT INTO `Cities` VALUES (5, 'Liverpool'); -- Cities table: +----+------------+ | id | city_name | +----+------------+ | 1 | York | | 2 | Manchester | | 3 | London | | 4 | Edinburgh | | 5 | Liverpool | +----+------------+
Now I can insert a new row in the
Friends table with the
city_id value of
INSERT INTO `Friends` (`firstName`, `city_id`) VALUES ('Susan', 5); -- Query OK, 1 row affected (0.00 sec)
Disabling the foreign key check
The second way you can fix the
ERROR 1452 issue is to disable the
FOREIGN_KEY_CHECKS variable in your MySQL server.
You can check whether the variable is active or not by running the following query:
SHOW GLOBAL VARIABLES LIKE 'FOREIGN_KEY_CHECKS'; -- +--------------------+-------+ -- | Variable_name | Value | -- +--------------------+-------+ -- | foreign_key_checks | ON | -- +--------------------+-------+
This variable causes MySQL to check any foreign key constraint added to your table(s) before inserting or updating.
You can disable the variable for the current session only or globally:
-- set for the current session: SET FOREIGN_KEY_CHECKS=0; -- set globally: SET GLOBAL FOREIGN_KEY_CHECKS=0;
Now you can
UPDATE rows in your table without triggering
a foreign key constraint fails:
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
After you’re done with the manipulation query, you can set the
FOREIGN_KEY_CHECKS active again by setting its value to
-- set for the current session: SET FOREIGN_KEY_CHECKS=1; -- set globally: SET GLOBAL FOREIGN_KEY_CHECKS=1;
But please be warned that turning off your
FOREIGN_KEY_CHECKS variable will cause the
city_id column to reference a
NULL column in the
It may cause problems when you need to perform a
JOIN query later.
Now you’ve learned the cause of
ERROR 1452 and how to resolve this issue in your MySQL database server. Great work! 👍