删除 PostgreSQL 中跨表的链接数据

Question

我正在寻找处理跨三个 PostgreSQL 表删除链接数据的最佳方法（技术上更多，但这是相同的想法）。

这三个表是agency、address 和agency_address。一个机构可以有多个地址，因此agency_address只是一个包含两列的链接表，agency_id和address_id（定义为各自的外键）

我想对其进行设置，以便在删除机构时它将删除链接表中的行Agency_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();

有关触发器函数和<的更多信息手册中的 href="http://www.postgresql.org/docs/current/interactive/sql-createtrigger.html" rel="nofollow noreferrer">触发器。