Custom Software Development

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

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.

Gerhard Kratz :
I got rid of this problem, when I added the name of the primary key of the table to be referenced to the foreign key constraint. Example:
NOT: FOREIGN KEY ( articleFK ) REFERENCES Article ON DELETE …
BUT: FOREIGN KEY ( articleFK ) REFERENCES Article ( articleID ) ON DELETE …

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….

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………. 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

anonymous :
It the definition of the local columns that is different to the ForeignKey-Column (such like “UNSIGNED”, (), etc.)

Dude ur the best! i solved my pproblem using #6! Thanks alot.

nice this trick saved my hell amount of time, thanks a ton