mysql error 150 Foreign keys

Question

whenever I do insert into table xxx:

alter table xxx 
add index FK68C3166C7B556202 (my_yyy_id), 
add constraint FK68C3166C7B556202 foreign key (my_yyy_id) references yyy (yyy_id)

...I get:

19:27:44,355 ERROR SchemaUpdate:212 - Unsuccessful: alter table xxx add index FK68C3166C7B556202 (my_yyy_id), add constraint FK68C3166C7B556202 foreign key (my_yyy_id) references yyy (yyy_id)
19:27:44,356 ERROR SchemaUpdate:213 - Can't create table 'mydb.#sql-2f1b_657' (errno: 150)

why? how do I resolve this issue?

Answer 1

It's hard to guess what is wrong without details, but there are some common mistakes you might make : 1. yyy.yyy_id is not a unique/primary key
2. yyy.yyy_id and xxx.my_yyy_id have different types (e.g. yyy_id unsigned int, xxx.my_yyy_id int)

Answer 2

Documentation:

If MySQL reports an error number 1005 from a CREATE TABLE statement, and the error message refers to error 150, table creation failed because a foreign key constraint was not correctly formed. Similarly, if an ALTER TABLE fails and it refers to error 150, that means a foreign key definition would be incorrectly formed for the altered table.

You need to provide more info about the foreign key constraint you're trying to make.

Answer 3

What version of MySQL are you using? Could it be older than 4.1 or so?

Can try create an index without adding the foreign key? If that works, try adding the foreign key.