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

On Wed, 2021-04-14 at 14:27 +0200, Bzzzz wrote:
> 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.

Thanks Jean-Yves, that makes more sense - although I do find
'normalised' structures slightly frustrating even though I can see that
the structure works.
You say that it's bad design having the foreigntablename on the
dependency as it's not practical if the number of dependent tables is
large, but why is it any better then having many 'intermediate main-to-
dependency' tables?
I think I've gone full circle in that I currently control the orphans
via hand cranked code - but I hadn't functionalised it. It seems that
another option is to make the tidy up generic by using the information
schemas in a function that purges all records that carry a
'foreigntablename' and 'foreignrecno' for the main table.

Thanks again for the time and effort you've put into explaining this
for me.

Regards,
Steve