MySQL Error Message Cannot add or update a child row: a foreign key constraint fails

This is an article written concerning on an error generated as part of an output resulted upon on executing a specific SQL Query in a MySQL command console. As stated in the title, the error message given as a reply from the specific SQL Query in MySQL command console is “Cannot add or update a child row: a foreign key constraint fails”.

The complete error is shown in the following output :

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`research`.`posts`, CONSTRAINT `posts_ibfk_1` FOREIGN KEY (`id_subcategory`) REFERENCES `subcategory` (`id_subcategory`))

The above output is an output which is triggered from the execution of a specific query which is taken as an example :

mysql> insert into posts(title,state,id_category,id_subcategory,id_maincategory) values('7 Ways to Improve your Wealth',2,2,'Posted',0);

To display the existing foreign key in the table mentioned in the previous SQL Query, below is an effort to show it by executing a query shown below :

mysql> desc posts;
+----------------+-------------+-----+----+--------+-------------+
| Field          | Type        | Null| Key| Default|Extra        |
+----------------+-------------+-----+----+--------+-------------+
| id_post        | int(11)     | NO  | PRI| NULL  |auto_increment|
| title          | varchar(255)| YES |    | NULL  |              |
| state          | varchar(255)| YES |    | NULL  |              |
| id_category    | int(11)     | NO  |    | NULL  |              |
| id_subcategory | int(11)     | NO  | MUL| NULL  |              |
| id_maincategory| int(11)     | NO  |    | NULL  |              |
+----------------+-------------+-----+----+-------+--------------+
6 rows in set (0,04 sec)

The effort shown by executing the query above doesn’t describe any expected result because there isn’t any information available above relates on any foreign key being exist in the table.

One of an effective way to do it is by executing the following query :

mysql> show create table posts;
+-------+---------------------------------------------------------+
| Table | Create Table                                            |
+-----------------------------------------------------------------+
| posts | CREATE TABLE `posts` (
  `id_post` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT NULL,
  `state` varchar(255) DEFAULT NULL,
  `id_category` int(11) NOT NULL,
  `id_subcategory` int(11) NOT NULL,
  `id_maincategory` int(11) NOT NULL,
  PRIMARY KEY (`id_post`),
  KEY `id_subcategory` (`id_subcategory`),
  CONSTRAINT `posts_ibfk_1` FOREIGN KEY (`id_subcategory`) 
REFERENCES `subcategory` (`id_subcategory`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1          |
+-------+---------------------------------------------------------+
1 row in set (0,04 sec)
mysql> 

The error is about the execution of a certain query for inserting a new record fails to meet the constraint of foreign key available in the table. There are several errors on inserting a new record which can trigger the error, such as :

The data being passed to the column represent the foreign key column doesn’t fit with the data type of that foreign key column itself. A column with normally an integer data type is passed with a character data type as shown previously. Look carefully at the insert query executed as follows :

mysql> insert into posts(title,state,id_category,id_subcategory,id_maincategory) values('7 Ways to Improve your Wealth',2,2,'Posted',0);

The column ‘id_subcategory’ which is a foreign key column is being inserted with a character value ‘Posted’. To correct the mistake of the above insert query, just change it into the following format :

mysql> insert into posts(title,state,id_category,id_subcategory,id_maincategory) values('7 Ways to Improve your Wealth','Posted', 2,2,0);

Just remember carefully to check the existance of its value in its associated table which is used as a foreign key reference and in this context it is the subcategory table on the id_subcategory column as specified in the output of ‘show create table posts’ :

CONSTRAINT `posts_ibfk_1` FOREIGN KEY (`id_subcategory`) REFERENCES `subcategory` (`id_subcategory`) 

The problem has been discussed actually in an article titled ‘MySQL Database generate ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails’ which can be visited in the following link.