If you are altering a table in your MySQL to add foreign key constraint, and receiving the following error
ERROR 1215 (HY000): Cannot add foreign key constraint
This error is very general and does not show the specific cause of why MySQL cannot add a foreign key constraint to the table. To fix this issue you need to get to know the exact cause, and to find that run the below query in your mysql client
SHOW ENGINE INNODB STATUS;
This query will return three fields Type, Name and Status. Expand the status column of the result and look for LATEST FOREIGN KEY ERROR.
In here you will find the exact cause of why the last alter query failed and then you can proceed to fix it.
The usual cause are generally a mismatch in the type of the column of the primary table and the foreign table. It can also be a mismatch in the Engine type of two tables i.e. MyISAM or InnoDB.
It can also be because of different collation on the tables, one UTF-8 and the other latin1
Following are the things you should look for
- Engine both tables should have same engine specfied e.g. InnoDB
- Datatype both columns should have same datatype. int(11) on one table and smallint(5) on another will cause problem.
- Collation Columns charset should be the same. e.g. utf8
- Watchout: Even if your tables have same Collation, columns still could have different one.
- Unique – Foreign key should refer to field that is unique (usually primary key) in the reference table.
- Ungisned – One table could have key identified as unsigned and other is not.