On Mon, Sep 26, 2022 at 2:28 AM Wolfgang Walther <walt...@technowledgy.de> wrote: > > James Coleman: > > If we have a declared constraint on x,y where x is unique based on an > > index including on x I do not think we should have that fk constraint > > work differently than a constraint on x,y where there is a unique > > index on x,y. That would seem to be incredibly confusing behavior > > (even if it would be useful for some specific use case). > > I don't think it's behaving differently from how it does now. See below. > But I can see how that could be confusing. Maybe it's just about > describing the feature in a better way than I did so far. Or maybe it > needs a different syntax. > > Anyway, I don't think it's just a specific use case. In every use case I > had for $subject so far, the immediate next step was to write some > triggers to fetch those derived values from the referenced table. > > Ultimately it's a question of efficiency: We can achieve the same thing > in two ways today: > - We can either **not** add the additional column (members.tenant, > bar.ftype in my examples) to the referencing table at all, and add > constraint triggers that do all those checks instead. This adds > complexity to write the triggers and more complicated RLS policies etc, > and also is potentially slower when executing those more complicated > queries. > - Or we can add the additional column, but also add an additional unique > index on the referenced table, and then make it part of the FK. This > removes some of the constraint triggers and makes RLS policies simpler > and likely faster to execute queries. It comes at a cost of additional > cost of storage, though - and this is something that $subject tries to > address. > > Still, even when $subject is allowed, in practice we need some of the > triggers to fetch those dependent values. Considering that the current > FK triggers already do the same kind of queries at the same times, it'd > be more efficient to have those FK queries fetch those dependent values. > > >> But this could also be a CHECK constraint to allow FKs only to a subset > >> of rows in the target table: > > > > Are you suggesting a check constraint that queries another table? > > No. I was talking about the CHECK constraint in my example in the next > paragraph of that mail. The CHECK constraint on bar.ftype is a regular > CHECK constraint, but because of how ftype is updated automatically, it > effectively behaves like some kind of additional constraint on the FK > itself.
Ah, OK. > > This "derive the value automatically" is not what foreign key > > constraints do right now at all, right? And if fact it's contradictory > > to existing behavior, no? > > I don't think it's contradicting. Maybe a better way to put my idea is this: > > For a foreign key to a superset of unique columns, the already-unique > columns should behave according to the specified ON UPDATE clause. > However, the extra columns should always behave as they were ON UPDATE > CASCADE. And additionally, they should behave similar to something like > ON INSERT CASCADE. Although that INSERT is about the referencing table, > not the referenced table, so the analogy isn't 100%. > > I guess this would also be a more direct answer to Tom's earlier > question about what to expect in the ON UPDATE scenario. 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). James Coleman
