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

On Mon, Sep 26, 2022 at 2:28 AM Wolfgang Walther
<walther(at)technowledgy(dot)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