Date: Tue, 13 Apr 2021 18:11:13 +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: <20210413181113.77f7ba46@msi.defcon1.lan>
In-Reply-To: <be3406b4935379ea0523365d9954b91ee349b93f.camel@tusol.co.uk>
References: <c1cfdf4054e8480d28fbf7fb2e15912d3996ee69.camel@tusol.co.uk>
	<3190280.1618325238@sss.pgh.pa.us>
	<be3406b4935379ea0523365d9954b91ee349b93f.camel@tusol.co.uk>
Organization: Anyone, anywhere but in banana demokratik republik of france
 or UERSS.
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/20210413181113.77f7ba46%40msi.defcon1.lan>
Precedence: bulk

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