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

Question

I am having a foreign key problem although I have already added the necessary information in the parent table. Here's the schema, the select statement to show that the foreign key exists in the parent table and the error inserting the new data in the child table:

MariaDB [scannedmusic]> show create table chhymnal;

+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| chhymnal | CREATE TABLE `chhymnal` (<br>
  `CHhymnalID` int(5) unsigned NOT NULL,<br>
  `Title` varchar(30) DEFAULT NULL,<br>
  `CHNumber` int(5) unsigned DEFAULT NULL,<br>
  `Type` varchar(1) DEFAULT NULL,<br>
  `WorshipItemType` varchar(30) DEFAULT NULL,<br>
  `DateLastSung` date DEFAULT NULL,<br>
  `HighNote` varchar(5) DEFAULT NULL,<br>
  `UseThisWeek` tinyint(1) DEFAULT NULL,<br>
  `ServiceOrder` int(2) unsigned DEFAULT NULL,<br>
  `StartKey` varchar(2) DEFAULT NULL,<br>
  `EndKey` varchar(2) DEFAULT NULL,<br>
  `Tempo` int(1) unsigned DEFAULT NULL,<br>
  `TimeSig` varchar(7) DEFAULT NULL,<br>
  `CCLINumb` varchar(10) DEFAULT NULL,<br>
  PRIMARY KEY (`CHhymnalID`)<br>
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |<br>
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

MariaDB [scannedmusic]> MariaDB [scannedmusic]> show create table chimages;

+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                       |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| <br>chimages | CREATE TABLE `chimages` (<br>
  `CHImageID` int(5) unsigned NOT NULL,<br>
  `ImagePath` varchar(50) DEFAULT NULL,<br>
  `Instrument` varchar(30) DEFAULT NULL,<br>
  `CHhymnalID` int(5) DEFAULT NULL,<br>
  PRIMARY KEY (`CHImageID`),<br>
  CONSTRAINT `fk_images` FOREIGN KEY (`CHImageID`) REFERENCES `chhymnal` (`CHhymnalID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+<br>
1 row in set (0.00 sec)
MariaDB [scannedmusic]> SELECT * FROM `chhymnal` WHERE `CHhymnalID` = 343;
+------------+-------------------------+----------+------+-----------------+--------------+----------+-------------+--------------+----------+--------+-------+---------+----------+
| CHhymnalID | Title                   | CHNumber | Type | WorshipItemType | DateLastSung | HighNote | UseThisWeek | ServiceOrder | StartKey | EndKey | Tempo | TimeSig | CCLINumb |<br>
+------------+-------------------------+----------+------+-----------------+--------------+----------+-------------+--------------+----------+--------+-------+---------+----------+
|        343 | What a Day That Will Be |      762 | h    | Hymn            | 0000-00-00   | Db       |           0 |           12 | Ab       | Ab     |     3 | 3/4     |          |
+------------+-------------------------+----------+------+-----------------+--------------+----------+-------------+--------------+----------+--------+-------+---------+----------+

1 row in set (0.00 sec)

MariaDB [scannedmusic]> INSERT INTO <br>Chimages(`CHImageID`,`ImagePath`,`CHhymnalID`) VALUES  ( 393, "\\BMP\\762_o1.bmp",343);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (scannedmusic.chimages, CONSTRAINT fk_images FOREIGN KEY (CHImageID) REFERENCES chhymnal (CHhymnalID))

Answer 1

You are looking at wrong columns. In table chhymnal there is no row with chhymnalid = 393.

Your SELECT statement suggest that you are checking your chhymnalid from chhymnal table, but your foreign key constraint works on chimageid that has 393 value in your INSERT statement.

There are two options - one of which must be true:

1. You meant what you typed when creating foreign key constraint and there is no row with chhymnalid = 393 in table chhymnal
2. You have mistaken column name reference in foreign key constraint and it should be chhymnalid instead of chimageid