title: Cannot add or update a child row: a foreign key constraint fails while data exists
body: <p>I am trying to add a Foreign key contraints and it fails:</p>
  
  <pre><code>mysql&gt; 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&gt; 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&gt; 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&gt; select count(*) from orders;
  +----------+
  | count(*) |
  +----------+
  |  2773095 |
  +----------+
  1 row in set (0.69 sec)
  
  mysql&gt; select count(*) from order_info;
  +----------+
  | count(*) |
  +----------+
  |  1914367 |
  +----------+
  1 row in set (0.61 sec)
  
  mysql&gt; select orderId from order_info where orderId not in (select orderId from orders);
  Empty set (5.07 sec)
  
  mysql&gt; select count(*) from order_info where orderId is null;
  +----------+
  | count(*) |
  +----------+
  |        0 |
  +----------+
  1 row in set (0.63 sec)
  </code></pre>
  
  <p>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.</p>
  
  <p>EDIT:
  Added the last query to verify that the column that is to become a foreign key does not have null values.</p>