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