外键指向不存在记录：为何ON DELETE CASCADE未生效？

问题背景 #

表与触发器定义 #

CREATE TABLE "parent_table"
(
    id uuid PRIMARY KEY
);

CREATE TABLE "child_table"
(
    id        bigserial PRIMARY KEY,
    parent_id uuid,
    CONSTRAINT "child_table_parent_id_fkey" FOREIGN KEY ("parent_id") REFERENCES "parent_table" ("id") ON DELETE CASCADE
);

CREATE OR REPLACE FUNCTION add_child_trigger() RETURNS TRIGGER AS
$$
BEGIN
    RETURN new;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER "on_change_add_change_event"
    BEFORE INSERT OR UPDATE OR DELETE
    ON "child_table"
    FOR EACH ROW
EXECUTE PROCEDURE "add_child_trigger"();

执行的SQL语句 #

INSERT INTO "parent_table" VALUES ('a275b51e-9a4f-460f-a0df-589fac39f9fe');
INSERT INTO "child_table" ("parent_id") VALUES ('a275b51e-9a4f-460f-a0df-589fac39f9fe');
DELETE FROM "parent_table" WHERE "id" = 'a275b51e-9a4f-460f-a0df-589fac39f9fe';

问题 #

执行完上述语句后，child_table中仍保留一条记录，但其parent_id指向的父表记录已被删除，违反了外键约束应保证目标记录存在的预期，这是为什么？

原因分析 #

问题出在child_table上的BEFORE DELETE触发器及其函数：

• 删除父表记录时，外键的ON DELETE CASCADE会自动触发子表对应记录的删除操作。
• 此时子表的on_change_add_change_event触发器（BEFORE DELETE类型）被激活，执行add_child_trigger函数。
• 在DELETE触发的上下文里，NEW变量未定义，RETURN new;实际返回的是NULL。
• PostgreSQL中，BEFORE DELETE触发器返回NULL会取消当前行的删除操作，导致原本要被CASCADE删除的子表记录被保留。
• 父表记录已成功删除，最终出现子表记录的外键指向不存在的父表记录的情况。

解决方法 #

修改触发器函数，根据触发事件返回正确的变量：

CREATE OR REPLACE FUNCTION add_child_trigger() RETURNS TRIGGER AS
$$
BEGIN
    -- 针对DELETE事件返回OLD，不阻止删除操作
    IF TG_OP = 'DELETE' THEN
        RETURN OLD;
    ELSE
        RETURN NEW;
    END IF;
END;
$$ LANGUAGE plpgsql;

或者，如果触发器不需要处理DELETE事件，直接修改触发器的触发条件：

DROP TRIGGER IF EXISTS "on_change_add_change_event" ON "child_table";
CREATE TRIGGER "on_change_add_change_event"
    BEFORE INSERT OR UPDATE
    ON "child_table"
    FOR EACH ROW
EXECUTE PROCEDURE "add_child_trigger"();

内容的提问来源于stack exchange，提问作者Tobias Marschall