postgresql - Postgres : Ignore DELETE triggers for cascade deletes

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 有一个不可为空的外键约束，它链接到该人的地址之一。然后您的要求会自动满足，您不需要触发器。

这会使删除电子邮件地址等操作变得更加复杂，但您不必依赖触发器来确保完整性，is always subject to race conditions .