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

On Wed, 14 Apr 2021 07:50:45 +0100
"Steve Tucknott (TuSol)" <steve(at)tusol(dot)co(dot)uk> wrote:

> On Tue, 2021-04-13 at 18:08 +0200, Bzzzz wrote:
> > 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.
>
> Jean-YvesI think I understand ON DELETE CASCADE and use it on other
> tables,but I still don't see how that works with the 'main',
> 'dependency' structure (and my notes). That seems to me to only work
> if the 'dependency' is only used by 'main', but what if I have other
> main tables called 'main2', 'main3', 'main4'...that also have records
> on 'dependency'?

Oh I missed that, ok, in this case, you need to do 2 things :

* add "join" tables with only 2 columns :
* a RI to 'main1' (or main2, etc, one table per father)
* a RI to 'dependency'
with ON DELETE CASCADE
|
create table schema.main1_dependency(
int not null references schema.main1(id) on delete cascade
int not null references schema.dependency(id)
)

* a trigger for each of these tables that'll delete the concerned
'dependency' rows when a row is deleted from one of these tables
(of course, that means you _must_ embed all this into a transaction.)
|
Note that, if it is what you're looking for, you should be able to…
cascade the deletion - ie: you delete a row from 'main5', which, by
cascade will delete all pointing rows from a 'main5_dependency' and the
trigger will, at last, delete pointing rows from 'dependency'.

Looks like that : main1 <--- main1_dependency ---> dependency
main2 <--- main2_dependency ---> dependency
…

Creation:
[if row doesn't exist into 'main1', transaction may starts here]
* create row into 'main1' (ie: 4798)
[if row does exist into 'main1', transaction starts here]
* create row into 'dependency' (ie: 15025)
* create row into 'main1_dependency' to materialize the link
(ie: ri_main1=2798 & ri_dependency=15025)
[commit|rollback]

Caution: if all of these steps are in only one transaction, you'll have
to defer to avoid errors !

Deletion:
delete from 'main1' where id=4798
CASCADE, deleting from 'main1_dependency' where ri_main1=4798
which launch trigger that will delete all rows from 'dependency'
when main1_dependency(ri_main1)=4798

> My structure handles that as the 'dependency' table
> also carries the table name of the 'main' - so I could have rows on
> dependency of:1,'some text for main', 1,'main'2,'some text for
> main2',1,'main2'3,'some text for main3',1,'main3'.....etcBut the the FK
> back to main[123] is composed of two parts - the ID and the table
> name.

Bad design, because that means you must have a RI column for each and
every 'mainN' table into 'dependency' - will works if N < 10, but
will start to be a mess when N > 20, unusable if N ≥ 1000.

> I feel that I'm being really stupid here and not understanding what I'm
> being told! Apologies if I haven't grasped what you're telling me.

No no, my bad, I missed multiple 'mainN' tables.

> The only answer I can see is to have a column on every table that may
> carry a note, document, address etc that is its table name. But that
> seems wrong somehow.
> Regards,Steve

Jean-Yves