Re: Multicolumn foreign keys need useless unique indices?

> Rod Taylor <rbt@rbt.ca> writes:
> > On Fri, 2002-09-13 at 04:27, Christopher Kings-Lynne wrote:
> >> Hmmm - thinking about it, I don't see why postgres would need the entire
> >> thing to be unique...can't think of a reason at the moment.  Stephen?
>
> > If it's not all unique, you cannot be guaranteed there is a single row
> > with those values in the referenced table.
>
> Right.  The single-column unique constraint guarantees at most one
> match, but it isn't helpful for checking if there's at least one match.
> The spec obviously intends that the index supporting the unique
> constraint be useful for verifying the existence of a match.
>
> I read this in SQL92:
>
>             a) If the <referenced table and columns> specifies a <reference
>               column list>, then the set of column names of that <refer-
>               ence column list> shall be equal to the set of column names
>               in the unique columns of a unique constraint of the refer-
>               enced table.
>
> It says "equal to", not "superset of".  So we are behaving per spec.

That's what I used when doing it.  It possibly is a stronger than
necessary statement but I assumed at the time they had some reason for
wanting to define it that way.