Deleting linked data across tables in PostgreSQL

Question

I'm looking for the best way to handle deleting linked data across three PostgreSQL tables (technically more, but it's the same idea).

The three tables are agency, address, and agency_address. An agency can have multiple addresses so the agency_address is just a link table with two columns, agency_id and address_id (defined as their respective foreign keys)

I want to set it up so that when an agency is deleted it will remove the link table row in agency_address and the related address row automagically. (So if an agency is deleted, so are all its addresses)

I can get it to clear the link table, but not sure how to get to the address table.

(Also address is a generic model and will be referenced by others, like a 'site' which will have their own link tables.)

Answer 1

Use foreign keys with ON DELETE CASCADE in the table definition.

ALTER TABLE agency_address
  ADD CONSTRAINT agency_address_agency_fkey FOREIGN KEY (agency_id)
      REFERENCES agency (agency_id) ON DELETE CASCADE;

It seems uncertain that you should delete addresses automatically, too.

If so, your data model is wrong and addresses should depend on agencies directly, with a foreign key constraint similar to the one above, no n:m linking table necessary.

---

Edit after more info:

So addresses can be linked to agencies or sites, but never to both at the same time.

The model could work as you have it, but you would have to make sure somehow that an address linked to an agency isn't linked to a site, too.

The foreign key constraint between address and agency_address points in the "wrong" direction, so you cannot simply add another ON DELETE CASCADE. You could implement it with an additional foreign key, but that's tricky. This approach per trigger is much simpler:

CREATE OR REPLACE FUNCTION trg_agency_address_delaft()
  RETURNS trigger AS
$BODY$
BEGIN

DELETE FROM address
WHERE address_id = OLD.address_id;

END;
$BODY$
  LANGUAGE plpgsql;


CREATE TRIGGER delaft
  AFTER DELETE ON agency_address
  FOR EACH ROW EXECUTE PROCEDURE trg_agency_address_delaft();

More about trigger functions and triggers in the manual.