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
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.
a great article!
I hit the very same problem today, and your article saved my butt! Thank you!
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?