Cannot Add Or Update A Child Row A Foreign Key Constraint Fails Mysql
In this post will you learn in details of Cannot Add Or Update A Child Row A Foreign Key Constraint Fails Mysql.
When we apply a foreign key constraint between tables and insert records into the child table, we get this error. Let’s look at an example.
The kiddie table is being built.
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
When we put records into the first table or child table after creating a foreign key constraint, we 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 problem occurs when you try to add a row to a table that does not have a corresponding row.
As already indicated
“A foreign key connection consists of a parent table containing the central data values and a child table containing identical values referring back to the parent. The child table specifies the FOREIGN KEY clause. If there is no matching candidate key value in the parent table, every INSERT or UPDATE operation that attempts to create a foreign key value in a child table will be rejected.”