Re: Multicolumn foreign keys need useless unique indices?

On Sat, 2002-09-14 at 20:14, Tom Lane wrote:
> Hannu Krosing <hannu(at)tm(dot)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