Search code examples
mysqlforeign-keysinnodb

Mysql, innodb - Can't create foreign key

Using the default engine (MyIsam), I'm able to create a foreign key with this syntax:

alter table `codes` add constraint foreign key(`associated_code_id`) references ask_codes(code_id)  on update cascade on delete cascade;

However, when i create the table using INNODB I'm no longer able to create the foreign key (mysql gives me ERROR 1005 (HY000): Can't create table 'my_table.#sql-3311_16115' (errno: 150) ).

Is this a syntax issue? Thanks for the help,

Solution

  • You problem is that both columns don't have the same type - they don't match exactly:

    If you re-create a table that was dropped, it must have a definition that conforms to the foreign key constraints referencing it. It must have the right column names and types, and it must have indexes on the referenced keys, as stated earlier. If these are not satisfied, MySQL returns error number 1005 and refers to error 150 in the error message.