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
Related Posts :
I have used tools like MySQL Migration Toolkit (*) and Bullzip's Access To MySQL utility, both ...
I discovered a feature (or bug) of MySQL timestamp fields. Maybe is documented in some place wha ...
Do you get write laziness in the command line everything what you need to connect to a MySQL ser ...
Those who have used PostgresSQL usually gets lazy when write the SQL code to auto-numeric fields ...
I was tired to get manually disk space used for all MySQL databases, I just created a stored pro ...
Did you know that you can look at the output of SHOW ENGINE INNODB STATUS and see the details of the last foreign key error? Or did you check the manual at http://dev.mysql.com/doc/refman/5.5/en/innodb-error-codes.html to see what the error code was? That page says:
“1005 (ER_CANT_CREATE_TABLE)
Cannot create table. If the error message refers to error 150, table creation failed because a foreign key constraint was not correctly formed. If the error message refers to error –1, table creation probably failed because the table includes a column name that matched the name of an internal InnoDB table.”
So if you had tried to search for your error in a search engine, you would have found that page, and you would have known that the foreign key constraint was not correctly formed.
You are also very lucky – if you run your example in MySQL 5.1 or earlier, the example works just fine with no errors, and the only time you get an error is when you try to insert into foo, and the foreign key actually fails.
SHOW ENGINE INNODB STATUS is your friend. It has a section with the last foreign key error, with sensible details as to what is wrong.
Thank you!, but I still thinking is not the right way… maybe add some comment on first error message like “see InnoDB status for more details.”