A solution to: ERROR 1005: Can't create table (errno: 150)

[Andrew Pitonyak]

I tried a command such as:

ALTER TABLE TABLE1 ADD ( FOREIGN KEY (FIELD1) REFERENCES TABLE2(FIELD2) ) ;

The generated error is of the form:

ERROR 1005 (HY000): Can't create table '.\schema_name\#sql-97c_c.frm' (errno: 150)

The error lead me to believe that it could not create the modified table specified by the alter command. First, I inspected FIELD1 in TABLE1.

SHOW CREATE TABLE TABLE1;

This shows the details required to create TABLE1, including very specific details related to the individual fields. Pay attention to the exact definition for FIELD1 and the character set for the field or table. Now, repeat for FIELD2 in TABLE2.

If the fields are not declared exactly alike, then use the alter command to make them the same. For example, I had something like:

FIELD2 varchar(20) DEFAULT NULL
FIELD1 varchar(20) NOT NULL DEFAULT ''

The fields are very similar, but they are not exactly the same. Now, I do not know for certain that they really must be exactly the same, but it is something that I did.

ALTER TABLE TABLE2 CHANGE FIELD2 FIELD2 VARCHAR(20) NOT NULL DEFAULT '';

If required, do the same for both tables and fields. I still received the error. Next, I created an index on FIELD2.

ALTER TABLE TABLE2 ADD INDEX (FIELD2);

Finally, creating the foreign key works.