Creating a Foreign Key

[murphyhg]

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.

ALTER TABLE `anchors` ADD CONSTRAINT `PROPERTYID_FK` FOREIGN KEY (`PROPERTYID`) REFERENCES `properties` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION;

ERROR

[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!

 

[DonQuichote]

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)

 

[murphyhg]

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.