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

James Coleman:
> 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).

Ah, thanks, I understand better now.

The two would only be indistinguishable at the constraint level, if
$subject was implemented by allowing to create unique constraints on a
superset of unique columns, backed by a different index (the suggestion
we both made independently). But if it was possible to reference a
superset of unique columns, where there was only a unique constraint put
on a subset of the referenced columns (the idea originally introduced in
this thread), then there would be a difference, right?

That's if it was only the backing index that is not part of the SQL
standard, and not also the fact that a foreign key should reference a
primary key or unique constraint?

Anyway, I can see very well how that would be quite confusing overall.
It would probably be wiser to allow something roughly like this (if at
all, of course):

CREATE TABLE bar (
b INT PRIMARY KEY,
f INT,
ftype foo_type GENERATED ALWAYS AS REFERENCE TO foo.type,
FOREIGN KEY (f, ftype) REFERENCES foo (f, type)
);

It likely wouldn't work exactly like that, but given a foreign key to
foo, the GENERATED clause could be used to fetch the value through the
same triggers that form that FK for efficiency. My main point for now
is: With a much more explicit syntax anything near that, this would
certainly be an entirely different feature than $subject **and** it
would be possible to implement on top of $subject. If at all.

So no need for me to distract this thread from $subject anymore. I think
the idea of allowing to create unique constraints on a superset of the
columns of an already existing unique index is a good one, so let's
discuss this further.

Best

Wolfgang