foreign keys on multiple parent table

On Tuesday, June 20, 2023, 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?

A foreign key in PostgreSQL is between two, and only two, tables. The PK
side of which must be uniquely constrained.

You can write custom triggers if you need something other than this.

David J.

.

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

Thank you Les for the link, it's a very good example, unfortunately my need
is more applicative (we need to store user of application, not the on pg,
proces who start etc), but for sure I can take advantage of it.

Il giorno mar 20 giu 2023 alle ore 23:01 Les <nagylzs@gmail.com> ha scritto:

.

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

--
Domenico L.

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

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@gmail.com> ha scritto:

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

--
Domenico L.

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