Re: foreign keys on multiple parent table

[Lorusso Domenico <domenico.l76@xxxxxxxxx>]

ehm.. I'm not sure I understood correctly :-D

in which way do you generate column?

Il giorno mer 21 giu 2023 alle ore 09:47 Dominique Devienne <ddevienne@xxxxxxxxx> ha scritto:

On Tue, Jun 20, 2023 at 10:47 PM Lorusso Domenico <domenico.l76@xxxxxxxxx> 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

--

Domenico L.

per stupire mezz'ora basta un libro di storia,
io cercai di imparare la Treccani a memoria... [F.d.A.]