On Thursday, June 13, 2024, David Barbour <
dbarbour@istation.com> wrote:
When a record in import_job is deleted, the child records (file records) in import_file need to be deleted first.
The constraint in both Oracle and Postgres is similar (Postgres version):
ALTER TABLE IF EXISTS idev.import_file
ADD CONSTRAINT fk1_import_file FOREIGN KEY (import_job_oid)
REFERENCES idev.import_job (oid) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE CASCADE;
This, by itself, should work. If it isn’t, please provide a self-contained test case demonstrating that fact so it can be investigated/explained.
What version are you running?
There aren't any delete triggers for either table. Any idea why this isn't working? Does cascade function differently in Postgres?
Nope (to both)
I've also tried creating a before trigger on import_job,
Why?
but can't seem to get the right syntax for taking the oid from the psql delete picked up by the trigger.
Your broken attempt to do this is likely what is causing the error.
Here is one of my (many) attempts (have tried describing, setting, using new.oid, old.oid, a bunch of stuff) and can't get this right either:
Delete only populates OLD.
David J.