MySQL: Can't create table (errno: 150)

Question

From the MySQL - FOREIGN KEY Constraints Documentation:

If you re-create a table that was dropped, it must have a definition that conforms to the foreign key constraints referencing it. It must have the correct column names and types, and it must have indexes on the referenced keys, as stated earlier. If these are not satisfied, MySQL returns Error 1005 and refers to Error 150 in the error message, which means that a foreign key constraint was not correctly formed. Similarly, if an ALTER TABLE fails due to Error 150, this means that a foreign key definition would be incorrectly formed for the altered table.

Error 150 means you have a problem with your foreign key. Possibly the key on the foreign table isn't the exact same type?

You can get the actual error message by running SHOW ENGINE INNODB STATUS; and then looking for LATEST FOREIGN KEY ERROR in the output.

Source: answer from another user in a similar question

Data types must match exactly. If you are dealing with varchar types, the tables must use the same collation.

I think all these answers while correct are misleading to the question.

The actual answer is this before you start a restore, if you're restoring a dump file with foreign keys:

SET FOREIGN_KEY_CHECKS=0;

because naturally the restore will be creating some constraints before the foreign table even exists.

In some cases, you may encounter this error message if there are different engines between the relating tables. For example, a table may be using InnoDB while the other uses MyISAM. Both need to be same

Error no. 150 means a foreign key constraint failure. You are probably creating this table before the table the foreign key depends on (table keywords). Create that table first and it should work fine.

If it doesn't, remove the foreign key statement and add it after the table is created - you will get a more meaningful error message about the specific constraint failure.