public inbox for [email protected]
help / color / mirror / Atom feedFrom: Steve Tucknott (TuSol) <[email protected]>
To: [email protected]
Subject: Re: Advice on foreign key and cascading delete design - postgresql 12.6
Date: Wed, 14 Apr 2021 07:50:45 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
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'?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
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]
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