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.

regards, tom lane