Re: More ADD CONSTRAINT behaviour questions

> "Christopher Kings-Lynne" <chriskl@familyhealth.com.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@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026