Foreign key problems with MySQL

I recently ran into a problem with creating foreign keys in MySQL. I used the syntax alter table child add foreign key (parentID) references Parent(id) on update cascade on delete restrict. I also tried to declare the foreign key as part of the table creation statement, but received the same error message anyway: ERROR 1005 (HY000): Can't create table 'sometempname.frm' (errno: 150). When I looked up error 150, it said: MySQL error code 150: Foreign key constraint is incorrectly formed. This lead me to believe that my foreign key syntax had problems, however, as I coincidentally discovered when running show create table Parent, my Parent table was in fact of type MyISAM, whereas Child was of type InnoDB. I have no idea how Parent ended up being of type MyISAM, InnoDB is supposed to be the default engine. Anyway, by redefining the parent table to be of type InnoDB my foreign key declaration worked.

UPDATE: Other aspects to check with this error message: the columns used as foreign keys must be indexed, they must be of the same type (if i.e one is of type smallint(5) and the other of type smallint(6), it won't work), and, if they are integers, they should be unsigned.

posted by ballerina @ 2:55 pm