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

"Steve Tucknott (TuSol)" <steve@tusol.co.uk> writes:
> I have a few tables that are subservient to multiple other tables
> (notes, addresses, attached documents etc).
> These tables carry the 'owning' table name and its record's PK.

> I can manually keep these tables tidy and avoid orphans, but is there a
> way within postgresql to reference these tables on a FK constraint to
> automatically delete the subservient recs when the parent is deleted?

Maybe I'm missing something, but aren't you just looking for the
ON DELETE CASCADE option of foreign key constraints?

            regards, tom lane

Maybe I'm missing something, but aren't you just looking for the

ON DELETE CASCADE option of foreign key constraints?

			regards, tom lane

On Tue, 13 Apr 2021 16:48:48 +0100
"Steve Tucknott (TuSol)" <steve@tusol.co.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) ...

When you have 2 tables, 'main' & 'dependency', with a referential
integrity between them and you want to wipe all rows in dependency when
the RI is deleted from main, it should be like that :

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.

Read the doc to see what to add if you create both rows in the same
transaction.

Jean-Yves

On Tue, 13 Apr 2021 16:48:48 +0100
"Steve Tucknott (TuSol)" <steve@tusol.co.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

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.

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

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.

On Thu, 15 Apr 2021 08:07:20 +0100
"Steve Tucknott (TuSol)" <steve@tusol.co.uk> wrote:

[2nd sent, the first one wasn't on the ML]

> 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?

Because of… normalized structure ;-p)
Using it, there is no repetition of any kind.

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

You just read old_table and write new_table with the columns you need
(transforming 'foreigntablename' and 'foreignrecno' references into
foreign keys references in this process), write the wanted functions and
triggers, test them thoroughly in a test DB, triple-check everything's
ok, delete the original table and finally, rename the new_table to the
old name and re-check the whole shebang.

Or you can make a text dump of old_table, modify it with sed and go on
with the rest of the process.

I would do all of this in a test DB then, when everything is checked,
make a dump of it, manually edit this dump to only keep what is needed
and apply it to the regular DB.
This would avoid downtime and make (almost) inevitable blunders
acceptable.

HINT: write down the whole procedure sequentially on a large sheet of
      paper with notes where needed, also write down your functions and
      triggers, write down the test procedures, triple-check everything's
      consistent and proceed step by step with no stress.

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

You're welcome.

Jean-Yves