> 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.
