I just finished a database modification, a new foreign key creation shouldn’t be take more than 5 mins, but I spent 2 hours because MySQL still have some useless error messages.
There is a way to create a new foreign key:
-- Create two tables foo and bar
CREATE TABLE foo (
id INTEGER NOT NULL PRIMARY KEY,
bar_id INT NOT NULL -- foreign key
);
CREATE TABLE bar (
id INTEGER NOT NULL PRIMARY KEY
);
-- Try to create a foreign key on `foo`
ALTER TABLE foo
ADD FOREIGN KEY(bar_id) REFERENCES bar(SOME_FIELD) ;
The last sentence returns a generic error message:
Error Code: 1005. Can't create table 'temp.#sql-4bd7_11'
(errno: 150)
Everything would have been easier if I had noticed that wrong
field name bar(SOME_FIELD), sometimes happens,
but if MySQL would have shown a different message like
"field bar.SOME_FIELD don't exists" I would …