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 B6119D1D27C for ; Fri, 13 Feb 2004 00:31:21 +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 68717-08 for ; Thu, 12 Feb 2004 20:31:19 -0400 (AST) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id D3C93D1B4DA for ; Thu, 12 Feb 2004 20:31:15 -0400 (AST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id A131335969; Thu, 12 Feb 2004 16:31:13 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 9FCF03595F; Thu, 12 Feb 2004 16:31:13 -0800 (PST) Date: Thu, 12 Feb 2004 16:31:13 -0800 (PST) From: Stephan Szabo To: Tuan Luu Cc: pgsql-admin@postgresql.org Subject: Re: Foreign Key Question In-Reply-To: <40273DD0.6030004@gmx.ch> Message-ID: <20040212162906.U99905@megazone.bigpanda.com> References: <40273DD0.6030004@gmx.ch> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/194 X-Sequence-Number: 12368 On Mon, 9 Feb 2004, 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? What are the semantics you are looking for? Foreign keys only can refer to unique things, because except for a few odd cases involving nulls and match partial, it's intended that one particular row is being referenced. If there are multiple rows in table1 with a particular productnr, how do you want a reference to that to act (esp in the case of delete or update to table 1).