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
