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 18B66D1B17C for ; Thu, 13 May 2004 11:51:02 -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 23099-07 for ; Thu, 13 May 2004 11:50:46 -0300 (ADT) Received: from ptb-relay02.plus.net (ptb-relay02.plus.net [212.159.14.213]) by svr1.postgresql.org (Postfix) with ESMTP id 57D00D1B170 for ; Thu, 13 May 2004 11:50:41 -0300 (ADT) Received: from [212.159.87.229] (helo=thelabyrinth.force9.co.uk) by ptb-relay02.plus.net with esmtp (Exim) id 1BOHXm-000MyV-UJ; Thu, 13 May 2004 14:50:43 +0000 Received: from ruth.localdomain (ruth.localdomain [192.168.0.1]) by thelabyrinth.force9.co.uk (Postfix) with ESMTP id AA77D30081; Thu, 13 May 2004 14:51:13 +0100 (BST) Date: Thu, 13 May 2004 15:52:04 +0100 (BST) From: Sam Barnett-Cormack X-X-Sender: sdb@localhost.localdomain To: CHRIS HOOVER Cc: pgsql-admin@postgresql.org Subject: Re: Help with foreign key creation problem In-Reply-To: Message-ID: References: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII 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/131 X-Sequence-Number: 13447 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. > > Please help me understand what is going on, and what I am missunderstanding > about foreign keys. Only slightly, you're creating the wrong way around. You want to be adding the foreign keep to table2. -- Sam Barnett-Cormack Software Developer | Student of Physics & Maths UK Mirror Service (http://www.mirror.ac.uk) | Lancaster University