This article is half-done without your Comment! *** Please share your thoughts via Comment ***
MySQL Error code: 1005 Can’t create table (errno 150).
This error message is a pretty frustrating because it is not properly well-defined error message.
I have got this message while creating a table with foreign key constraint. I checked syntax and other things which were also correct.
This error looks like an OS file permission issue because it is not able to create a .frm file.
After some time, I found that my reference column data type is BIGINT and foreign key data type is INT.
Actually, this was a problem of data type mismatching and misleading of the error message.
There are some other reasons:
The two key fields data type and/or size doesn’t match exactly.
For example, one is INT and another is BIGINT.
One of the key fields that you are trying to reference does not have an index and/or is not a primary key. If one of the fields in the relationship is not a primary key, you must create an index for that field.
The foreign key name is a duplicate of an already existing key.
One or both of your tables is a MyISAM table. In order to use foreign keys, the tables must both be InnoDB.
You have specified a cascade ON DELETE SET NULL, but the relevant key field is set to NOT NULL.
Make sure that the Charset and Collate options are the same both at the table level as well as an individual field level for the key columns.
The name of your foreign key exceeds the max length of 64 chars.
