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 C06655E46EC for ; Wed, 18 Aug 2004 13:27:52 -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 49518-04 for ; Wed, 18 Aug 2004 16:27:57 +0000 (GMT) Received: from smtp106.mail.sc5.yahoo.com (smtp106.mail.sc5.yahoo.com [66.163.169.226]) by svr1.postgresql.org (Postfix) with SMTP id A15DB5E46E6 for ; Wed, 18 Aug 2004 13:27:48 -0300 (ADT) Received: from unknown (HELO jupiter.black-lion.info) (janwieck@68.80.245.191 with login) by smtp106.mail.sc5.yahoo.com with SMTP; 18 Aug 2004 16:27:56 -0000 Received: from [172.21.8.18] (ismtp.afilias.com [216.217.55.254]) (authenticated bits=0) by jupiter.black-lion.info (8.12.10/8.12.9) with ESMTP id i7IGRqT7065050; Wed, 18 Aug 2004 12:27:53 -0400 (EDT) (envelope-from JanWieck@Yahoo.com) Message-ID: <4123837C.30408@Yahoo.com> Date: Wed, 18 Aug 2004 12:27:40 -0400 From: Jan Wieck User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.7.1) Gecko/20040707 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane Cc: Richard Huxton , Markus Bertheau , Josh Berkus , olly@lfix.co.uk, pgsql-sql@postgresql.org, Bruce Momjian Subject: Re: multi column foreign key for implicitly unique columns References: <1092734724.2627.4.camel@dicaprio.akademie1.de> <1092735576.28365.48.camel@linda> <1092736197.2627.12.camel@dicaprio.akademie1.de> <200408171945.15663.josh@agliodbs.com> <41235A90.1080300@Yahoo.com> <1092836961.2630.3.camel@dicaprio.akademie1.de> <41236277.5060903@Yahoo.com> <22808.1092842209@sss.pgh.pa.us> <41237DA2.9090405@archonet.com> <1535.1092845917@sss.pgh.pa.us> In-Reply-To: <1535.1092845917@sss.pgh.pa.us> 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=1.6 tagged_above=0.0 required=5.0 tests=RCVD_IN_NJABL_DUL, RCVD_IN_SORBS_DUL X-Spam-Level: * X-Archive-Number: 200408/202 X-Sequence-Number: 18699 On 8/18/2004 12:18 PM, Tom Lane wrote: > Richard Huxton writes: >> * Allow multiple unique constraints to share an index where one is a >> superset of the others' columns. > >> That way you can mark it unique without having the overhead of multiple >> indexes. > > That just moves the uncertain-dependency problem over one spot, ie, it's > the fabricated unique constraint that you can't pin down the > requirements for. If we allow for a unique index, that * it is NOT maintained (no index tuples in there) * depends on another index that has a subset of columns * if that subset-index is dropped, the index becomes maintained then the uncertainty is gone. At the time someone drops the other constraint or unique index, the data is unique with respect to the superset of columns. So building the unique index data at that time will succeed. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #