October 2nd, 2003, 10:14 PM
unable to create a foreign key
I'm still trying to learn the ins and outs of posgresql. I've got the following table structure:
ing_id interger primary key
recipes extends ingredients with the following extra columns
and a table
I'm trying to create a foreign key on recipe_ingredients
on recipes ing_id
using the following statement:
LTER TABLE "recipe_ingredients" ADD FOREIGN KEY ("recipe_id") REFERENCES "recipes"("ing_id") ON UPDATE CASCADE ON DELETE CASCADE
However when ever I do this I get the following error:
ERROR: $3 referential integrity violation - key referenced from recipe_ingredients not found in recipes
Is there any way I can do this, or will I need to look at triggers?
October 3rd, 2003, 08:01 AM
That error means you already have an invalid reference in data inside the table. You need to make sure existing data is valid for ANY new constraint, FK, check, whatever, BEFORE applying the constraint.
October 3rd, 2003, 08:23 AM
could you avoid this problem by doing a pg_dump to a file with just the insert statements for that table, then apply the constraint then move the data back? U should know the constraints ri when you make the tables in an ideal world but that's why they make the dump tool.
October 3rd, 2003, 08:34 PM
There is only a little bit of test data in there, easier to do fix it up by hand. Thanks for the help.