Solution for ERROR 1005 (HY000) Can't create table ' in MySQL

If you are using referential integrity on MySQL, you can easily run into this error with the InnoDB engine. It frequently seems to occur with an ALTER TABLE statement. It can mean many things but typically it means the data types don? match between the foreign key and primary key column. Likewise, it can mean one of the column data types disagrees in a multiple-column foreign to multiple-column primary key constraint. The error by itself isn't very handy. This is a sample:

ERROR 1005 (HY000): Can't create table 'my_data' (errno: 150)

You can try SHOW WARNINGS but you will discover more about the error by running the following command as the root superuser:

mysql> SHOW engine innodb STATUS;

It returns a dump of the InnoDB's activity. You can see it by unfolding the complete log, if you are interested in the details. The significant part of the log to solve this type of problem is:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
1001111 13:11:00 Error IN FOREIGN KEY CONSTRAINT OF TABLE my_data :
FOREIGN KEY(id2)
REFERENCES pk(test1):
Cannot find an INDEX IN the referenced TABLE WHERE the
referenced COLUMNS appear AS the FIRST COLUMNS, OR COLUMN types
IN the TABLE AND the referenced TABLE do NOT MATCH FOR CONSTRAINT.

While this is more information, it isn't necessarily enough to solve the problem when you are new to MySQL. The first place you should look is whether the data types agree between the foreign key and primary key columns. The most common variation that I've run into is where the primary key column uses a int unsigned data type and the foreign key column uses an int data type. It's quite nice that the InnoDB Engine stops this cold. Naturally, you fix it by changing the foreign key data type to match the int unsigned data type.