MySQL Error codes Error code 1215: Cannot add foreign key constraint
Example
This error occurs when tables are not adequately structured to handle the speedy lookup verification of Foreign Key (FK) requirements that the developer is mandating.
CREATE TABLE `gtType` (
`type` char(2) NOT NULL,
`description` varchar(1000) NOT NULL,
PRIMARY KEY (`type`)
) ENGINE=InnoDB;
CREATE TABLE `getTogethers` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`type` char(2) NOT NULL,
`eventDT` datetime NOT NULL,
`location` varchar(1000) NOT NULL,
PRIMARY KEY (`id`),
KEY `fk_gt2type` (`type`), -- see Note1 below
CONSTRAINT `gettogethers_ibfk_1` FOREIGN KEY (`type`) REFERENCES `gtType` (`type`)
) ENGINE=InnoDB;
Note1: a KEY like this will be created automatically if needed due to the FK definition in the line
that follows it. The developer can skip it, and the KEY (a.k.a. index) will be added if necessary.
An example of it being skipped by the developer is shown below in someOther.
So far so good, until the below call.
CREATE TABLE `someOther` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`someDT` datetime NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `someOther_dt` FOREIGN KEY (`someDT`) REFERENCES `getTogethers` (`eventDT`)
) ENGINE=InnoDB;
Error Code: 1215. Cannot add foreign key constraint
In this case it fails due to the lack of an index in the referenced table getTogethers to
handle the speedy lookup of an eventDT. To be solved in next statement.
CREATE INDEX `gt_eventdt` ON getTogethers (`eventDT`);
Table getTogethers has been modified, and now the creation of someOther will succeed.
From the MySQL Manual Page Using FOREIGN KEY Constraints:
MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist.
Corresponding columns in the foreign key and the referenced key must have similar data types. The size and sign of integer types must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same.
InnoDB permits a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.
Note that last point above about first (left-most) columns and the lack of a Primary Key requirement (though highly advised).
Upon successful creation of a referencing (child) table, any keys that were automatically created for you are visible with a command such as the following:
SHOW CREATE TABLE someOther;
Other common cases of experiencing this error include, as mentioned above from the docs, but should be highlighted:
-
Seemingly trivial differences in
INTwhich is signed, pointing towardINT UNSIGNED. -
Developers having trouble understanding multi-column (composite) KEYS and first (left-most) ordering requirements.
