MySQL ERROR 1005 (HY000): Can't create table

I got the following MySQL error ("1005 error") earlier today when trying to create a table with a foreign key relationship:

ERROR 1005 (HY000): 
Can't create table './mover/mover_files.frm' 
(errno: 150)

In my case this MySQL ERROR 1005 (HY000) error came from having slightly different declarations for the index of one table, which was referenced as a foreign key in my second table. When I created the index on the first table I declared it as id int unsigned auto_increment not null, and when I created the foreign key I declared it as file_source_id int not null, accidentally omitting the unsigned keyword.

I solved the problem by simply adding the unsigned keyword to my second field definition, as that's how I really want it. MySQL was right, I did have an error in my foreign key relationship, but the error message "ERROR 1005 (HY000): Can't create table" just wasn't very helpful.

A few more details

Here's a little more information on the two declarations that led to this MySQL error message ... in the first table, named file_sources, I declared my id field like this:

id int unsigned auto_increment not null

And in the table where I created the foreign key I had these two declarations:

file_source_id int not null,
constraint foreign key fk_mover_files_to_file_sources (file_source_id) references file_sources(id)

As mentioned, you'll see that I declared the auto_increment field using the unsigned keyword, but I didn't use that keyword when defining the file_source_id field. It's a subtle error, but an error nonetheless.



In my copy and paste hurry to get things done, I managed to create two FKs that were identical to another table's FKs (similar table names.) I had made the same paste error on two more FKs on the same table, but since they were unique (even if named after the wrong table) those didn't cause the error.

It was puzzling until I coincidentally compared the table that I had copied from to the one I had pasted to, and the identical names jumped out at me like jack-in-the-boxes. Have you noticed that bashing your head against the monitor isn't as satisfying with an LCD?

Add new comment

The content of this field is kept private and will not be shown publicly.

Anonymous format

  • Allowed HTML tags: <em> <strong> <cite> <code> <ul type> <ol start type> <li> <pre>
  • Lines and paragraphs break automatically.
By submitting this form, you accept the Mollom privacy policy.