Re: foreign key restrictions

<rafal(at)zorro(dot)isa-geek(dot)com> wrote in message
news:fed538acdecf7f90be655937817877c1(dot)squirrel(at)localhost(dot)(dot)(dot)
>> 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.
>

<unique column list> just means the column name list specified between the
parentheses of some UNIQUE or PRIMARY KEY constraint. There is no such thing
as a UNIQUE INDEX in SQL.

> 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
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>