Search code examples
mysqlsqlforeign-keys

Cannot add foreign key constraint, (no different datatypes)

I got the error message:

Cannot add foreign key constraint, (no different datatypes)

So the keys must have the same datatype. They do (check). Also both must be a key. Both are (check).

What is the of cause this error?

CREATE TABLE `parent` (
  `an_id` char(24) NOT NULL,
  `stuff` int(10) unsigned NOT NULL DEFAULT '0',
  `otherstuff` datetime DEFAULT NULL,
  PRIMARY KEY (`an_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `child` (
 `date` datetime NOT NULL,
 `an_id` char(24) NOT NULL,
 PRIMARY KEY (`date`,`an_id`),
 CONSTRAINT `child` FOREIGN KEY (`an_id`) REFERENCES `parent` (`an_id `)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Solution

  • It's a typo on your part. You have an extra space after an_id:

    REFERENCES `parent` (`an_id `)