Thread: foreign keys on multiple parent table

.

From programming point of view and also to reduce the number of objects in DB could be convinient create just an audit table with a structure like:

• auditi id
• reference_uuid (the key of the main table)
• table_name
• list of audit data

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

For existing solution, check out https://github.com/2ndQuadrant/audit-trigger

Regarding fk constraints, a single fk constraint can only reference the primary key of a single table.

But, if you want to be serious about audit logs, then you need to keep logs of deletions too, and for those, foreign key constraints would not work anyway. 

You may also want to consider bulk insert speed. Foreign key constraint checking can reduce speed.

  Laszlo

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