Skip site navigation (1) Skip section navigation (2)

Re: multi column foreign key for implicitly unique columns

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Jan Wieck <JanWieck(at)Yahoo(dot)com>
Cc: Richard Huxton <dev(at)archonet(dot)com>,Markus Bertheau <twanger(at)bluetwanger(dot)de>,Josh Berkus <josh(at)agliodbs(dot)com>, olly(at)lfix(dot)co(dot)uk,pgsql-sql(at)postgresql(dot)org, Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
Subject: Re: multi column foreign key for implicitly unique columns
Date: 2004-08-18 16:46:26
Message-ID: 1803.1092847586@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-sql
Jan Wieck <JanWieck(at)Yahoo(dot)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.
(Sample problem: what you're actually trying to do is drop the entire
table ... but because the subset-index happens to get visited first,
you go off and build the superset-index before you let the DROP finish.
User will be unhappy, if table is large.  Or try this one: the superset-
index build actually fails because you've already dropped something it
depends on.  This seems quite possible in cases involving cascading from
a drop of an individual column or datatype, for instance.)

I'd like to see more than one person needing it, before we go to that
kind of trouble to do something that's not in the spec.

			regards, tom lane

In response to

Responses

pgsql-sql by date

Next:From: Josh BerkusDate: 2004-08-18 16:56:14
Subject: Re: SQL Challenge: Arbitrary Cross-tab
Previous:From: Rod TaylorDate: 2004-08-18 16:46:14
Subject: Re: multi column foreign key for implicitly unique columns

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group