Re: Advice on foreign key and cascading delete design - postgresql 12.6

On Tue, 13 Apr 2021 16:48:48 +0100
"Steve Tucknott (TuSol)" <steve(at)tusol(dot)co(dot)uk> wrote:

> > Maybe I'm missing something, but aren't you just looking for theON
> > DELETE CASCADE option of foreign key constraints?
> > regards, tom lane
>
> Tom,
> Maybe it's me that's missing it.
>
> On my subordinate table I have two 'identification' fields - one
> containing a varchar for the table name and the other a value for the
> PK for that table. So if I add a 'note' on my notes table for my
> suppliers table record 1, I would have an entry on notes with
> ...'suppliers', 1, 'some note text'...
> .
> ..and on my supplier table a record with PK of 1. So supplier PK 1 has
> a note of 'some note text'.
>
> How do I set up the FK on the notes table? All I can see is the option
> to link on column names, so I can set up:
> ...CONSTRAINT notes_c1 FOREIGN KEY (foreignRecNo) REFERENCES supplier
> ...
>
> BUT that doesn't work as far as I can see, as I may have multiple
> foreignrecnos on notes with value 1, each of which is dependent on the
> foreigntablename as well - but I cannot see how to specify a literal in
> the FK constraint. What I think I need is something like:
> ...CONSTRAINT notes_c1 FOREIGN KEY (foreigntablename,foreignRecNo)
> REFERENCES supplier ('supplier',recno) ...

When you have 2 tables, 'main' & 'dependency', with a referential
integrity between them and you want to wipe all rows in dependency when
the RI is deleted from main, it should be like that :

main (
id int generated always as identity primary key,
tm text not null
)

dependency (
id int generated always as identity primary key,
td text not null,
ri_main int not null
REFERENCES public.main ON DELETE CASCADE
)

This way, when you have a row in 'dependency' that references another one
from 'main', if the 'main' row is deleted, the 'dependency' row is
automatically also deleted.

Read the doc to see what to add if you create both rows in the same
transaction.

Jean-Yves