MySQL error 1452 - Cannot add or update a child row: a foreign key constraint fails?

This error comes whenever we add a foreign key constraint between tables and insert records into the child table. Let us see an example.

Creating the child table.

mysql> create table ChildDemo
   -> (
   -> id int,
   -> FKPK int
   -> );
Query OK, 0 rows affected (0.86 sec)

Creating the second table.

mysql> create table ParentDemo
   -> (
   -> FKPK int,
   -> Name varchar(100)
   -> ,
   -> primary key(FKPK)
   -> );
Query OK, 0 rows affected (0.57 sec)

To add foreign key constraint.

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

After creating foreign key constraint, whenever we insert records into the first table or child table, we will get the above error.

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`))

The error comes when you are trying to add a row for which no matching row in in the other table.

As stated

“Foreign key relationships involve a parent table that holds the central data values, and a child table with identical values pointing back to its parent. The FOREIGN KEY clause is specified in the child table. It will reject any INSERT or UPDATE operation that attempts to create a foreign key value in a child table if there is no a matching candidate key value in the parent table.”

Chandu yadav

e

Updated on: 14-Sep-2023

28K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started