Re: Advice on foreign key and cascading delete design - postgresql 12.6 - Mailing list pgsql-novice

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.