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.
First Steps:
If you have admin permission on the server, you may want to start by running the MySQL command “SHOW INNODB STATUS” (or MySQL 5.5 “SHOW ENGINE INNODB STATUS”) immediately after receiving the error. This command displays log info and error details. (Thanks Jonathan for the tip)
If your script runs fine on one server, but gives an error when you try to run it on a different server, then there is a good chance that #6 is the problem. Different versions of MySQL have different default charset setting and you may have unknowingly assigned different charsets on the different servers.
Known Causes:
Below is a running list of known causes that people have reported for the dreaded errno 150:
- The two key fields type and/or size is not an exact match. 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.
No no no no no and No!!! I’ve been looking closer and closer and closer again. I have 2 tables, the first one with 2 fields and the second one with only one. The PK I’m trying to build is for 2 Unsigned INT(10) fields. It just doesn’t work. I’m fed up of this MySQL-Administrator.
I’m not happy
((((((
Hey Stephane, I know this error sucks. MySQL doesn’t tell you what the problem is. But, something about the fields are not matching or else there may be a conflicting key or something. I don’t know I’ve never tried a table with only 1 column before – maybe MySQL doesn’t like that? If you find the answer, post here and maybe it will help someone at some point.
Hi, I had this error also. My problem was that I was trying to create a foreign key with a name, which I had already given another foreign key.
I would try checking that the foreign key name is unique.
I am no MySQL expert, but it just a suggestion.
Thanks Niels – I updated the post to include that info as well. Hopefully will prevent one of us from pulling his/her hair out one day!
Okay, I reinstalled mysql-server-5.1.6_2, mysql-administrator and mysql-query-browser. Now mysql-administrator let me enter the FK and displays a message that the command completed successfully. However, the FK never shows up, nor after I refreshed the schema, shutdown the box, etc. It’s not there at all… I tried it on a W2K box and everything goes smooth. I had this setup working on a FreeBSD 5.4 in the past, I don’t know what’s buggy with 6.1. I’ll try to update all my ports and reinstall the damn thing again. Mmmmm…Ubuntu? Maybe
Hey Stephane, it sounds to me like you may be trying to create foreign keys on a “MyISAM” table? If you need to use foreign keys, you have to use “InnoDB” tables. You can change the table type with the MySQL Query Browser utility.
Jason,
I am using InnoDB as well. I solved my problem: I returned to the command line on the FreeBSD 6.1 box. I don’t know what’s going on since I installed FreeBSD 6.1 but MySql-Administrator goes nuts. And just to be sure it wasn’t something weird with my setup, I set up another FreBSD 6.1 box with the same stuff and still, no luck, same problems. The reason why I’m sure there’s a problem with this mix (FreeBSD 6.1, mysql-server 5.1.6_2 and mysql-administrator 1.1.6) is because everything works fine with mysql-administrator from a remote machine. I’ve been successfully doing my foreign keys remotely from a Win32 box as well as from a Fedora box.
I’ll dig that out eventually. Thanks to all for your input.
BTW, I also tried Ubuntu as a curiosity but naaahh… Too bulky
Guys, I’d like to insert some revealing snapshots here. I’ll setup a blog and provide you with a link for it. MySql-Administrator 1.1.6 and FreeBSD 6.1 do not cooperate well.
Thanks for the tip.
I was getting fustrated anf finally just did a search for MySQL Error 1005 and came across your blog. Had it fixed in a minute.
Very useful tip.. Was a bit frustrated and got help from this article!!
Hi, I had the same problem but in my case the cause of the problem was that the “Colum Charset†and “Colum Collate†was different between the key fields. It’s interesting although the both tables have already configured the same “Charset†and “Collateâ€, the fields could have different values in these parameters.
Thanks FRR – I’ll add that to the list as well. That one would definitely be difficult to troubleshoot
Index missing on the column that is being tried to have foreign key. Creating index on that column fixed it.
Hi folks,
I’ve just discover another interesting case. You can get also errno: 150 when you try to do the following: create table A, create table B with the relation many to 1 (respectively) and then create table C which is in relation one to many with table B and it is also in relation one to many with table A. I though this relations should work fine but I figure out if I take one of them I can create all the tables A, B and C if you know what I mean
You might want to mention for newbies like me, that your foreign key must point to a primary key. I assumed that you could just point to anything.
Thanks everyone for posting your causes – I’ll continue to update the article with your ideas.
Jesus, this is really a crap error message. It’s also thrown if you have the wrong syntax:
ALTER TABLE ADD CONSTRAINT fk_foo FOREIGN KEY (foo_id) references foo
throws this error. It should of course throw a syntax exception… This works:
ALTER TABLE ADD CONSTRAINT fk_foo FOREIGN KEY (foo_id) references foo (foo_id)
Bravo! In my case, problem was about MyISAM and INNODB table types.
Thanks for this post. Was beating my head against the wall on this one until I found this.
In my case, it turned out to be a problem with the jack ass trying to create the table. I was a little overzealous in my copy/pasting writing the SQL to create the tables and forgot to change the name of one of the columns. When I tried to create another table later with a foreign key that referenced the correct name, it failed. Took me a little while to realize that the problem wasn’t with the table that was erroring out, but with the one I was referencing.
Oh, and I’m dumb. I’m sure that contributed.
Thanks again.
My problem is that you have to create an INDEX on the table where you have the referencing column. Example:
CREATE TABLE `gcom_dbo`.`PERFILES` (
`IDPerfil` INTEGER NOT NULL AUTO_INCREMENT,
`Nombre` VARCHAR(20) NULL,
PRIMARY KEY (`IDPerfil`)
)
ENGINE= INNODB;
CREATE TABLE `gcom_dbo`.`USUARIOS` (
`IDUsuario` INTEGER NOT NULL AUTO_INCREMENT,
`IDPerfil` INTEGER NOT NULL,
`UserName` VARCHAR(50) NULL,
`Password` VARCHAR(50) NULL,
PRIMARY KEY (`IDUsuario`),
INDEX (`IDPerfil`),
CONSTRAINT `FK_USUARIOS_PERFILES` FOREIGN KEY `FK_USUARIOS_PERFILES` (`IDPerfil`)
REFERENCES `gcom_dbo`.`perfiles` (`IDPerfil`)
ON DELETE NO ACTION
ON UPDATE NO ACTION
)
ENGINE= INNODB;
Made me lost like 3 hours to work it out. It’s strange that nobody else said this… we’re migrating from MSSQL Server 2000 to MySQL 4.0.20 (pretty old stuff, I still have to figure out what to do with triggers!)
Good Luck!
Thanks for the tricks, but after hours of testing and re testing we found another issue : There should be no ‘defaut 0′ close on the foreign key column in the child table.
Greetings,
Make sure when use of ON DELETE SET NULL with a foreign key, it is not setted to NOT NULL
THANK YOU! As many others have commented above, this blog entry saved me a great deal of time and hair pulling.
(my 150 was, for your statistics, caused by differing collations)
This Article helped me a lot. I was really frustrated with the error and the InnoDB/MyISAM combination was killing me. One of the tables was MyISAM. Thanks you very much for putting up this article. It saved me a lot of time.
– Vamsi
I can’t alter tables, drop key, nothig. I can’t do nothing with my database. And always show this message:
#1005 – Can’t create table ‘#sql-a2f_2e680′ (errno: 13)
Thank you for your blog! My problem was not on the list but at least this article gave me light to hunt what was happening. In my case the problem was simply that I had TWO primary keys in the referenced table by accident! I simply dropped the other primary key and presto.
Sometimes when you get this error you can get more information by checking the InnoDB log with ‘SHOW INNODB STATUS;’ It helped me pinpoint the problem.
In my case datatypes of both tables were not same, one was int with unsigned and other was just int. I put the unsigned in the other one and it worked fine. Both tables should be innoDB
If you think you’ve tried it all, check that you are trying to reference an EXISTING field. So, perhaps you have “id_table” instead of “idtable”. The “_” can be a baddie sometimes
This post really helped me. My problem was that the referenced table was MyISAM. I changed to InnoDB and everything works nice now. Thank you Jason.
My problem of migratina an MS Access db to MySQL was solved by rewriting the ALTER TABLE table1 ADD FOREIGN KEY… statement. There was a syntax error, I guess, from the fact that the two FK were a composite PK in table1. Excellent blog, btw.
I had this problem, but it was the famous user error. I defined the foreign key column as ‘NOT NULL’ and then specified that on delete set NULL. Makes perfect sense that MYSQL refused to create the table.
Thanks – mine was #4
ammm…can i allso add that none of all that is here helped me
(
a good solution would be to use the plain old SQL
THANK YOU SO MUCH! This has been driving me crazy for the last couple days and I finally got it working. I didn’t know every foreign key has to have a unique name withing the database. What a pain in the ass!
Thanks, this was a big help.
Thanks, you saved me a lot of time and headache. Mine was #1.
Thank you, Jason. I was struggling with this error for hours now. Excellent posting!
Great post. I happened across this while researching this error. One thing that you mentioned what that “one or the other must be indexed”.
But i wish to add that if one or the other (PK or FK) is part of a combined primary key/index, you will STILL get this error, you MUST create a separate index for the field you wish to reference with your FK.
Thanx
Thanks Alex. I didn’t know that was the case but I don’t use combo keys too often. I can see that one stumping somebody so I added your tip to the article.
Ty #3 !!
Also, UNSIGNED and ZEROFILL properties for the foreign key columns must match.
Here is my SQL code: NO CLUE what is going on with this error…
CREATE TABLE PCP
(
PCPID MEDIUMINT NOT NULL,
PCPName VARCHAR(75),
PCPMD MEDIUMINT NOT NULL,
PRIMARY KEY (PCPID),
FOREIGN KEY (PCPMD) REFERENCES PCP
);
I placed the primary key field when using the foreign and that work, just as Christian did further above.
Jim try this code:
CREATE TABLE PCP
(
PCPID MEDIUMINT NOT NULL,
PCPName VARCHAR(75),
PCPMD MEDIUMINT NOT NULL,
PRIMARY KEY (PCPID),
FOREIGN KEY (PCPMD) REFERENCES PCP (PCPMD)
);
Hope this works as this did for me.
BTW i noticed in you code your pulling data using the (foreign key) from itself as the primary key is in the same table, have you tried pulling data from a primary key of another table.
Example
FOREIGN KEY (PCPMD) REFERENCES PCP2 (PCPMD)
Woo Hoo! That did it! I understand that this product is open source and free, but if there’s been this much wasted time in the world on this, wouldn’t one think that an owner, might document at least the usage? I see references to this dating back to 2006. Now I must admit that I didn’t look at the mysql forum, I just googled it and found my way to here, after several sites trying to peddle something, including Micro$oft. Just my 2 cents, and two hours. Jeff
Oh, and BTW, isn’t specifying the primary key we’re referencing redundant, hence the confusion, but I may be wrong. I’m using server 5.5.
Thanks a lot.. i was going nuts on this bizzare err.. this aricle saved a lot … mine was INNODB == MYISAM … [:)]
Mine was number 5, thank you!!!
I had a problem where the primary key of the referenced table was not the first column on the table and I couldn’t use it as a foreign key. Droping the table and having the primary key as the first column fixed the issue. Both tables were Innodb
Cheers,
Arturo
Thanks!
Number 3 worked for me. Actually it is very obvious that foreign key name must be unique
Ivan
i nead to know very soon.. is there any possible to use char 0r varchar to set auto_incriment
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?
Thank you so much. This helped us find the solution to our problem. You are the best
Dear Jason,
Thanks a lot.. your blog helped me to solve my problem. thanks again.
Thank you verry much! Just didn’t know there has to be uniq foreign key names.
Thank you for this article. Just printed it out. It just saved the remaining hair I am left with!
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.
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
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.
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!
Thanks, the 4th cause worked for me
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.
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.
You’re great!
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
thanks a lot
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
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
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.
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
Thanks Nyleta, I found the max length is 64 chars, so I’ll add this to the list
It the definition of the local columns that is different to the ForeignKey-Column (such like “UNSIGNED”, (), etc.)
Thanks for the information. I had the problem of one is SIGNED and the other is UNSIGNED.
Your blog helped a lot!!!
Thanks again
Hi,
Thank you Niels, I had this problem and with your suggestion solved.
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.
“You should also check that one is not SIGNED and the other is UNSIGNED.” Ahh..HAH!
Very good! Thank you sir.
-Brian
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.
thanks a lot, very helpful information
This was incredibly helpful. I usually avoid these kind of sites, but this helped what was proving to be an extremely frustrating issue. Brilliant.
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
Thanks.
I’ve solved the problem.
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!
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.
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.
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.
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.
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.
Make sure the tables are clean, with no data in them….
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.
thaaaaanks a ton … got the prblem fixed in lesser time than expected.
))))) i am so happppyyyy
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.
Thanks heaps!!!!!!!!!!!!!!!
My problem was point 3 – duplicate fk name
thankyou!
Can i translate this Posts to vietnamese and post it in our public site?
hi thao, that would be ok – it would be appreciated if you link back to my site as well.
Hi………. Thanks a lot. Great work. Helped me a lot.
ciao, thank you for this. my problem was #8
Issue number 6 was my problem today. Thanks for the solution.
Really helpful post mate
Dude ur the best! i solved my pproblem using #6! Thanks alot.
nice this trick saved my hell amount of time, thanks a ton
In the case it serves to somebody: Do not skip step6.
I’ve been messing with the same problem. The DATA TYPE was identical. In both cases a VARCHAR(36) which I use to store as primary keys standard UUID values as “string”.
Nevertheless, being them text, they have a “charset”, and one table was “UTF” and the other “LATIN”.
They did not link for that reason.
Changing the charset to be coherent (both to utf8 or both to latin) I was able to create the FOREIGN KEY without the error of “can’t create table…”
Also:
If the primary key you want to connect to your foreign key is UNSIGNED make it G** D*** SURE that your foreign key is also UNSIGNED.
Hey, Fixed my Bug! Thx!
I encountered this problem in this case
I created a foreign key
On Delete = Set Null
On Update = Set Null
but the foreign key field does Have “Not null ” flag
Just removed the check by mysql GUI administrator and Everything was just fine
I had the same problem and spent many hours on troubleshooting. This article helped me to figure it out the issue. It was I have set the ondelete to set null where the table field is set not null.
Thanks
My issue came up because I was trying to import/build a new table that had foreign keys in tables that didn’t exist. The export from the old MYSQL server exported the tables in alpha numeric order, but on the new server, my CONSTRAINT statement was referencing foreign keys in tables that didn’t yet exist as they were further down in the file. So I just cut and pasted the Create table statements into a new order, where all the ones that needed foreign keys were built after the tables they referenced. And it worked! Hope this helps others…
If you use the SQL command,
SHOW ENGINE INNODB STATUS;
it will give you are more detailed error message.
Thanks, It solved my problem. One table had UNSIGNED checked and the other not. Pretty simple but very hard to find.
THANKS BABY! My solution was number 4.
thy! uft8 != latin1
Thanks! it helped me I solved my permanent problem
The error may occur, if both the fields having different storage engine.
Many thanks.
I remove “NOT NULL” from the FK column and it works!
Thanks! #1 was my match.
For some reason, this table was created with INT(5) for the PK, where all previous tables were SMALLINT(5). I have no idea why the GUI did that.
But now I know.
Thanks again.
unable to create partition on a parent a child table.
query executed:
ALTER TABLE table_name1 PARTITION BY KEY (pk_column_name1,pk_column_name2);
ALTER TABLE table_name2 PARTITION BY KEY (pk_column_name1,pk_column_name2);
parent table name:table_name1
child table name: table_name2
need to alter both the tables.
dude, thanks. it was number 6
Good!As I have try the first one ,my problem was solved!
haha,thanks a lot!
You’re a saviour! My error was number 5, I had FK set to “on delete set null” which was referencing a column that was “not null” in hind sight my mistake was so blindingly obvious but at the time I just couldn’t see the wood for the trees.
This is fantastic list and a great resource
Cheers
The thing that I noticed is the Character set of both the columns should be same….for e.g. A table has primary key named ID,Now the table which want to use this key as foreign key ,the column to which it is relating to be the foreign key should have same character set as that of the A table as well as the size should also be same.
hi just run into another one
let me show you
it doesn’t work
constraint `fk_res_num_occ` foreign key(`occ_res_num`) references`hotel`.`reservation`(`res_num`),
it works
constraint `fk_res_num_occ` foreign key(`occ_res_num`) references `hotel`.`reservation`(`res_num`),
there was no space after references
I had a really weird one I just fixed… Nothing on this list made a difference…but I noticed that a second column (NOT USED AT ALL IN ANY WAY BY THE FK) in the original table had a unique index…so on a whim, I deleted it…was able to add a foreign key, then reinstate the unique index…
No idea why that should matter…but if none of the above helps you, try it…
If MySQL is acting like the usual piece of crap that it is, you might want to do this:
SET FOREIGN_KEY_CHECKS = 0;
To disable checks. It worked for me so far, as I couldn’t find the problem, despite your wonderfully compiled list, and don’t intend so spend the rest of my day on this.
Once done, there is still the option to re-enable the sadistic masochistic checks:
SET FOREIGN_KEY_CHECKS = 1;
Hope this info will be added to your post
Yes! this page was really helpful….Thanks a lot…
Thanks for the info….
Thank you very much!!!!!!!!!!
………
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)
…….
this step helps me
Hi,
Really really useful collection. Thanks a lot……
Ok, and here is another dumbass thing that caused it.
If you misspelled a foreign key, it doesn’t give you a hint, it gives you an errno 150.
had a primary key namet agnetID (should have been agentID), tried to refference that by calling the name that it should have had, but haddn’t
I know that it is a stuppid fault, but a plausible one none the less.
Thanks for your website!
Ouch. I just had 2 hours of frustration with this stupid error message. And going though the list suggestions above, number 6 finally did it. The field’s character set was UTF8 the referenced table, but the database default character set is LATIN1 and the new table I created didn’t have character set specified for that field. So it was created as LATIN1…
Boy this sucks! Now I have to search through my database definitions and find out what other tables may give me a headache in the future.
Thanks for this page. It should really be on MySQL’s web site. Why are the devs so ignorant to the general user public and through non-descript error messages on the screen like this? If the innodb engine knows what the problem is (which I checked with the show innodb status), then the error MySQL throws should be able to be more acurate than it is right now. (my two cents)
Thank you so much, issie 5 was my problem, I was trying to “ON DELETE SET NULL” for a NOT NULL column, doh!
Thank you!!! #1 INT(10) vs INT(11) How id that INT(11) get
in there???
Segui todos os passos e obtive sucesso vlw, o que mais pegou: criar indice com os campos relacionados nas duas tabelas, mesma tipagem de campos ate com o mesmo tamanho,Thanks!
(Automatic Portuguese to English translation: I followed all the steps and got success VLW, which took over: create index with the related fields in both tables, the same type of the fields even with the same size, Thanks!)
THANK YOU!
very good!
i had this error, because my key had the unsigned attiribute
Thank´s men!!!
My problem with adding a foreign key was that the auto increment for the primary key in one of the tables was some huge number. I manually changed it to 24 and was able to create a foreign key after that.
Thanks bro!!! I had unchecked the UNSIGNED option in one of my tables.
Mine solved by checking column FK in a table not null (FK for every row must has value). BTW, thanks for sharing this entry
It was really a great help for me ….i was breaking my head but finally i got it working……..
Although there are hundreds of reasons mentioned here,(I read each and everyone) none of them solved my problem………….!
SO SAAAAAAAAAAAAAAAAAAAAAAAAAD………………..!
Number one cured my headache! Thanks a lot!
#8 saved my day!! (after 2 hours working on it)
Thanks!
Thanks!. This post, solved a lot questions; thanks again.
thank u soooooooooooo much
Great page – thanks
Hi,
Great thanks for this post!
I was debugging the last 3 hours and searching for a solution of this error …
Finaly I found out that there’s an sql-error in the symfony-plugin sfDoctrineUserPlugin I tried to install.
Here’s the error in details. Maybe this helps someone who’s searching for this error using google
110518 19:43:02 Error in foreign key constraint of table commerce/#sql-fbe_d3:
FOREIGN KEY (sf_guard_user_id) REFERENCES sf_guard_user(id):
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html
for correct foreign key definition.
My workarround: I simply removed this plugin
My problem is the same: errorno 150.
But we are more than just a DB.
It’s simple:
1- Open your table with FK
2- Click in the tab “structure” (or Estrutura at PT BR) (please, fix if the name is other in EN)
3- Find Relationships or something (or ver relações em PT BR)
4- Find your FK with error
5- SET NULL or first camp of the inputs field
6- BACK and delete or change your FK.
I just don’t know what but this works.
Mail me if you want help or try this and dont understood what I said. =)
Another cause : The table referenced by a foreign key constraint doen’st exist. It may happen if you tested your Create request using a windows box, and tried to run it on a linux box. table names being case insensitive under windows, this can lead to the error #150
Helo everybody !!!
I was found this problem and i was solve this way
with MySqlAdministrator in the window MySql Table Editor unchecked the flag UNSIGNED for the field
thats is the reference by foreing key
and make click on apply changes and create the foreign key.
I hope help us.
I had this problem when trying to run some create table scripts that I got from someone else. The scripts ran fine (in phpMyAdmin) on MySQL 5.1.54, but failed on another server running 5.5.13
This post helped me to focus on the FK statements, and I found that the scripts used lower case for the table name.
This worked in 5.1 but failed with a 150 error in 5.5:
FOREIGN KEY(colaborator_id) REFERENCES colab(id)
This worked in 5.5:
FOREIGN KEY(colaborator_id) REFERENCES COLAB(id)
Thanks for pointing me in the right direction – I don’t know enough about MySQL to have got there by myself otherwise.
Roy
Thanks a lot man
it was the index for composite key thing..never would’ve guessed it
I finally got to the bottom of this myself when it turned out to be the ordering of the foreign key elements in the add constraint such as:
ALTER TABLE table1ADD CONSTRAINT `fk1`
FOREIGN KEY (`field2` , `field3` , `field1` )
REFERENCES `db.table1` (`field2` , `field3` , `field1` )
was failing because the indexes in referencing and referenced tables were in order field1, field2, field3 so it should have been:
ALTER TABLE table1ADD CONSTRAINT `fk1`
FOREIGN KEY (`field1` , `field2` , `field3` )
REFERENCES `db.table1` (`field1` , `field2` , `field3` )
Seems obvious now but I was using fwd generation of db from mysql workbench which seemed to have acquired some sort of bug since I noticed this issue in the fk in my diagram and amended it but the sql was still generating incorrectly.
I have been pulling my hair out trying to work it out and finally can move on past to new and exciting (frustrating?!) problems.
Thanks a Lot… for me it was Collation of Field.
Hi there.
I had the same problem.
My sql that causes the problem:
CREATE TABLE `Reports`( `ID` INT NOT NULL AUTO_INCREMENT, `DeviceID` varchar(50) NOT NULL, `Date` datetime NOT NULL, `DriverVersion` varchar(50) NOT NULL, `SignalLevel` int NOT NULL, `ByWho` varchar(10) NOT NULL, `Time` varchar(50) NOT NULL, `TryNumber` int NOT NULL, `Source` text NOT NULL, PRIMARY KEY (`ID`) ) ENGINE = InnoDB; CREATE TABLE `ReadNumbers`( `ID` INT NOT NULL AUTO_INCREMENT, `ReportID` INT NOT NULL, `NumberOfReadsWithReset` INT NULL, `DateOfThisRead` DATETIME NULL, `TimeOfThisRead` VARCHAR(50) NULL, `ThisTerminal` VARCHAR(50) NULL, `DateOfLastRead` DATETIME NULL, `TimeOfLastRead` VARCHAR(50) NULL, `LastTerminal` VARCHAR(50) NULL, `UserDefinedField` VARCHAR(50) NULL, `NumberOfReadsWithOrWithoutResetSinceInitialization` INT NULL, `NumberOfResetsSinceInitialization` INT NULL, PRIMARY KEY (`ID`), INDEX `FK_ReadNumbers_Reports` (`ReportID` ASC), CONSTRAINT `FK_ReadNumbers_Reports` FOREIGN KEY (`ReportID`) REFERENCES `Reports` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE = InnoDB;But if I write like this:
REFERENCES `reports` (`ID`)it works perfect.
I think the problem is that I set lower_case_table_name value to 0 on Windows machine. I know, during this article (http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html)
I should not do this, but I really need it.
P.S. I am ms-sql guy, mysql is new for me.
THANK YOU!!!!
I was strugling for 2 days now.
For me the problem were the indeces of the fields linking to the foreign keys.
Thanks Again
The error can occur if the table already has data in it. Empty the table (Truncate) and then create the foreign key.
There may be a better way to handle this but this resolved my error. *First backup your data so you can import it later.
You can create foreign keys with data in the table, but you have to make sure all of the existing rows conform to the new constraints.
Thanks a ton for “SHOW INNODB STATUS”
This post completely saved my ass. Thanks!! effin awesome the tips
create table company (c_id int(10) NOT NULL,company_name varchar(255) NOT NULL,PRIMARY KEY (c_id));
create table company_users (u_id int(10) NOT NULL,user_name varchar(255) NOT NULL,phone varchar(25) NOT NULL,status int(1),company_id int(1),PRIMARY KEY (u_id),Foreign Key (company_id) references company_users(c_id));
mysql> create table company_users (u_id int(10) NOT NULL,user_name varchar(255)
NOT NULL,phone varchar(25) NOT NULL,status int(1),company_id int(1),PRIMARY KEY
(u_id),Foreign Key (company_id) references company_users(c_id));
ERROR 1005 (HY000): Can’t create table ‘.\sanjay\company_users.frm’ (errno: 150)
I had received the same problem.
I had a similar problem, mysql complaining about an INNODB table which hadn’t any foreign keys.
The problem was: I tried to restore a mysqldump file without a drop database command.
I am used to MYIsam tables, so I just moved away the database directory on the filesystem (so I had a backup)
So I solved my issue by moving back the ‘backup’, copying the database directory, rather than moving it, and then dropped the database on the mysql command line
Awesome Post..!!!
This is such a common error, that you come across while doing development on mysql, and sometimes after frustration are so fed up, that you decide not to have that damn foreign key AT ALL. But having all the points, at a single place really helps. Thanks a ton….
Thanks for your splendid list of possible reasons. I just found another one: Foreign keys are supported for InnoDB only, for compatibilty reasons, the statement is ignored for MyISAM tables without error message. but if you mix InnoDB and MyISAM, (e.g. the referencing table is InnoDB, but the referenced table is MyISAM), you get this error message.
IT WAS THE SIGNED AND UNSIGNED DIFFERENCE IN MY CASE …
Thank you so much for this article! Turns out when importing my SQL data, one out of all of my database tables was configured as MyISAM, which just so happened to be part of the first foreign key relationship.
Five years have passed and nothing changed. That’s just sad…
Oh, it was MyISAM/InnoDB in my case: table with foreign key was InnoDB, but referenced one was MyISAM, which resulted in this cryptic error. On insert, mind you, not during creation or altering of a table with foreign key…
Thanks a lot Jason you made my day!
I was having the “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)” issue.
Cheers!
Friends I found the solution, but you need to know spanish:
El problema se presenta al crear las relaciones entre las entidades, al momento de declarar una clave foranea esta no se puede usar mas de una vez con el mismo nombre de la entidad a la que se hace referencia.
lo unico que hice es cambiar los nombres de las claves foraneas para que no sean iguales a como se llaman a la tabla referenciada y listo, ejecute el script y no tuve errores
(translated: The problem occurs when create relationships between entities, upon declaring a foreign key that can not be used more than once with the same entity name to which it refers. All I did is change the names of foreign keys so they are not the same as they are called to the referenced table and go, run the script and had no errors)
Great list, thanks, and while my cause wasn’t on it I was pointed in the right direction. The foreign key was referencing a field that was not the first item in an index.
Thanks Jason for the article. I have checked each one of them but none seem to have caused the error in my cause. I posted what worked for me in the end.
Below is the my script:
CREATE TABLE `table1` (
`col1` varchar(8) NOT NULL,
`col2` decimal(8,2) NOT NULL,
`col3` date NOT NULL,
`col4` date NOT NULL,
`col5` varchar(20) NOT NULL,
PRIMARY KEY (`col1`)
)ENGINE=INNODB DEFAULT CHARSET=latin1;
CREATE TABLE `table2` (
`pk_id` int(11) NOT NULL AUTO_INCREMENT,
`col2` int(11) NOT NULL default ’0′,
`col3` varchar(8) NOT NULL,
PRIMARY KEY (`pk_id`),
KEY(`col2`),
CONSTRAINT FOREIGN KEY (`col3`) REFERENCES table1(`col1`)
)ENGINE=INNODB DEFAULT CHARSET=latin1;
The second table was failing with the error 150. When I removed the acute signs in the foreign key statement line it worked.
Regards,
Prash
Sometimes, creating the FK will fail because you have named both columns the same.
e.g.: ALTER TABLE `Projects`
ADD CONSTRAINT `fk_projectCLID` FOREIGN KEY (`CLID`) REFERENCES `Classes` (`CLID`) ON DELETE NO ACTION ON UPDATE CASCADE;
fails because both columns are named CLID – try changing the ame of one of them.
New Solution Version:
The Problem had to do with the way, the dump was created.
I tried to import with phpMyAdmin a dump that was also made with phpMyAdmin: it failed and throwed the error as reported above.
When I tried to import a dump that was made with mysqldump from the console: it worked!
Thanks a lot for collecting the possible Solutions, it hasn’t got the right answer for me, but I could exclude some kind of possibilities.
Dan
mysqldump probably disables all key violation errors while doing the import would be my guess. glad you got it sorted out.
thank u
This one was my case.
The two key fields type and/or size is not an exact match. 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).
Excellent Post! very well explain every detail of the problem…was the only one post that really could help me
Thanks a lot!
I had the MyISAM vs InnoDB issue…
Such chaos.
I have been having problems in getting this to work….:
CREATE TABLE Booking (
cottageNo char(5) not null,
customerNo char(5),
dateFrom date,
dateTo date,
status varchar(10),
primary key(dateFrom),
foreign key (status) REFERENCES cottage ON DELETE SET NULL ON UPDATE CASCADE);
Any suggestions?? I am using the InnoDB.
“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)”
This was my problem, but even after I read it, it took me a while to figure it out.
You need to create an INDEX on the column you are referencing on the PARENT table.
thanks guys for the suggestions! keep up the good work
, what worked for me this: make sure the foreign key field exist in the table in wich you make a reference from to another table. not only in a manually create table statement the line : foreign key (test) references table(test). (like my teacher said ^^)
also what worked after all was to just create the table and then alter the table, add a field and add the foreign key.
“SHOW INNODB STATUS” is deprecated in 5.0 and removed in 5.5. It’s “SHOW ENGINE INNODB STATUS” now.
thanks, i added it to the article
your post helped me a lot.
my problem was #3. my foriegn key name was not unique.
When using InnoDB and working between different OS’s (Win and Linux…), watch out for the case-sensativity:
http://blog.rough-sea.com/2010/06/innodb-the-case-sensitive-monster/
When I set lower_case_table_names to 0, I started getting the 1005 error.