2

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.)

jshrc
  • 187
  • 8
  • Your description is contradictory: if addresses `will be referenced by others`, then you should not cascade a delete on agency to all linked addresses. They can be linked to others (agencies?). – Erwin Brandstetter Jan 06 '12 at 17:24
  • Sorry, the address table (object) itself will be used by other objects. Like a site would use an address stored in the address table, but it would be unique. We're not allowing two different things, whether it's two+ agencies, a site and an agency, or two+ sites, use the same address. – jshrc Jan 06 '12 at 19:21

1 Answers1

0

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.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • The idea is to have a single address table to store all of our addresses in for different resources. (eg, addresses for agencies and sites, both of which could have multiples) This lets us have a single model/object for our addresses that can be used by our agency and site objects. The address object doesn't care who/what is using it, it just manages stuff in the address table. The agency object sees/knows it has N addresses and creates that many address objects. Would it be better to have separate address tables and extend the address object for agency and site to use different tables? – jshrc Jan 06 '12 at 17:42
  • @jshrc: I added a possible solution for this scenario to my answer. – Erwin Brandstetter Jan 06 '12 at 18:22