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.

regards, tom lane