Cannot add or update a child row: a foreign key constraint fails while data exists

Question

I am trying to add a Foreign key contraints and it fails:

mysql> alter table order_info add constraint FKlnh846un1oe6hnwkqf5ovtjwo
foreign key (orderId) references orders (orderId);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails 
(`storefront`.`#sql-1601_28`, CONSTRAINT `FKlnh846un1oe6hnwkqf5ovtjwo`
 FOREIGN KEY (`orderId`) REFERENCES `orders` (`orderId`))




mysql> show create table orders;
| orders | CREATE TABLE `orders` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `amount` float NOT NULL,
  `couponCode` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `createdAt` datetime DEFAULT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `orderId` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `phone` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `status` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `updatedAt` datetime DEFAULT NULL,
  `address_id` int(11) DEFAULT NULL,
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  `pGateway` varchar(255) COLLATE utf8_unicode_ci DEFAULT 'PAYU',
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK_9erxssgysqmn8axwyq4er6hen` (`orderId`),
  KEY `FKf5464gxwc32ongdvka2rtvw96` (`address_id`),
  KEY `email_status_index` (`email`,`status`),
  KEY `createdAt_index` (`createdAt`),
  KEY `status_index` (`status`),
  CONSTRAINT `FKf5464gxwc32ongdvka2rtvw96` FOREIGN KEY (`address_id`) REFERENCES `address` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2961655 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |




mysql> show create table order_info;
| order_info | CREATE TABLE `order_info` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `createdAt` datetime DEFAULT NULL,
  `orderId` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `packageId` int(11) NOT NULL,
  `updatedAt` datetime DEFAULT NULL,
  `amount` int(11) NOT NULL,
  `status` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `expiry` datetime DEFAULT NULL,
  `child_id` int(11) DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1916085 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |



mysql> select count(*) from orders;
+----------+
| count(*) |
+----------+
|  2773095 |
+----------+
1 row in set (0.69 sec)

mysql> select count(*) from order_info;
+----------+
| count(*) |
+----------+
|  1914367 |
+----------+
1 row in set (0.61 sec)

mysql> select orderId from order_info where orderId not in (select orderId from orders);
Empty set (5.07 sec)

mysql> select count(*) from order_info where orderId is null;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.63 sec)

Now, both tables have data. There exist no such orderId which does not exists in orders table. The data type of both columns is same. So why does this gives an error? What exactly is being violated? Although I can set foreign key checks as false and then create the key, I want to know why this constraint is failing.

EDIT: Added the last query to verify that the column that is to become a foreign key does not have null values.

`SELECT COUNT(*) FROM order_info WHERE orderId IS NULL` <-- does this return a positive count? — Tim Biegeleisen, Feb 20 '19 at 10:28
This is because in table order_info, you allready have rows that violate the foreign key constraint. Try to look for rows in order_info that don 't have the correspondent row (orderId) in orders — nacho, Feb 20 '19 at 10:29
@nacho Check the output of last query. There are no orderIds in order_info that are already not there in orders table. — Nikhil Sahu, Feb 20 '19 at 10:41
@TimBiegeleisen Any ideas what possibilities can I check in the data/indexes to understand why this is happening? — Nikhil Sahu, Feb 20 '19 at 10:46

score 0 · Answer 1 · answered Feb 20 '19 at 14:54

It seems the query:

select orderId from order_info where orderId not in (select orderId from orders)

is not the best candidate to know faulty data points. NOT EXISTS is better as suggest by @paul-spiegel. Here is the query output:

mysql> select count(*) from order_info oi where not exists 
( select null from orders o where o.orderId = oi.orderId);
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (31.27 sec)

There were 6 rows in order_info whose orderId was non existent in orders table.

Documentation for NOT EXISTS : https://dev.mysql.com/doc/refman/5.7/en/exists-and-not-exists-subqueries.html

Relevant: SELECT * WHERE NOT EXISTS

Cannot add or update a child row: a foreign key constraint fails while data exists

1 Answers1