Re: More ADD CONSTRAINT behaviour questions

> "Christopher Kings-Lynne" <chriskl(at)familyhealth(dot)com(dot)au> writes:
> > 6. A unique index is already defined over (b, a)
>
> > - As above. Technically a different index, but effect
> > as far as uniqueness is concerned is identical?
>
> This case *must not* be an error IMHO: it's perfectly reasonable to have
> indexes on both (a,b) and (b,a), and if the column pair happens to be
> unique, there's no reason why they shouldn't both be marked unique.
>
> Because of that, I'm not too excited about raising an error in any case
> except where you have an absolutely identical pre-existing index, ie,
> there's already a unique index on (a,b) --- doesn't matter much whether
> it's marked primary or not.
>
> For ADD PRIMARY KEY, there mustn't be any pre-existing primary index,
> of course. I can see promoting an extant matching unique index to
> primary status, though, rather than making another index.
>

Yea, I agree with Tom. Usually we let the person do whatever they want
except in cases that clearly make no sense or where we can improve it.

Good questions, though.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026