CODEWITHSUNDEEP
sqlpostgresql
sebastianf182
sebastianf182

Reputation: 9978

Delete foreign key referenced table record when setting field to null

I am aware when you have a foreign key reference, you can do 'on delete cascade' and if you delete a parent, the child references are deleted as well.

Is there a way to delete the referenced records when the child field is set to null?

Place {
  id (PK)
  name
  id_address (FK)
}

Address {
   id (PK)
   name
   city
}

I would like the record on address to be deleted if I set place.id_address to null.

Would this be a good case for a trigger? I'd like to to this properly but I'd hate to handle that on the app layer.

Upvotes: 0

Views: 732

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246818

I think that the best solution for this is a trigger like this:

CREATE FUNCTION del_addr() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   DELETE FROM address WHERE address.id = OLD.address_id;
   RETURN OLD;
END;$$;

CREATE TRIGGER del_addr AFTER DELETE ON place
   FOR EACH ROW EXECUTE PROCEDURE del_addr();

Upvotes: 1

Related Questions