On Sat, 2002-09-14 at 20:14, Tom Lane wrote:
> Hannu Krosing <hannu@tm.ee> writes:
> > If we have unique index on t.i and we define additional unique
> > constraint on (t.i, t.j), then we don't need the extra unique index to
> > be created - the index on t.i is enough to quarantee the uniqueness of
> > (t.i,t.j) or any set of columns that includes t.i.
>
> You missed the point: we are concerned about existence of a row, not only
> uniqueness.
Maybe I'm missing something, but I'll reiterate my two points
1) to check for existance of a referenced tuple for a foreigh key we
have to:
* lookup the row in index
and
* check if the row is live in the relation
so the index will help us equally for both cases, as it will point to N
entries of which only one can be alive at a time and which all have to
be checked.
It will be only marginally more work to check if the only live entry
does match the non-index columns.
And I think that my other point holds as well - there is no need for
extra unique index on (redundant) unique constraint that is put over a
superset of columns covered by _another_ unique constraint.
There will probably be additional work if we want to drop the original
constraint, but this is a separate issue.
---------------
Hannu