How to solve MySql errorno : 150 "Can't Create Table"

Recently I faced one issues while creating tables in database. I was adding primary and foreign key in tables and I get errorno : 150 "Can't Create Table" I took almost couple of hours to resolve that error so in this blog I am going to explain what could be possible cause for this error. If you get this error check following possible cause.

1) The two tables must have same storage ENGINE

As we know we can configure storage engines like MyISAM, InnoDB. When you add foreign key to the table both parent and reference table should have same storage engine.

2) The two tables must have the same charset.

MySql supports more than 30 character sets. Both parent and reference table should have the same charset.

3) Primary key and Foreign key column must have same datatype.

When you add a foreign key, the primary key in parent table must have the same datatype as foreign key. For example if you have following definition of primary key

id INT UNSIGNED NOT NULL AUTO_INCREMENT

then you must define foreign key as

pk_id INT UNSIGNED NOT NULL

Else it will give you an error and will not work.

4) Primary key and Foreign key column must have same collation type

MySql supports more than 70 collation type. To define primary key and foreign key both columns should have same collation type

5) If there is already data is must match 

If there are already data in both the tables it should match otherwise MySql will not allow to add foreign key. If there is data inconsistency, first delete the inconsistent data and then add primary key.

6) If the reference column is part of composite primary key, it may not work.

If the referenced column is part of composite primary key it may not work so if it's not required to have composite primary key, just define single column as primary key.

Hope this helps you.