> Gregory Stark <stark@xxxxxxxxxxxxxxxx> 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
