Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Creating a Foreign Key

Status
Not open for further replies.

murphyhg

Technical User
Mar 1, 2006
98
US
I have been struggling with how to create a Foreign key on these 2 tables (anchors, properties). The tables already exist and have data in them.

This is my script to create the Foreign Key and below is the error.
Code:
ALTER TABLE `anchors` ADD CONSTRAINT `PROPERTYID_FK` FOREIGN KEY (`PROPERTYID`) REFERENCES `properties` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION;
ERROR
Code:
[SQL] ALTER TABLE `anchors` ADD CONSTRAINT `PROPERTYID_FK` FOREIGN KEY (`PROPERTYID`) REFERENCES `properties` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION;
[Err] 1452 - Cannot add or update a child row: a foreign key constraint fails (`mysql_renaud`.<result 2 when explaining filename '#sql-b18_49'>, CONSTRAINT `PROPERTYID_FK` FOREIGN KEY (`PROPERTYID`) REFERENCES `properties` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION)
Thanks for your help!
 
First, make sure that all references in the existing data are correct, which means that no record points to a non-existing record in the other table. Also check if the data types are exactly the same in both ends of the reference.

If the above does not solve the problem, please post the table definitions.

The filename part of the message puzzles me though. An ALTER TABLE command is usually done on a temporary table (and therefore a temporary file), but I never saw an "error explaining filename". Are other queries succeeding? Could this be a permissions problem?


+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)
 
Don, thanks for your quick reply. You were correct. I had some data in the propertyid column in the anchors table which did not match the id of the properties table. Once I set the 0 zeros to NULL the issue went away.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top