Error : Cannot add foreign key constraint

Question

Problem

I have two tables. The first one is populated; the second one is empty.

I want the second one to have a foreign key which references a column in the first one.

My understanding is that it should be possible as long as:

• Both tables have the same Engine
• Both columns have the same Datatype
• Both columns have the same Length
• Both columns have the same Collation
• Both columns have the same Character Set
• The parent column has a Unique key
• The parent column has a matching value for every value in the child column

In my case, all of these conditions are true, but MySQL still will not allow a foreign key relationship.

What other condition(s) need to be met?

Example

Note: This example gives a general idea of the situation, but it will not reproduce the error.

Schema:

CREATE TABLE `parents` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4

CREATE TABLE `kids` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `parent_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `parent_id` (`parent_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4

parents is populated. kids is empty.

kids.parent_id cannot become a foreign key of parents.id:

ALTER TABLE `kids` ADD FOREIGN KEY (`parent_id`) REFERENCES `parents` (`id`);
-- Error : Cannot add foreign key constraint

Failed Solutions

MySQL does not provide a reason for the error; SHOW ENGINE INNODB STATUS returns nothing:

SHOW ENGINE INNODB STATUS;
-- [Type]       [Name]      [Status]
-- InnoDb

I have the needed database permissions.

I've double checked that the columns (and even tables) have the same collation (character sets do not apply to INT columns):

ALTER TABLE `parents` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `parents` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `parents` MODIFY `id` int(10) unsigned NOT NULL AUTO_INCREMENT COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `kids` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `kids` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `kids` MODIFY `parent_id` int(10) unsigned NOT NULL COLLATE utf8mb4_unicode_520_ci;

ALTER TABLE `kids` ADD FOREIGN KEY (`parent_id`) REFERENCES `parents` (`id`);
-- Error : Cannot add foreign key constraint

Discussion

I've set up many foreign keys before, but apparently something is different in this case.

The example above does not reproduce the error, which means that the schema is correct. Therefore, something besides the schema must be causing the error.

There must be a hidden setting or condition in the parents table or data which makes it incompatible with the kids table. What could it be?

Notes

Similar questions have been asked before, but their answers did not solve the problem in this particular case.

• MySQL Cannot Add Foreign Key Constraint
• MySQL : ERROR 1215 (HY000): Cannot add foreign key constraint
• Cannot add foreign key constraint - MySQL ERROR 1215 (HY000)

Answer 1

You need to allow NULL for your Foreign KEY

CREATE TABLE `kids` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `parent_id` int(10) unsigned NULL, // <<== HERE 
  PRIMARY KEY (`id`),
  KEY `parent_id` (`parent_id`),
  CONSTRAINT parent_fk FOREIGN KEY (parent_id)
   REFERENCES parents(parent_id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4