. Advertisement .
..3..
. Advertisement .
..4..
Error 1452 in MySQL is a common problem when there is a foreign key constraint between insert records and tables. Here is the example of this error:
Child table:
mysql> create table ChildDemo
-> (
-> id int,
-> FKPK int
-> );
Query OK, 0 rows affected (0.86 sec)
Second table:
mysql> create table ParentDemo
-> (
-> FKPK int,
-> Name varchar(100)
-> ,
-> primary key(FKPK)
-> );
Query OK, 0 rows affected (0.57 sec)
mysql> alter table ChildDemo add constraint ConstChild foreign key(FKPK) references ParentDemo(FKPK);
Query OK, 0 rows affected (1.97 sec)
Records: 0 Duplicates: 0 Warnings: 0
Inserting records into the child table will offer the following result:
mysql> insert into ChildDemo values(1,3);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`business`.`childdemo`, CONSTRAINT `ConstChild` FOREIGN KEY (`FKPK`) REFERENCES `parentdemo` (`fkpk`))
So how to fix error 1452: cannot add or update a child row: a foreign key constraint fails? Here is the answer!
How To Fix The Error
There are two ways to fix the error:
Method 1: Add Value
You can add the value to the referenced table to solve the problem. To clarify this method, let’s look at the following example:
| id | city_name |
+----+------------+
| 1 | York |
| 2 | Manchester |
| 3 | London |
| 4 | Edinburgh |
+----+------------+
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`)
)
INSERT INTO `Friends` (`firstName`, `city_id`) VALUES ('John', 5);
Here you will need to add the id value of 5:
INSERT INTO `Cities` VALUES (5, 'Liverpool');
-- Cities table:
+----+------------+
| id | city_name |
+----+------------+
| 1 | York |
| 2 | Manchester |
| 3 | London |
| 4 | Edinburgh |
| 5 | Liverpool |
+----+------------+
Then add a new row in this Friends table:
INSERT INTO `Friends` (`firstName`, `city_id`) VALUES ('Susan', 5);
-- Query OK, 1 row affected (0.00 sec)
Method 2: Disable The Foreign Key Check
When the FOREIGN_KEY_CHECKS variable disabled, you can also fix this error. First and foremost, check the status of the variable:
SHOW GLOBAL VARIABLES LIKE ‘FOREIGN_KEY_CHECKS’;
-- +--------------------+-------+
-- | Variable_name | Value |
-- +--------------------+-------+
-- | foreign_key_checks | ON |
-- +--------------------+-------+
Run the following code to disable the variable:
-- set for the current session:
SET FOREIGN_KEY_CHECKS=0;
-- set globally:
SET GLOBAL FOREIGN_KEY_CHECKS=0;
Now you can update or insert new rows without getting the error 1452.
Conclusion
To sum up, the error code 1452 prevents you from adding or updating a child row. This tutorial has introduced you to two methods to fix this error: cannot add or update a child row: a foreign key constraint fails.
Leave a comment