Postgres:忽略级联删除的 DELETE 触发器

Question

我正在尝试实现人员与电子邮件地址的关系，其中一个人必须始终至少拥有一个电子邮件地址。表格如下所示：

CREATE TABLE persons (
id serial PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE email_addresses (
id serial PRIMARY KEY,
person_id integer REFERENCES persons (id) ON DELETE CASCADE ON UPDATE CASCADE,
email_address text NOT NULL
);

为了实现一个人必须至少有一个电子邮件地址的约束，我想我会使用触发器。满足约束所需的触发器之一是email_addresses表上的BEFORE DELETE触发器，如果DELETE将删除某人的最后一个电子邮件地址，则会引发错误：

CREATE FUNCTION email_addresses_delete_trigger() RETURNS trigger AS $$
DECLARE
num_email_addresses integer;
BEGIN
num_email_addresses := (SELECT count(*) FROM email_addresses WHERE person_id = OLD.person_id);
IF num_email_addresses < 2 THEN
RAISE EXCEPTION 'A person must have at least one email address';
END IF;
RETURN OLD;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER email_addresses_delete_trigger BEFORE DELETE ON email_addresses
FOR EACH ROW EXECUTE FUNCTION email_addresses_delete_trigger();

此触发器执行其预期操作，但它会阻止从persons表中删除人员。例如：

mydb=# DELETE FROM persons WHERE id = 1;
ERROR:  A person must have at least one email address
CONTEXT:  PL/pgSQL function email_addresses_delete_trigger() line 7 at RAISE
SQL statement "DELETE FROM ONLY "public"."email_addresses" WHERE $1 OPERATOR(pg_catalog.=) "person_id""

如果我要删除一个人，那么我也希望删除他们所有的电子邮件地址，所以我不在乎在级联删除过程中是否保留了触发器表示的约束。有没有办法在删除人员时"忽略"此触发器？还是有其他方法需要删除一个人？

Answer 1

我的建议是更改数据模型，以便您有一个不可为空的外键约束，从persons到链接到该人的地址之一的email_addresses。然后，您的要求将自动满足，并且您不需要触发器。

这将使删除电子邮件地址等某些事情变得更加复杂，但您不必依赖完整性触发器，这始终受竞争条件的约束。