MySQL: "Foreign key constraint is incorrectly formed"
Ah, MySQL, source of many weird and incomprehensible error messages. Like this one:
Error 150: Foreign key constraint is incorrectly formed
There are a number of possible reasons for this error. A lot of them are described on Stackoverflow, but there is one missing and I wanted to collect the reasons in one place, so here they are.
If you encounter this error, consider running
SHOW ENGINE INNODB STATUS
status immediately after the failed FK query. The InnoDB status usually contains
all the information you need to fix the issue. An example output could look like
------------------------ LATEST FOREIGN KEY ERROR ------------------------ Error in foreign key constraint creation for table `mytable`.`#sql-6a70_2`. A foreign key constraint of name `mytable`.`my-fk` already exists. (Note that internally InnoDB adds 'databasename' in front of the user-defined constraint name.) Note that InnoDB's FOREIGN KEY system tables store constraint names as case-insensitive, with the MySQL standard latin1_swedish_ci collation. If you create tables or databases whose names differ only in the character case, then collisions in constraint names can occur. Workaround: name your constraints explicitly with unique names.
Reason 1: Column types don’t match
This is the most obvious reason. If you try to create a foreign key from an
INT(10) column to a
SMALLINT(5) column, you will get the error above.
Simply make sure that the column types are identical, including a possible
Reason 2: MyISAM instead of InnoDB
MyISAM does not support foreign keys. It’s as easy as that. Use InnoDB or any other storage engine that can handle FKs. But be aware that InnoDB is no silver bullet and you might have tables which work better in simpler engines like MyISAM (especially if you fear your InnoDB log growing indefinitely for something like log tables).
Reason 3: Foreign Key name not unique
Believe it or not, but the names of foreign keys must be unique within the same database. Depending on your MySQL version, you might also get the
Can’t create table ‘mydatabase.#sql-798_ffe95d’ (errno: 121) Duplicate key on write or update
Reason 4: Impossible
ON DELETE or
ON UPDATE clauses
You cannot create a foreign key with
ON UPDATE SET NULL if the column does
NULL values. Sounds obvious, but I tripped over that after
checking that in fact, the columns in both tables are identical.
MySQL’s error messages are a mess. Thankfully,
SHOW ENGINE INNODB STATUS can
help if one doesn’t spot the problem right away.