Adding a foreign key may result in the following error:
mysql> ALTER TABLE `table1` ADD CONSTRAINT FK_table2_id FOREIGN KEY (`table2_id`) REFERENCES table2(`ID`); ERROR 1215 (HY000): Cannot add foreign key constraint
This error can occur because in couple of cases and it is not very informative, but basically, it says there is some kind of compatibility issue between the table1.table_id and table2.ID. And the three most common problems, besides the tables and columns exists, are:
- One of the two table are not Innodb.
SHOW CREATE TABLE `table1`;
- There is a value (or values) in table1.table_id, which does not exists in table2.ID.
- The type and the attributes of the columns are different. The attributes such as UNSINGED may cause a problem, too!
ALTER TABLE `table1` CHANGE `table2_id` `table2_id` INT(11) UNSIGNED NOT NULL;
In this case, the column types are the same, but the attributes are different and the MySQL server throws the error! Change the signed int to unsigned with the above command. And after the table1.table2_id and table2.ID are of the same type and they have the same attributes, the alter command will be executed successfully.
SELECT `table1`.`table2_id`, `table2`.`ID` FROM `table1` LEFT JOIN `table2` ON `table1`.`table2_id`=`table2`.`ID`;
Execute a left join to check whether there are NULL values in the table1.table2_id. If NULL values exist a record with the same missing IDs should be inserted in table2.ID. this kind of check may be stopped temporarily with:
Change it back to 1 after the ALTER clause.
Here are the initial structure of above tables:
CREATE TABLE `table1` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `table2_id` int(11) NOT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=220 DEFAULT CHARSET=utf8 CREATE TABLE `table2` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `col1` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=642 DEFAULT CHARSET=utf8
And after a successful ALTER statement, the table1 will looks like:
ALTER TABLE `table1` ADD CONSTRAINT `FK_table2_id` FOREIGN KEY (`table2_id`) REFERENCES `table1`(`ID`); Query OK, 216 rows affected (0.92 sec) Records: 216 Duplicates: 0 Warnings: 0 CREATE TABLE `table1` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `table2_id` int(11) NOT NULL, PRIMARY KEY (`ID`), KEY `FK_table2_id` (`table1_id`), CONSTRAINT `FK_table2_id` FOREIGN KEY (`table2_id`) REFERENCES `table2` (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=220 DEFAULT CHARSET=utf8