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
columnnames of that <refer-             ence column list> shall be equal to the set of column names             in the
uniquecolumns of a unique constraint of the refer-             enced table.
 

It says "equal to", not "superset of".  So we are behaving per spec.
        regards, tom lane