Re: multi column foreign key for implicitly unique columns

Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> No, actually, it's that the SQL92 (at least) spec says explicitly that
> there must be a unique constraint across all of the columns specified, not
> merely across a subset.

> "then the set of column names of that <reference column list> shall be
> equal to the set of column names in the unique columns of a unique
> constraint of the referenced table."

SQL99 says the same.  11.8 syntax rule 3a:
           a) If the <referenced table and columns> specifies a <reference             column list>, then the set of
<columnname>s contained             in that <reference column list> shall be equal to the             set of <column
name>scontained in the <unique column             list> of a unique constraint of the referenced table.
 

I think one reason for this is that otherwise it's not clear which
unique constraint the FK constraint depends on.  Consider
create table a (f1 int unique, f2 int unique);
create table b (f1 int, f2 int,        foreign key (f1,f2) references a(f1,f2));

How would you decide which constraint to make the FK depend on?
It'd be purely arbitrary.
        regards, tom lane