Re: foreign key restrictions

> Gregory Stark <stark(at)enterprisedb(dot)com> writes:
>> I'm not sure if there's a fundamental reason why there has to be an
>> index that
>> exactly matches the foreign key or not -- offhand I can't think of one.
>
> The reason why is that the SQL spec says so:
>
> 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. Let
> referenced columns be the column or columns identified by
> that <reference column list> and let referenced column be
> one
> such column. Each referenced column shall identify a column
> of the referenced table and the same column shall not be
> identified more than once.
>
> I'm not entirely sure, but I think the restrictive definition might be
> necessary with some of the more complex options for foreign keys, such
> as MATCH PARTIAL.

I must admit, the standard is not very easy reading for me; what exactly
does the standarad mean by "<unique column list>":
1. is that a requirement for mathematical properties of that list, or
2. is that a requirement for explicit SQL UNIQUE INDEX existing over the
entire list.

Since <column list> is a <unique column list> whenever a subset of <column
list> is a <unique column list>, then if interpretation nr.1 of the
standard is OK, there is no real requirement to install (and require to
install) an additional unique constraint on the target <column list>.

-R