MySQL Error code: 1005 Can’t create table (errno 150).
This error message is a pretty frustrating because it is not properly well define the message.
I have got this message while I am creating one table with foreign key constraint. I have checked syntax and other thing which was 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 have 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.
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.