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 5C335D1B4DA for ; Fri, 13 Feb 2004 00:43:32 +0000 (GMT) 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 80671-02 for ; Thu, 12 Feb 2004 20:43:33 -0400 (AST) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id A835DD1B4D2 for ; Thu, 12 Feb 2004 20:43:29 -0400 (AST) Received: (qmail 20578 invoked by uid 500); 13 Feb 2004 00:45:20 -0000 Date: Thu, 12 Feb 2004 18:45:20 -0600 From: Bruno Wolff III To: Tuan Luu Cc: pgsql-admin@postgresql.org Subject: Re: Foreign Key Question Message-ID: <20040213004520.GA22135@wolff.to> Mail-Followup-To: Tuan Luu , pgsql-admin@postgresql.org References: <40273DD0.6030004@gmx.ch> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <40273DD0.6030004@gmx.ch> User-Agent: Mutt/1.5.4i X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/196 X-Sequence-Number: 12370 On Mon, Feb 09, 2004 at 08:59:12 +0100, Tuan Luu wrote: > Example: > > Table 1: > Inventory, Productnr, Productserial > (PK Inventory, Prouctnr) > > Table 2: > Pruductnr, Produktdesc, Productversion > (PK Productnr; FK Productnr) > > This Construct gives me an error during the creation in Postgresql > (saying there is no unique constraint matching given keys for referenced > table xxxx). The Primary Key in table 1 consists of two columns. In the > second table there is a foreign key refencing to the first table. > > A unique constraint doesn't make sense on table one. Am I missing > something here? Having typos in what you show us doesn't help. Inventory, Productnr being unique does not imply Productnr is unique. If Productnr in table 1 is unique, you should make a unique constraint on it. If it isn't unique you can't use a foreign key reference and you will need to write your own trigger functions to do whatever makes sense for your problem.