Stephan Szabo <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
