Re: Advice on foreign key and cascading delete design - postgresql 12.6 - Mailing list pgsql-novice
| From | Steve Tucknott (TuSol) |
|---|---|
| Subject | Re: Advice on foreign key and cascading delete design - postgresql 12.6 |
| Date | |
| Msg-id | f0e99cfb734baba7a7f41397f27e222cfe385ee8.camel@tusol.co.uk Whole thread Raw |
| In response to | Re: Advice on foreign key and cascading delete design - postgresql 12.6 (Bzzzz <lazyvirus@gmx.com>) |
| Responses |
Re: Advice on foreign key and cascading delete design - postgresql 12.6
(Bzzzz <lazyvirus@gmx.com>)
|
| List | pgsql-novice |
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 nullREFERENCES public.main ON DELETE CASCADE)This way, when you have a row in 'dependency' that references another onefrom 'main', if the 'main' row is deleted, the 'dependency' row isautomatically also deleted.
Jean-Yves
I 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'
.....etc
But 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
pgsql-novice by date: