Удаление связанных данных между таблицами в PostgreSQL

Question

Я ищу лучший способ справиться с удалением связанных данных в трех таблицах PostgreSQL (технически больше, но это та же идея).

Три таблицы: агентство, адрес и адрес агентства. Агентство может иметь несколько адресов, так что адрес_агентства - это просто таблица ссылок с двумя столбцами: агент_ид и адрес_ид (определяется как их соответствующие внешние ключи)

Я хочу настроить его так, чтобы при удалении агентства оно автоматически удаляло строку таблицы ссылок в agency_address и соответствующую строку адреса. (То есть, если агентство удалено, все его адреса будут удалены)

Я могу заставить его очистить таблицу ссылок, но не уверен, как добраться до таблицы адресов.

(Также address - это общая модель, на которую будут ссылаться другие, например, "сайт", который будет иметь свои собственные таблицы ссылок.)

Answer 1

Используйте внешние ключи с ON DELETE CASCADE в определении таблицы.

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

Кажется неуверенным, что вам также следует автоматически удалять адреса.

Если это так, ваша модель данных неверна, и адреса должны напрямую зависеть от агентств, с ограничением внешнего ключа, аналогичным приведенному выше, таблица привязки n:m не требуется.

---

Изменить после получения дополнительной информации:

Таким образом, адреса могут быть связаны с агентствами или сайтами, но не с обоими одновременно.

Модель может работать так, как она есть у вас, но вам нужно как-то убедиться, что адрес, связанный с агентством, тоже не связан с сайтом.

Ограничение внешнего ключа между address а также agency_address указывает в "неправильном" направлении, поэтому вы не можете просто добавить другой ON DELETE CASCADE, Вы можете реализовать его с помощью дополнительного внешнего ключа, но это сложно. Этот подход для триггера намного проще:

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();

Подробнее о функциях триггеров и триггерах в руководстве.