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 < ddevie...@gmail.com> ha scritto: > On Tue, Jun 20, 2023 at 10:47 PM Lorusso Domenico <domenico....@gmail.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.]
