Monday, June 20, 2005

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.


At 9:11 am, Blogger Doctrine Dark said...

thanks for the post. It was incredible and useful.

At 6:09 pm, Anonymous Anonymous said...

Perfect thanks

At 4:34 pm, Blogger aprudencio said...

This error is also caused by having different charset/collation between referenced tables. E.g. having one table with latin1 - default collation and it's referenced table with ascii - default collation. So check that too if you are still getting that error

At 12:15 pm, Anonymous Anonymous said...

It was really useful


Post a Comment

<< Home

En blogg kan være et godt verktøy for en som i litt for stor grad glemmer de små og store tingene som utgjør livet. Dette er min reserve- hukommelse.