X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 8CF35D1B169 for ; Thu, 13 May 2004 12:08:58 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 27337-10 for ; Thu, 13 May 2004 12:08:33 -0300 (ADT) Received: from bcbssc.com (mail1.bcbssc.com [207.19.198.69]) by svr1.postgresql.org (Postfix) with ESMTP id 87074D1B16B for ; Thu, 13 May 2004 12:08:32 -0300 (ADT) Received: from ([207.19.198.73]) by mail1.bcbssc.com with ESMTP ; Thu, 13 May 2004 11:07:35 -0400 X-Internal-ID: 40A07AB20000335D Received: from companiongroup.com (207.19.198.73) by companion1.companiongroup.com (NPlex 2.0.108); 13 May 2004 11:07:39 -0400 Message-ID: Date: Thu, 13 May 2004 11:05:00 -0400 From: "CHRIS HOOVER" To: Stephan Szabo Cc: pgsql-admin@postgresql.org Subject: Re: Help with foreign key creation problem X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200405/132 X-Sequence-Number: 13448 You are absolutely right. I hate it when I get turned around. Thank you very much for the assistance. Chris ------------------( Forwarded letter 1 follows )--------------------- Date: Thu, 13 May 2004 07:57:38 -0700 (PDT) To: chris.hoover Cc: pgsql-admin@postgresql.org.comp From: Stephan.Szabo[sszabo]@megazone.bigpanda.com.comp Subject: Re: [ADMIN] Help with foreign key creation problem On Thu, 13 May 2004, CHRIS HOOVER wrote: > 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. Are you sure you're making the key the direction you want? I would think you'd want a foreign key on table2(table1_id) referencing table1(table1_id) not the other way around since presumably table1 is the table with the authoratative list of table1_ids.