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 not be awake until 2 am.
I’m using MySQL 5.5.21 community edition.
if I had noticed that wrong field name