Re: Add primary key/unique constraint using prefix columns of an index

On Tue, 2012-05-22 at 10:24 -0700, Jeff Janes wrote:
> Now that there are index only scans, there is a use case for having a
> composite index which has the primary key or a unique key as the
> prefix column(s) but with extra columns after that. Currently you
> would also need another index with exactly the primary/unique key,
> which seems like a waste of storage and maintenance.
>
> Should there be a way to declare a "unique" index with the unique
> property applying to a prefix of the indexed columns/expression? And
> having that, a way to turn that prefix into a primary key constraint?
>
> Of course this is easier said then done, but is there some reason for
> it not to be a to-do item?

Technically, this can be done today using exclusion constraints if you
define an indexable operator that always returns true.

A similar idea, which I brought up during 9.0 development, is that it
might be useful to have one index that can enforce two UNIQUE
constraints. For instance, an index on (a,b,c) could be used to enforce
UNIQUE(a,b) and UNIQUE(a,c) using the exclusion constraint mechanism.

I didn't offer a lot of convincing evidence of practical value, so it
was left out. But it might be worth a second look.

Either way, it seems like the deviations from normal UNIQUE would be
better expressed in terms of an exclusion constraint, which offers a
little more room in the language.

Regards,
Jeff Davis