X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id AD8065E46D5 for ; Tue, 17 Aug 2004 06:52:32 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 94001-01 for ; Tue, 17 Aug 2004 09:52:32 +0000 (GMT) Received: from anchor-post-32.mail.demon.net (anchor-post-32.mail.demon.net [194.217.242.90]) by svr1.postgresql.org (Postfix) with ESMTP id 6513E5E46DB for ; Tue, 17 Aug 2004 06:52:30 -0300 (ADT) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-32.mail.demon.net with esmtp (Exim 3.35 #1) id 1Bx0dr-0001c4-0W; Tue, 17 Aug 2004 09:52:31 +0000 Received: from [192.168.1.17] (client17.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id 2B50618226; Tue, 17 Aug 2004 10:52:30 +0100 (BST) Message-ID: <4121D55D.1040105@archonet.com> Date: Tue, 17 Aug 2004 10:52:29 +0100 From: Richard Huxton User-Agent: Mozilla Thunderbird 0.7 (X11/20040615) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Markus Bertheau Cc: pgsql-sql@postgresql.org Subject: Re: multi column foreign key for implicitly unique columns References: <1092734724.2627.4.camel@dicaprio.akademie1.de> In-Reply-To: <1092734724.2627.4.camel@dicaprio.akademie1.de> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/158 X-Sequence-Number: 18655 Markus Bertheau wrote: > Hi, > > PostgreSQL doesn't allow the creation of a foreign key to a combination > of fields that has got no dedicated unique key but is unique nonetheless > because a subset of the combination of fields has a unique constraint. [snip example] > Is this on purpose? I think the foreign key should be allowed. Creating > an extra unique key only has a negative impact on performance, right? As you say, the uniqueness is guaranteed so there's no good reason why it couldn't be made to work. It's probably more of an implementation issue. Unique constraints are implemented with an index, so I'm guessing the FK code assumes there is an index there to check against. -- Richard Huxton Archonet Ltd