Help with foreign key creation problem

From: "CHRIS HOOVER" <CHRIS(dot)HOOVER(at)companiongroup(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Help with foreign key creation problem
Date: 2004-05-13 14:34:00
Message-ID: NXcdf526-39ff0515@companiongroup.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Tom Lane 2004-05-13 14:43:07 Re: GNUmakefile size 0
Previous Message Laurens Wagemakers 2004-05-13 14:19:36 Re: GNUmakefile size 0