Message-ID: <4123969A.4020509@archonet.com>
Date: Wed, 18 Aug 2004 18:49:14 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 0.7 (X11/20040615)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Jan Wieck <JanWieck@Yahoo.com>, Markus Bertheau <twanger@bluetwanger.de>,
	Josh Berkus <josh@agliodbs.com>, olly@lfix.co.uk,
	pgsql-sql@postgresql.org, Bruce Momjian <pgman@candle.pha.pa.us>
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>
	<4123837C.30408@Yahoo.com> <1803.1092847586@sss.pgh.pa.us>
In-Reply-To: <1803.1092847586@sss.pgh.pa.us>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Tom Lane wrote:
> Jan Wieck <JanWieck@Yahoo.com> writes:
> 
>>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.
> 
> 
> My goodness this is getting ugly.  The notion of having to invoke an
> index build as a side-effect of a DROP sounds like a recipe for trouble.

I'm not sure it needs to be as clever as Jan suggested (but then I'm not 
as clever as Jan :-). I'd have thought a reference that forces you to 
use DROP...CASCADE would be enough. In those cases where you're dropping 
a whole table, presumably that's already implied.

-- 
   Richard Huxton
   Archonet Ltd