MySQL Bugs: #3491: alter table - wrong/incomplete error messages using foreign keys
I was receiving the following error when trying to add a foreign key to my table using an alter statement:
mysql> alter table detail add foreign key fk(mid1) references master(mid);
ERROR 1005: Can't create table '.\dbtest\#sql-8b8_4.frm' (errno: 150)

It turns out that you MUST create an ordinary index on the field that you are going to make a foreign key in your alter statement.

mysql > alter table category add index category(category_group_id);

mysql > alter table category add foreign key(category_group_id) references grp(group_id);
Query OK, 0 rows affected (0.28 sec)



MySQL Bugs: #3491: alter table - wrong/incomplete error messages using foreign keys: "This error message is also displayed when creating a foreign key on a field which does not already have an index. To correct the problem, create an index on the foreign key field and then create the foreign key constraint."
- posted by ThinkEddy @ 2:39 AM