Re: multi column foreign key for implicitly unique columns

Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)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 <column name>s contained
in that <reference column list> shall be equal to the
set of <column name>s contained 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