Unanswered: Error On creating Foreign Key: ERROR 1005 (HY000)
I am using MySQL 5.5 on Ubuntu 13.04, using the InnoDB engine.
I have much SQL / DBMS background and what I am doing is something really simple.
Its about creating a foreign key. And I am running the foreign key script after my tables are created and I have confirmed the column exists on the foreign table.
I know my foreign key script is fine, the column in the other table exists (is Primary Key), I have tried with and without adding 'on delete cascade' and also 'on update cascade'. The column on the child table is set as NOT NULL, but again this should not matter.
My statement is:
ALTER TABLE member ADD CONSTRAINT fk_member_member_type_id FOREIGN KEY (member_type_id) REFERENCES member_type (id);
And also I have tried:
ALTER TABLE member ADD CONSTRAINT fk_member_member_type_id FOREIGN KEY (member_type_id) REFERENCES member_type (id) ON UPDATE CASCADE ON DELETE CASCADE;
I keep getting this error:
ERROR 1005 (HY000): Can't create table 'import_dbms_db.#sql-1eb1_99' (errno: 150)
So I am confirming the tables are created, I checked that the id column is there in 'member_type' table before running the foreign key script.
The constraint name is within the 31 character limit
In fact the script works fine Postgres and SQLite as I am running them in Postgres and SQLlite as well to try out some things simultaneously.
However it is NOT working in MySQL. And I am just working with 2 tables to try out the scripting and datatypes in these environments.
I keep finding messages on forums that means that the column does not exist in other table or such. But again I confirm that is not the case here.