Maybe I'm missing something, but aren't you just looking for the
ON 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?