For quite some time I had faced this mysterious problem with MySQL where it was refusing to create a new table in our application’s database with a foreign key relationship to one of its existing tables. The error message MySQL was displaying was not very helpful:
[cc]ERROR 1005 (HY000): Can’t create table ‘DB_NAME.TBL_NAME’ (errno: 150)[/cc]
[cci lang="sql"]SHOW INNODB STATUS[/cci] was not helpful either:
[cc lang="sql"]
LATEST FOREIGN KEY ERROR
————————
100716 1:27:25 Error in foreign key constraint of table DB_NAME/TBL_NAME:
foreign key (COL_NAME) references ANOTHER_TBL_NAME(ID_COL_NAME)) default charset=’UTF8′ engine=InnoDB:
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.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html
for correct foreign key definition.
[/cc]
Oddly enough MySQL had no problem creating two new tables with a foreign key relationship between them. Well, long story short, it turned out that the old tables were using latin1 charset whereas the default charset for my MySQL installation was UTF8 and also the columns that had a foreign key relationship between them were varchar columns^*.

The solution was quite easy: explicitly setting the charset to latin1 in create table statements (or alternatively changing the default charset of the database to latin1).

^*Use of VARCHARS should be avoided in primary key columns.