From: | "Steve Tucknott (TuSol)" <steve(at)tusol(dot)co(dot)uk> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | PostGreSQL <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Advice on foreign key and cascading delete design - postgresql 12.6 |
Date: | 2021-04-13 15:48:48 |
Message-ID: | be3406b4935379ea0523365d9954b91ee349b93f.camel@tusol.co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
> 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) ...
Does any of that make sense?
From | Date | Subject | |
---|---|---|---|
Next Message | Bzzzz | 2021-04-13 16:08:41 | Re: Advice on foreign key and cascading delete design - postgresql 12.6 |
Previous Message | Tom Lane | 2021-04-13 14:47:18 | Re: Advice on foreign key and cascading delete design - postgresql 12.6 |