#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2001
    Posts
    286
    Rep Power
    13

    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:

    ingredients
    ing_id interger primary key
    name
    basic_unit

    recipes extends ingredients with the following extra columns
    recipe_text
    amount_made
    made_unit

    and a table
    recipe_ingredients

    recipe_id
    ing_id
    amount_required
    weight
    id

    I'm trying to create a foreign key on recipe_ingredients
    recipe_id
    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?
  2. #2
  3. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    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.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    12
    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.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2001
    Posts
    286
    Rep Power
    13
    There is only a little bit of test data in there, easier to do fix it up by hand. Thanks for the help.

    John Joske

IMN logo majestic logo threadwatch logo seochat tools logo