On Mon, Sep 26, 2022 at 9:59 AM Wolfgang Walther <walt...@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
