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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-sql by date

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