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.

regards, tom lane