MySQL must improve error messages

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

3 Responses to MySQL must improve error messages

Leave a Reply

Your email address will not be published.

Go back to top