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

From: "Steve Tucknott (TuSol)" <[email-masked]>
Date: Tue, 13 Apr 2021 13:40:51 +0100

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?

OR have I got the structure all wrong anyway and is there a cleaner way
of holding, say notes, for records on a whole range of tables?

Thanks all.

---

From: Tom Lane <[email-masked]>
Date: Tue, 13 Apr 2021 10:47:18 -0400

> 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

---

From: "Steve Tucknott (TuSol)" <[email-masked]>
Date: Tue, 13 Apr 2021 16:48:48 +0100

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

Does any of that make sense?

---

From: Bzzzz <[email-masked]>
Date: Tue, 13 Apr 2021 18:08:41 +0200

On Tue, 13 Apr 2021 16:48:48 +0100

> 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

---

From: Bzzzz <[email-masked]>
Date: Tue, 13 Apr 2021 18:11:13 +0200

> REFERENCES supplier ('supplier',recno) ...

Whoops, it should of course read :

	REFERENCES public.main(id) ON DELETE CASCADE

my bad, sorry.

Jean-Yves

---

From: "Steve Tucknott (TuSol)" <[email-masked]>
Date: Wed, 14 Apr 2021 07:50:45 +0100

> 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'?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. 
As a real-life example - I have this data on a working 'notes' and
'document' tables. These are both subordinate tables - subordinate to
the tables named in the data:dev_gyb=#  select
recno,notesforeigntablename,notesforeignrecno from notes order by
3; recno | notesforeigntablename | notesforeignrecno -------+--------
---------------+-------------------    21 |
gybcust               |                 1    29 |
gyblocation           |                 1    13 |
globalnotes           |                 1    15 |
globalnotes           |                 1    14 |
globalnotes           |                 1     8 |
globalnotes           |                 1     7 |
globalnotes           |                 1    28 |
gybgarden             |                 5    30 |
gybplot               |                19    22 |
calendar              |                25    26 |
calendar              |                28    25 |
calendar              |                40
...as you can see, there are records on notes for tables gybcust,
gyblocation and globalnotes all with a PK of 1... So '1' cannot be used
in isolation.Similarly the 'document' table (carries links to attached
documents such as images, pdfs,videos etc) has:dev_gyb=#  select
recno,docforeigntablename,docforeignrecno from document order by
3; recno | docforeigntablename | docforeignrecno -------+------------
---------+-----------------     1 |
gybgarden           |               1   211 |
gybcust             |               1    26 |
gybplant            |               1     6 |
gyblocation         |               1     7 |
gybplot             |               1     2 |
gybgarden           |               2    27 |
gybplant            |               2     8 |
gybplot             |               2     3 |
gybgarden           |               3    29 |
gybplant            |               3     9 |
gybplot             |               3    10 |
gybplot             |               4     4 |
gybgarden           |               4    30 |
gybplant            |               4....where there are a whole raft
of records that point back to owning tables with PKs of 1,2,3,4 - but
you can't tell which table without the table name.
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.
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

---

From: Bzzzz <[email-masked]>
Date: Wed, 14 Apr 2021 14:27:19 +0200

On Wed, 14 Apr 2021 07:50:45 +0100
"Steve Tucknott (TuSol)" <[email-masked]> wrote:

> 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

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.

> being told! Apologies if I haven't grasped what you're telling me.

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

Jean-Yves

---

From: "Steve Tucknott (TuSol)" <[email-masked]>
Date: Thu, 15 Apr 2021 08:07:20 +0100

> 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

---

From: Bzzzz <[email-masked]>
Date: Thu, 15 Apr 2021 15:15:23 +0200

On Thu, 15 Apr 2021 08:07:20 +0100
"Steve Tucknott (TuSol)" <[email-masked]> wrote:

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

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

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

You're welcome.

Jean-Yves