Re: Allow foreign keys to reference a superset of unique columns - Mailing list pgsql-hackers

On Mon, Sep 26, 2022 at 9:59 AM Wolfgang Walther
<walther@technowledgy.de> wrote:
>
> 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?

I think that's not true: the SQL standard doesn't have the option of
"this foreign key is backed by this unique constraint", does it? So in
either case I believe we would be at minimum implementing an extension
to the standard (and as I argued already I think it would actually be
contradictory to the standard).

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

Yeah, I think that would make more sense if one were proposing an
addition to the SQL standard (or an explicit extension to it that
Postgres would support indepently of the standard).

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

Sounds good to me!

James Coleman