Re: Allow foreign keys to reference a superset of unique columns

> So the broader point I'm trying to make is that, as I understand it,
> indexes backing foreign key constraints is an implementation detail.
> The SQL standard details the behavior of foreign key constraints
> regardless of implementation details like a backing index. That means
> that the behavior of two column foreign key constraints is defined in
> a single way whether or not there's a backing index at all or whether
> such a backing index, if present, contains one or two columns.

> I understand that for the use case you're describing this isn't the
> absolute most efficient way to implement the desired data semantics.
> But it would be incredibly confusing (and, I think, a violation of the
> SQL standard) to have one foreign key constraint work in a different
> way from another such constraint when both are indistinguishable at
> the constraint level (the backing index isn't an attribute of the
> constraint; it's merely an implementation detail).

It appears to me that the unique index backing a foreign key constraint
*isn't*
an implementation detail in PostgreSQL; rather, the *unique constraint* is
the
implementation detail. The reason I say this is because it's possible to
create
a foreign key constraint where the uniqueness of referenced columns are
guaranteed by only a unique index and where no such unique constraint
exists.

Specifically, this line in the documentation:

The referenced columns must be the columns of a non-deferrable unique or
primary key constraint in the referenced table.

Isn't true. In practice, the referenced columns must be the columns of a
valid,
nondeferrable, nonfunctional, nonpartial, unique index. Whether or not a
unique
constraint exists is immaterial to whether or not postgres will let you
define
such a foreign key constraint.