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.

Can't reproduce the problem on sqlfiddle: sqlfiddle.com/#!9/7eb8e/1 — Paul Spiegel
– Paul Spiegel, Commented Oct 24, 2017 at 18:56
Thanks. I've updated the question. The example shows what the schema looks like, so that means the problem is unrelated to the schema. — GreenRaccoon23
– GreenRaccoon23, Commented Oct 24, 2017 at 19:02
Logically the syntax is correct if others can get this to work. that leaves the issue specific to your environment. Since we don't have the exact two tables and setup; we have to trust all of the needed requirements on size type have been met. That leaves us with odd ball questions like: are you sure the alter is being attempted on the schema that has the tables and in the same environments and those environments do not have data. — xQbert
– xQbert, Commented Oct 24, 2017 at 19:26
If your example cant reproduce the error then isnt a good example. My suggestion start with this example and start adding thing until you reach your current structure to find out what cause the error. — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Oct 24, 2017 at 19:31
"removed the unrelated columns (non-keys), and renamed the columns/tables" - Please post the exact create statement. If you don't know where the problem is, how can you know it's not there? — Paul Spiegel
– Paul Spiegel, Commented Oct 24, 2017 at 19:31

Juan Carlos Oropeza · Accepted Answer · 2017-10-24 19:25:54Z

0

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

answered Oct 24, 2017 at 19:25

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

xQbert Over a year ago

Only if there was data. there is no data.

Juan Carlos Oropeza Over a year ago

@xQbert Well the problem cant be reproduced anyway, so at least try something ;)

xQbert Over a year ago

This is true :P and these types of issue you second guess everything you know even when you know its right.

Collectives™ on Stack Overflow

Error : Cannot add foreign key constraint

Problem

Example

Failed Solutions

Discussion

Notes

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Problem

Example

Failed Solutions

Discussion

Notes

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related