Re: foreign keys on multiple parent table

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(at)gmail(dot)com> ha scritto:

> 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
>

--
Domenico L.

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