Re: foreign keys on multiple parent table

On Tue, Jun 20, 2023 at 10:47 PM Lorusso Domenico <domenico(dot)l76(at)gmail(dot)com>
wrote:

> Could work, but is there a way to set a reference key over the uuid of all
> the tables?
>

Yes, it's possible. We do it. There are several ways to emulate what I call
"polymorphic" FKs.

All approaches have pros and cons, the one we use relies on CHECK
constraints and virtual/generated columns.
It assumes all mutually exclusive FKs are of the same type. For ON DELETE
CASCADE FKs, you have the primary
"fk" concrete column, plus a secondary "fk$t" type column, telling you
which FK is active, then N "fk$N" virtual columns
whose expression automatically turn them ON (="fk") or OFF (is NULL) based
on "fk$t"'s value. A CHECK constraint
ensures only 0 or 1 "fk$N" column is ON, depending on "fk"'s NULLablity.
For ON DELETE SET NULL, you need to
reverse the concrete and virtual columns, so the constraint can *write* the
"fk$N" columns, with more CHECK constraints.

The technique works because FKs on virtual column works fine. As with all
FKs with ON DELETE CASCADE, you want
to index your FKs to avoid full scans. With partial indexes (since the FKs
are mutually exclusive and full of NULLs), the
storage overhead from multiplicating (virtual) columns and indexes can be
limited (i.e. not as bad as N times the single index).
Of course, this is tricky to pull-off correctly w/o automatic schema
generation from a logic model. We have dozens of these PFKs,
of various cardinality, maintaining those manually would be a nightmare.
And when the polymorphism is too much,
we give up on referential integrity on a case by case basis, to avoid
bloating the tables and schema. It's a tradeof, as always.

I'm sure I didn't invent this technique. But it sure isn't very common and
it has been our "secret sauce" for a few years.
On Oracle first, now on PostgreSQL. A Dalibo consultant once told me I
should present it at a PGCon conference :).

Good luck if you try that. FWIW, --DD