Re: Multicolumn foreign keys need useless unique indices?

> Rod Taylor <rbt(at)rbt(dot)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.