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