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 49826D1B171 for ; Thu, 13 May 2004 11:38:23 -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 21261-01 for ; Thu, 13 May 2004 11:38:01 -0300 (ADT) Received: from bcbssc.com (mail1.bcbssc.com [207.19.198.69]) by svr1.postgresql.org (Postfix) with ESMTP id E04C3D1B19E for ; Thu, 13 May 2004 11:37:53 -0300 (ADT) Received: from ([207.19.198.73]) by mail1.bcbssc.com with ESMTP ; Thu, 13 May 2004 10:37:21 -0400 X-Internal-ID: 40A07AB20000326A Received: from companiongroup.com (207.19.198.73) by companion1.companiongroup.com (NPlex 2.0.108) for pgsql-admin@postgresql.org; 13 May 2004 10:36:53 -0400 Message-ID: Date: Thu, 13 May 2004 10:34:00 -0400 From: "CHRIS HOOVER" To: pgsql-admin@postgresql.org Subject: 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/129 X-Sequence-Number: 13445 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