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
