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

Whoops, it should of course read :

REFERENCES public.main(id) ON DELETE CASCADE

my bad, sorry.

Jean-Yves