Re: multi column foreign key for implicitly unique columns

On Tue, 17 Aug 2004, Richard Huxton wrote:

> Markus Bertheau wrote:
> > Hi,
> >
> > PostgreSQL doesn't allow the creation of a foreign key to a combination
> > of fields that has got no dedicated unique key but is unique nonetheless
> > because a subset of the combination of fields has a unique constraint.
> [snip example]
> > Is this on purpose? I think the foreign key should be allowed. Creating
> > an extra unique key only has a negative impact on performance, right?
>
> As you say, the uniqueness is guaranteed so there's no good reason why
> it couldn't be made to work. It's probably more of an implementation
> issue. Unique constraints are implemented with an index, so I'm guessing

No, actually, it's that the SQL92 (at least) spec says explicitly that
there must be a unique constraint across all of the columns specified, not
merely across a subset.

"then the set of column names of that <reference column list> shall be
equal to the set of column names in the unique columns of a unique
constraint of the referenced table."