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

MySQLMySQLi Database

To understand error 1452, first we need to create a table and relate that to another table with the help of a foreign key constraint.

Creating the first table −

mysql> CREATE table ForeignTable
-> (
-> id int,
-> name varchar(200),
-> Fk_pk int
-> );
Query OK, 0 rows affected (0.43 sec)

After creating the first table successfully, we will create the second table −

mysql> CREATE table primaryTable1
-> (
-> Fk_pk int,
-> DeptName varchar(200),
-> Primary key(Fk_pk)
-> );
Query OK, 0 rows affected (0.48 sec)

Now, we have created both tables. Then both the tables are related with the help of alter command as well as adding foreign key constraint. The syntax is as follows −

alter table yourFirstTable add constraint anyConstraintName foreign key(column_name which is
acts foreign key in second table) yourSecondTable(column_name which acts primary key in
second table).

Now, the above query is used to relate both the tables. This is given as follows −

mysql> alter table ForeignTable add constraint constFKPK foreign key(Fk_pk) references
primaryTable1(Fk_pk);
Query OK, 0 rows affected (1.57 sec)
Records: 0 Duplicates: 0 Warnings: 0

Now, both the tables are related. The records are inserted into the table ‘foreignTable’ as follows −

mysql> INSERT into ForeignTable values(1,'John',1);

This results in an error that is shown in the below output −

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
(`business`.`foreigntable`, CONSTRAINT `constFKPK` FOREIGN KEY (`Fk_pk`)
REFERENCES `primarytable1` (`fk_pk`))

In the above output, we are getting the error ‘Cannot add or update a child row: a foreign key constraint fails’. We can remove this error by inserting the records into the table primaryTable1 as shown below −

mysql> INSERT into primaryTable1 values(1,'ComputerScience');
Query OK, 1 row affected (0.14 sec)

After inserting the records into the table primaryTable1, we can insert the required records into the table ForeignTable without any errors. This is shown below −

mysql> INSERT into ForeignTable values(1,'John',1);
Query OK, 1 row affected (0.13 sec)

Now, we can display the table records of ForeignTable with the help of the select command, which is as follows −

mysql> SELECT * from ForeignTable;

The output of the above query is −

+------+------+-------+
| id   | name | Fk_pk |
+------+------+-------+
| 1    | John | 1     |
+------+------+-------+
1 row in set (0.00 sec)

We can also display the table records of primarytable1 with the help of the select command, which is as follows −

mysql> SELECT * from primarytable1;

The output of the above query is −

+-------+-----------------+
| Fk_pk | DeptName        |
+-------+-----------------+
| 1     | ComputerScience |
+-------+-----------------+
1 row in set (0.00 sec)

The error 1452 - Cannot add or update a child row: a foreign key constraint fails occurs when data record is initially inserted into the ForeignTable.

Note: First, add the record into the second table i.e primarytable1 to avoid the above error.
raja
Published on 18-Oct-2018 12:40:52
Advertisements