MySQL Database generate ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails

Posted on

This is another article written in order to handle error which is generated from MySQL Database Server. The error as specified in the title is ‘Cannot add or update a child row: a foreign key constraint fails’. The error itself is triggered when a query is being executed. In the context of this article, the query which is being executed is performed to add a record in the table.

The scenario is actually similar or in other words it is the scenario which is used in other article titled ‘MySQL Database generate ERROR 1215 (HY000): Cannot add foreign key constraint‘ which can be visited in this link.

The relationship between tables can be viewed in the following image :

MySQL Database generate ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
MySQL Database generate ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails

Basically, the scenario is following the above ERD (Entity Relationship Diagram) with the availability of three tables. The error triggered when a query which is performed to insert a new record is being executed in table user_role. Below is the example of the query executed through MySQL Command Console :

mysql> insert into user_role(id_user,id_role) values(7,1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db_app`.`user_role`, CONSTRAINT `user_role_fk1` FOREIGN KEY (`id_user`) REFERENCES `user` (`id_user`) ON DELETE CASCADE ON UPDATE CASCADE)
mysql> 

The following is actually a command which is can be performed to solve the above problem :

mysql> set  foreign_key_checks=0;
Query OK, 0 rows affected (0,01 sec)

After performing the above query, try to add the record again by executing the following query to add a new record :

mysql> insert into user_role(id_user,id_role) values(7,1);
Query OK, 1 row affected (0,02 sec)

mysql> insert into user_role(id_user,id_role) values(7,2);
Query OK, 1 row affected (0,02 sec)

mysql> insert into user_role(id_user,id_role) values(8,2);
Query OK, 1 row affected (0,07 sec)

mysql> insert into user_role(id_user,id_role) values(8,3);
Query OK, 1 row affected (0,01 sec)

So, based on the above output, the query has already successfully executed. Try to select the already inserted record in the above by executing the following query :

mysql> select * from user_role;
+---------+---------+
| id_user | id_role |
+---------+---------+
|       7 |       1 |
|       7 |       2 |
|       8 |       2 |
|       8 |       3 |
+---------+---------+
4 rows in set (0,01 sec)

mysql> 

So, based on the output presented above as a query result, the query for adding a new record has executed succesfuly.

2 thoughts on “MySQL Database generate ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails

Leave a Reply