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

[...] is not the case - it is just a silly, misleading message. I started googling for it and found some good tips on how to avoid this error at VerySimple Dev Blog. I spent over two hours testing all possible solutions listed in that post. [...]

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)

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.

[...] VerySimpleDevBlog [...]

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