MySQL Error Number 1005
Can’t create table ‘.\mydb\#sql-328_45.frm’ (errno: 150)
If you get this error while trying to create a foreign key, it can be pretty frustrating. The error about not being able to create a .frm file seems like it would be some kind of OS file permission error or something but this is not the case. This error has been reported as a bug on the MySQL developer list for ages, but it is actually just a misleading error message.
In every case this is due to something about the relationship that MySQL doesn’t like. Unfortunately it doesn’t specify what the exact issue is. Here is a running list of causes that people have reported for the dreaded errno 150. I’ve tried to put them in order based on the frequency that I hear about a particular cause.
You may want to start by running the MySQL command “SHOW INNODB STATUS” immediately after receiving the error. This command displays log info and error details. (Thanks Jonathan for the tip)
Known Causes:
- The two key fields type and/or size doesn’t match exactly. For example, if one is INT(10) the key field needs to be INT(10) as well and not INT(11) or TINYINT. You may want to confirm the field size using SHOW CREATE TABLE because Query Browser will sometimes visually show just INTEGER for both INT(10) and INT(11). You should also check that one is not SIGNED and the other is UNSIGNED. They both need to be exactly the same. (More about signed vs unsigned here).
- One of the key field that you are trying to reference does not have an index and/or is not a primary key. If one of the fields in the relationship is not a primary key, you must create an index for that field. (thanks to Venkatesh and Erichero and Terminally Incoherent for this tip)
- The foreign key name is a duplicate of an already existing key. Check that the name of your foreign key is unique within your database. Just add a few random characters to the end of your key name to test for this. (Thanks to Niels for this tip)
- One or both of your tables is a MyISAM table. In order to use foreign keys, the tables must both be InnoDB. (Actually, if both tables are MyISAM then you won’t get an error message – it just won’t create the key.) In Query Browser, you can specify the table type.
- You have specified a cascade ON DELETE SET NULL, but the relevant key field is set to NOT NULL. You can fix this by either changing your cascade or setting the field to allow NULL values. (Thanks to Sammy and J Jammin)
- Make sure that the Charset and Collate options are the same both at the table level as well as individual field level for the key columns. (Thanks to FRR for this tip)
- You have a default value (ie default=0) on your foreign key column (Thanks to Omar for the tip)
- One of the fields in the relationship is part of a combination (composite) key and does not have it’s own individual index. Even though the field has an index as part of the composite key, you must create a separate index for only that key field in order to use it in a constraint. (Thanks to Alex for this tip)
- You have a syntax error in your ALTER statement or you have mistyped one of the field names in the relationship (Thanks to Christian & Mateo for the tip)
- The name of your foreign key exceeds the max length of 64 chars. (Thanks to Nyleta for the tip)
The MySQL documentation includes a page explaining requirements for foreign keys. Though they don’t specifically indicate it, these are all potential causes of errno 150. If you still haven’t solved your problem you may want to check there for deeper technical explainations.If you run into this error and find that it’s caused by something else, please leave a comment and I’ll add it to the list.
#1 by Jason on March 29th, 2008
varma – i don’t think it is possible to use varchar as auto-increment. i’m not sure the reason to do that unless it is to support some legacy schema?
#2 by Matt and Cody on April 3rd, 2008
Thank you so much. This helped us find the solution to our problem. You are the best
#3 by Mamta on April 4th, 2008
Dear Jason,
Thanks a lot.. your blog helped me to solve my problem. thanks again.
#4 by Andreas on April 8th, 2008
Thank you verry much! Just didn’t know there has to be uniq foreign key names.
#5 by Pete on April 24th, 2008
Thank you for this article. Just printed it out. It just saved the remaining hair I am left with!
#6 by Vinod Pillai on May 7th, 2008
It is true that MySQL is having such a problem. This is the solution.
1) First of all make sure both parent and child table should be InnoDB.
Now when you create the foreign key in child table it creates the key_Name by default now you have to change that key_Name.
2) Now make sure that Foreign Column name should exactly match with the field name of the parent table.
That’s all I hope this will solve the problem.
#7 by Dele Agagu on May 9th, 2008
Whao!! couldnt help but post.i tried all sorts but nothing worked until i search for the error and whola!! your blog blew my mind!! thanks for this
#8 by Dele Agagu on May 9th, 2008
Discovered another reason for this error:
Make sure that if your referenced table has the primary key column as UNSIGNED, then the referencing table needs to have the referencing column as UNSIGNED too.
#9 by Tor Andre on May 15th, 2008
Very nice article.
I was using ActiveRecord/NHibernate to keep my db up to date. Since this was before release 1.0 I had ActiveRecord to automatically update my database (using ‘hibernate.hbm2ddl.auto’). This results in dropping every table and generate them again every time I rebuild my project.
I got stuck with this error, and there was one table I was unable to create. I tried everything, manually without foreign keys as well.
What solved it was to create the table WITH a foreign key but not naming it. Once doing that, I edited the table in Query Browser and found two (2) foreign keys with same reference. It seemed as the table was dropped as it should be, but the previous foreign key was stuck somewhere (couldn’t find it in the system tables either).
I dropped both the foreign keys and the table, then clean build the project (resulting in drop and create statements for all tables), and it WORKED!
Happy debugging!
#10 by Rahul Master on May 18th, 2008
Thanks, the 4th cause worked for me
#11 by Aswin Anand on May 31st, 2008
If the table for which the foreign key has to be applied contains data, this could happen. I took a backup of the existing data, truncated the table, ran the alter table query again. This time it went on smoothly.
#12 by Find an Appraiser on June 12th, 2008
I checked everything in this list and still got the error. Then I just dropped and recreated the database, ran new create script with only difference being my new table having PK varchar(255) instead of int(3), and it all worked fine. Strange, but there you have it.
#13 by Amin Abbasopour on June 15th, 2008
You’re great!
#14 by Spenner on July 2nd, 2008
Just a little extra info with MySQL
I had quite a few problems creating FK’s and found out that it’s good practice to use different FK names for each table.
Apparently MySQL does not like multiple instances of the same FK name in the same DB for different tables which I also think is quite logical.
Thanks to everyone else on this post for all the info provided as well
#15 by pavan on July 7th, 2008
thanks a lot
#16 by Marco Agurto on July 18th, 2008
I don’t write in english very well so a try to do my best work. I try to fix my alter with the nine steps but I cant fix my problem So I could fix my problem using the alter in the same order. In table one DE_PROC, CO_PROC in the table two CO_PROC, DE_PROC change the order of table two, I am happy again lucky for everybody
#17 by jegreat on September 10th, 2008
make sure that the foreign key u r trying to add is already present in the reference table!! if this value is not a primary key in the reference table; then u wil get this error
#18 by Nyleta on October 7th, 2008
After a lot of swearing and reading through your suggestions I tried something from left field and shortened the name of my foreign key, and bingo! So if you are still adding to your list up the top, can I suggest shortening of the foreign key name.
#19 by werutzb on October 7th, 2008
Hi!
I would like improve my SQL experience.
I red that many SQL resources and would like to
get more about SQL for my work as mysql database manager.
What would you recommend?
Thanks,
Werutz
#20 by Jason on October 8th, 2008
Thanks Nyleta, I found the max length is 64 chars, so I’ll add this to the list
#21 by anonymous on October 24th, 2008
It the definition of the local columns that is different to the ForeignKey-Column (such like “UNSIGNED”, (), etc.)
#22 by Paresh on November 11th, 2008
Thanks for the information. I had the problem of one is SIGNED and the other is UNSIGNED.
Your blog helped a lot!!!
Thanks again
#23 by Nayyereh on November 12th, 2008
Hi,
Thank you Niels, I had this problem and with your suggestion solved.
#24 by Deepak on November 13th, 2008
The solution that worked for me (having ensured all the above were verified was the length of the name of FK. I gave it a shorter name and it worked.
#25 by Brian on November 15th, 2008
“You should also check that one is not SIGNED and the other is UNSIGNED.” Ahh..HAH!
Very good! Thank you sir.
-Brian
#26 by Ignace on November 16th, 2008
Some good advice: make sure all your tables are dropped in your database before running your sql installation script! I personally use:
DROP TABLE IF EXISTS ..
before i place my table definition, however when i ran the script when their were already tables in the db i got the #1005 error, when i used the same script on an empty db it worked just fine.
#27 by the.janitor on November 18th, 2008
thanks a lot, very helpful information
#28 by Shan on December 3rd, 2008
This was incredibly helpful. I usually avoid these kind of sites, but this helped what was proving to be an extremely frustrating issue. Brilliant.
#29 by chicco on December 15th, 2008
It’s a dirty way to solve it but if you enclose the alter table statement between
SET FOREIGN_KEY_CHECKS=0;
and
SET FOREIGN_KEY_CHECKS=1;
it works
#30 by HKASLCA on January 4th, 2009
Thanks.
I’ve solved the problem.
#31 by Lainey on February 3rd, 2009
Can I say I love you? I have spent hours trying to figure out this issue and it turned out that both tables were MyISAM tables! Gotta love the internet and people like you who post solutions to problems!
#32 by CJ on February 12th, 2009
ONE MORE:
Make sure your tables are written in the definition file BEFORE you try and make a foreign key on that table.
Stupid rubbish mysql parser.
#33 by kirov on March 12th, 2009
yeah, one more time THANK YOU, I was ready unninstal MySQL because of that, and the problem was stupid index on column. Why they couldn’t do a error message about this, I don’t understand.
#34 by ynabid on April 10th, 2009
when I change my tables engine to innoDB and I want to create a foreign key for a attribut that has data type=varchar, that not work but when I set flag to binary it work.
#35 by Plat on April 15th, 2009
Thanks for the troubleshootin’ list. This saved me an incredible amount of time. I caught an UNSIGNED/SIGNED mismatch prior to this page, but didn’t think to check InnoDB vs MyISAM. My new editor must be defaulting to InnoDB for some reason.
#36 by Gerhard Kratz on April 21st, 2009
#37 by Gerhard Kratz on April 22nd, 2009
If one wants to
- CREATE TABLE A with FOREIGN KEY ( ab ) REFERENCES B
- CREATE TABLE B with FOREIGN kEY ( ba ) REFERENCES A
then one of the foreign keys has to be declared without the table it references already existing.
The problem is remedied proceeding as follows:
- CREATE TABLE A
- CREATE TABLE B with FOREIGN kEY ( ba ) REFERENCES A
- ALTER TABLE A ADD FOREIGN KEY ( ab ) REFERENCES B
I always give the name of the primary key of the table referenced to the foreign key constraint.
#38 by Lauro Valente on April 22nd, 2009
Make sure the tables are clean, with no data in them….
#39 by Jason on April 24th, 2009
that’s a good point Lauro. You can actually have data in the tables, but you have to make sure that every row works with the new key. a lot of times when you try to add a key you’ll have to clean up the data first.
#40 by Heti on April 29th, 2009
thaaaaanks a ton … got the prblem fixed in lesser time than expected.
))))) i am so happppyyyy
#41 by Radek on May 22nd, 2009
Thanks for that checklist.
Here’s what was wrong in my case: mySQL 5.0 under windows lowercased all column names when I created the tables. Under UNIX the tables were created with orignal cases. Then I tried copy pasting the ALTER TABLE statement from Windows DEV DB with the lowercase version. That is where erno 150 came from.
#42 by Dave on June 24th, 2009
Thanks heaps!!!!!!!!!!!!!!!
My problem was point 3 – duplicate fk name
#43 by thao on June 27th, 2009
thankyou!
Can i translate this Posts to vietnamese and post it in our public site?
#44 by Jason on June 27th, 2009
hi thao, that would be ok – it would be appreciated if you link back to my site as well.
#45 by zachariah on June 29th, 2009
Hi………. Thanks a lot. Great work. Helped me a lot.