Date: Wed, 14 Apr 2021 14:27:19 +0200
From: Bzzzz <lazyvirus@gmx.com>
To: pgsql-novice@lists.postgresql.org
Cc: steve@tusol.co.uk
Subject: Re: Advice on foreign key and cascading delete design - postgresql
 12.6
Message-ID: <20210414142719.181ffd62@msi.defcon1.lan>
In-Reply-To: <f0e99cfb734baba7a7f41397f27e222cfe385ee8.camel@tusol.co.uk>
References: <c1cfdf4054e8480d28fbf7fb2e15912d3996ee69.camel@tusol.co.uk>
	<3190280.1618325238@sss.pgh.pa.us>
	<be3406b4935379ea0523365d9954b91ee349b93f.camel@tusol.co.uk>
	<20210413180841.3ba6e741@msi.defcon1.lan>
	<f0e99cfb734baba7a7f41397f27e222cfe385ee8.camel@tusol.co.uk>
Organization: Anyone, anywhere but in banana demokratik republik of france
 or UERSS.
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/20210414142719.181ffd62%40msi.defcon1.lan>
Precedence: bulk

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
> > )
> >=20
> >=20
> > dependency (
> > id      int generated always as identity        primary key,
> > td      text                                    not null,
> > ri_main int                                     not null
> >         REFERENCES public.main ON DELETE CASCADE
> > )
> >=20
> >=20
> > 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.
>=20
> 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=E2=80=
=A6
  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
			=E2=80=A6

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=3D2798 & ri_dependency=3D15025)
[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=3D4798
    CASCADE, deleting from 'main1_dependency' where ri_main1=3D4798
	which launch trigger that will delete all rows from 'dependency'
        when main1_dependency(ri_main1)=3D4798

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

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 =E2=89=A5 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