<[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] >> Gregory Stark <[EMAIL PROTECTED]> 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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
