public inbox for [email protected]  

help / color / mirror / Atom feed

From: Bzzzz <[email protected]>
To: [email protected]
Cc: [email protected]
Subject: Re: Advice on foreign key and cascading delete design - postgresql 12.6
Date: Tue, 13 Apr 2021 18:08:41 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>

On Tue, 13 Apr 2021 16:48:48 +0100
"Steve Tucknott (TuSol)" <[email protected]> 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

view thread (9+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Advice on foreign key and cascading delete design - postgresql 12.6
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox