Help with foreign key creation problem - Mailing list pgsql-admin

From CHRIS HOOVER
Subject Help with foreign key creation problem
Date
Msg-id NXcdf526-39ff0515@companiongroup.com
Whole thread Raw
Responses Re: Help with foreign key creation problem  (Sam Barnett-Cormack <s.barnett-cormack@lancaster.ac.uk>)
List pgsql-admin
I need some help understanding and creating foreign keys in postgresql.

Here is an example of what I am trying to do:

I have table 1 with:
table1_id serial unique
table1_col1 varchar

I have table2 with:
table2_id serial unique
table1_id integer
table2_col1 varchar

When I try to create the foreign key with
alter table "schema_name"."table1"
  add foreign key ("table1_id")
    references "schema_name"."table2"("table1_id")
    on delete cascade
    on update cascade
    not deferrable;

Postgres complains with:
ERROR: UNIQUE constraint matching given keys for referenced table "table2" not
found.

Why is postgresql demanding a unique key on table2.table1_id?  It is a foreign
key in a parent/child 1 to many relationship.

Please help me understand what is going on, and what I am missunderstanding
about foreign keys.

Thanks

chris

Postgresql 7.3.4 on RH ES 2.1

pgsql-admin by date:

Previous
From: Laurens Wagemakers
Date:
Subject: Re: GNUmakefile size 0
Next
From: Tom Lane
Date:
Subject: Re: GNUmakefile size 0