'How to do two things on delete?

ALTER TABLE table_a
ADD CONSTRAINT fkey
        FOREIGN KEY (f_id) REFERENCES table_b(id)
            ON DELETE SET NULL;

This is a normal constraint, when a tuple is deleted from table_b, a corresponding tuple's column f_id in table_a will be set to null.

Here besides set f_id to null, I want to set f_id's old value to column f_old_id. Is it possible?

postgresql


Solution 1:[1]

Here is a trigger-based solution.

create or replace function fkey_tf() returns trigger language plpgsql as
$$
begin
 update table_a 
    set f_old_id = f_id, f_id = null
  where f_id = OLD.id;
  return null;
end;
$$;

create trigger fkey_t 
after delete on table_b
for each row execute procedure fkey_tf();

Please note that an index on table_a.f_id is needed in order to not sacrifice performance. This is a good practice for foreign keys anyway.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions