This has caused me 3-4 hours of time wasting and it is so bloody simple! I can't believe after googling for so many hours that I could not find something that could solve this problem for me.
If you've came across an error #1452 when trying to add a foreign key and you have existing entries in the table, you need to make sure there are valid values in all entries in the foreign key column before creating a foreign key.
So what I did was do a zero fill on the foreign key column. Sweet I thought. This should fix it. After trying to create the foreign key again, I get an error #1216. Turns out that the values in the foreign key column must be valid (i.e. correspond to a valid id to referenced table) and the zero fill obviously didn't help because I dont have any id with 0.
Once you've entered valid values in, the foreign key creation should work! Make sure that the column in the referenced table is a primary key or an index and foreign key column is an index before proceeding.
Thursday, 18 October 2007
Foreign Key in MySQL Error #1452 and #1216
Subscribe to:
Post Comments (Atom)
41 comments:
Just what I was looking for !
Thanks, very helpfull !!
Thanks for the tip.
I still have some issues when I try to add foreign keys. By chance, do you have any idea of the cause of error #1005
Hey dude thank you for this topic !!
It was pretty useful to me !
You came from heaven !
Really thank you! Congratulations that you publish the solution! I was desparate but you saved me!
No worries, I'm glad this solution helped you guys.
Thank you. Seriously.
Very helpful - sorted my prob after 2 hours googling in vain.
Hey, It works..Thanks a lot.. It was really helpful..
So helpful. Thank you heaps
Oh my, thank you for this.
How insane, I just wasted two hours until I found this.
TYVM!
yes, thank u really much for this :)
Really dumb... I wasted like 3 hours as well trying to fix this error. Thanks a lot!!!
Thanks also, this explanation finally helped - better than the useless descriptions on the mysql forums that just tell people to get stuffed.
Yeah, THANK YOU, THANK YOU! REALLY HELPFUL AND HOUR-SAVING!
About error #1005:
1. Check your database type. Be sure they are both InnoDB.
2. Check your field types. They must be absolutely the same - for example INT (11) NOT NULL or whatever but both field types must be absolutely the same
3. Keep asking Google ;)
Thank you!
With the information you provided, I was able to track down the error in my database. My foreign key field type did not match the primary key field type. The resulting data some field became truncated and therefore did not match.
For anyone else, check to make sure that your field types match in both tables! :)
Thanks again for the information!!
ty much, I had the same problem, spent several minutes double checking the database!
Thanks a lot, it fixed my problem too.
This post just saved me hours of frustrasion. Thanks a lot!
I don't understand...
you say:
"Once you've entered valid values in, the foreign key creation should work!"
What you mean by valid? Of course they are valid, I have primary ids and the foreign keys are valid (they exist in the parent table).
Could you be more specific?
I can't get out of this problem. Both tables columns are exact same, except the parent is a primary key and auto_increment.
Wow! I spent 3-4 hours digging through the web and trying every trick I could find, and this was the solution. Many thanks for putting this up.
Thank you, thank you, the angels sing your name.
hey, thanks! i needed that answer so badly.. good thing you posted!
thanks! =)
Grrrrrrrrrrrrrrrrrrrrrr!! Where were you six hours ago??!! Thank you so much for this very very useful tip. Finally!
THANKS! I was almost ready to give up and restructure my db :) That worked like a charm!
thank you for this and for the message on the captcha!
Thanks a lot - after 2 wasted hours u did the trick
nice solution. I wrote sql code to see the inconsistence:
SELECT * FROM table1 where id not in (select id from table2 );
Nice - real helpful - THANKS
Thank you! This was just information I needed!
Well said!
Thanks for your idea... Now I'm trying to resolve a problem when I insert data to some tables... g'bye!
Thanksssssssssssssss
Thank you very much - finally. Just feal so stupid now that it was so simple.. :)
you're right, the solution is obvious. thanks
Thanks for taking the time to post your observations. You saved me hours of head-banging and I'm very grateful! :)
great! thanks...
Really Superb :)
Thankyou... :)
Thanks allot. My problem was engine type difference
thanku very much....
Post a Comment