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

On Wed, 14 Apr 2021 07:50:45 +0100
"Steve Tucknott (TuSol)" <steve@tusol.co.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