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

In MySQL, Enter a row in the child table without having row in the parent table

Example of insert row in Child table without having a row in Parent table MySQL

  1. Create the table Parent P1 and Child C1.
mysql> create table p1(id integer primary key, name varchar(100));
Query OK, 0 rows affected (0.09 sec)

mysql> create table c1(cid integer primary key, pid integer, foreign key (pid) references p1(id));
Query OK, 0 rows affected (0.09 sec)

2. Insert data in the Parent and child table and child table throw error due to not presence of data in the parents table.

mysql> insert into p1 values (1,'a');
Query OK, 1 row affected (0.03 sec)

mysql> insert into p1 values (2,'b');
Query OK, 1 row affected (0.01 sec)

mysql> insert into c1 values (2,5);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`c1`, CONSTRAINT `c1_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `p1` (`id`))

3. Disable the foreign key check and enable it.

mysql> SET FOREIGN_KEY_CHECKS = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into c1 values (3,5);
Query OK, 1 row affected (0.02 sec)

mysql> SET FOREIGN_KEY_CHECKS = 1;
Query OK, 0 rows affected (0.00 sec)

4. After enabling the foreign key check, the insert query throw error again.

mysql> insert into c1 values (4,5);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`c1`, CONSTRAINT `c1_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `p1` (`id`))

5. Verify the data in both P1 and C1 tables.

mysql> select * from c1;
+-----+------+
| cid | pid  |
+-----+------+
|   3 |    5 |
+-----+------+
1 row in set (0.00 sec)

mysql> select * from p1;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
+----+------+
2 rows in set (0.00 sec)