Re: Advice on foreign key and cascading delete design - postgresql 12.6

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