MySQL: "Foreign key constraint is incorrectly formed"

January 23, 2016

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.

Debugging

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 this:

------------------------
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 UNSIGNED flag.

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

error message.

Reason 4: Impossible ON DELETE or ON UPDATE clauses

You cannot create a foreign key with ON UPDATE SET NULL if the column does not allow NULL values. Sounds obvious, but I tripped over that after checking that in fact, the columns in both tables are identical.

Conclusion

MySQL’s error messages are a mess. Thankfully, SHOW ENGINE INNODB STATUS can help if one doesn’t spot the problem right away.