1

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.

GreenRaccoon23
  • 3,603
  • 7
  • 32
  • 46
  • 2
    Can't reproduce the problem on sqlfiddle: http://sqlfiddle.com/#!9/7eb8e/1 – Paul Spiegel Oct 24 '17 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 Oct 24 '17 at 19:02
  • Ckeck: `select count(*) from kids k left join parents p on p.id = k.parent_id where p.id is null` – Paul Spiegel Oct 24 '17 at 19:05
  • @PaulSpiegel Shouldnt the `LEFT JOIN` be inverted? Unless you are talking kids without parents? – Juan Carlos Oropeza Oct 24 '17 at 19:06
  • @Paul Spiegel `kids` has no rows at all; it's a new table. – GreenRaccoon23 Oct 24 '17 at 19:07
  • @JuanCarlosOropeza that is exactly what I want - Find kids without parents. If they exist, the FK can't be created. – Paul Spiegel Oct 24 '17 at 19:08
  • Troubleshooting an unreproduce-able error is challenging: http://rextester.com/MLE20603 same as paul it works. Maybe the engine is trying to create it with an object name that already exists? Try naming the constraint? `ALTER TABLE kids ADD CONSTRAINT KidsFKParents FOREIGN KEY (parent_id) REFERENCES parents(id);`? – xQbert Oct 24 '17 at 19:08
  • @xQbert Naming the constraint produces the same error. – GreenRaccoon23 Oct 24 '17 at 19:11
  • Since your "Sample" isn't an exact replica of your tables... Are you SURE the types are both signed/unsigned same data type/size? and is setup as primary key? `SHOW ENGINE INNODB STATUS;` showing nothing is disconcerting. Are you sure the alter attempt was made in the same database environment & Schema (now grasping at straws since we can't reproduce error) – xQbert Oct 24 '17 at 19:22
  • @xQbert Yes, I am 100% certain that the example schema matches the real one. I copied and pasted the schema from the real one, removed the unrelated columns (non-keys), and renamed the columns/tables. This is why I'm so stumped. – GreenRaccoon23 Oct 24 '17 at 19:26
  • 1
    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 Oct 24 '17 at 19:26
  • You said these two tables are just removal of columns not needed; though we can't recreate the error. Do you by chance already have a FK on parent_ID to a different table? or a second column also setup as PK? Could we see the entire table structure; or can you try and recreate in a fiddle to see if you can recreate the error using the entire structure? – xQbert Oct 24 '17 at 19:30
  • 1
    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 Oct 24 '17 at 19:31
  • 1
    "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 Oct 24 '17 at 19:31
  • ha Juan and Paul all thinking alike. – xQbert Oct 24 '17 at 19:32
  • I suspect the name of the foreign key constraint is a duplicate. Try giving it an explicit name. – Barmar Oct 24 '17 at 19:33
  • Time to bring the big guns, now we have @Barmar here. – Juan Carlos Oropeza Oct 24 '17 at 19:34
  • @xQbert I cannot publish the real schema, but I can test it on sqlfiddle. It does not produce an error on sqlfiddle. It was a direct copy and paste there. – GreenRaccoon23 Oct 24 '17 at 19:37
  • @barmar we did that 29 minutes ago ;P I had a similar thought. GreenRaccoon23 That to me implies that it's a server setting unique to your server. Do you have a different instance of mySQL that you could recreate tables and try? if it works there then we know it's server specific at least; but still don't know what. – xQbert Oct 24 '17 at 19:40
  • https://dzone.com/articles/dealing-with-mysql-error-code-1215-cannot-add-foreign-key-constraint Is the parent table partitioned? or is ID virtual? Guessing not given the create but hey.... straws again. – xQbert Oct 24 '17 at 19:46

1 Answers1

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
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118