PostgreSQL中如何为生成列外键实现ON UPDATE/DELETE CASCADE

问题 #

希望为生成列上的多态外键添加ON UPDATE CASCADE约束，操作步骤如下：

-- 创建两个父表
CREATE TABLE IF NOT EXISTS "SCH_A".parent_tab_a
(
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    name text COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT parent_tab_a_pkey PRIMARY KEY (id),
    CONSTRAINT parent_tab_a_nkey UNIQUE (name)
);

CREATE TABLE IF NOT EXISTS "SCH_A".parent_tab_b
(
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    name text COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT parent_tab_b_pkey PRIMARY KEY (id),
    CONSTRAINT parent_tab_b_nkey UNIQUE (name)
);

-- 创建子表
CREATE TABLE IF NOT EXISTS "SCH_A".child_tab
(
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    name text COLLATE pg_catalog."default" NOT NULL,
    parentname text COLLATE pg_catalog."default" NOT NULL,    
    parentcode text COLLATE pg_catalog."default" NOT NULL,
    "parent$parent_tab_a" text COLLATE pg_catalog."default" GENERATED ALWAYS AS (
    CASE
       WHEN (parentcode = 'parent_tab_a'::text) THEN parentname
       ELSE NULL::text
    END) STORED,
    "parent$parent_tab_b" text COLLATE pg_catalog."default" GENERATED ALWAYS AS (
    CASE
       WHEN (parentcode = 'parent_tab_b'::text) THEN parentname
       ELSE NULL::text
    END) STORED,
    CONSTRAINT child_tab_pkey PRIMARY KEY (id),
    CONSTRAINT "child_parent$parent_tab_a_fk" FOREIGN KEY ("parent$parent_tab_a")
        REFERENCES "SCH_A".parent_tab_a (name) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT "child_parent$parent_tab_b_fk" FOREIGN KEY ("parent$parent_tab_b")
        REFERENCES "SCH_A".parent_tab_b (name) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE 
);

执行子表创建语句时收到错误：

ERROR:  invalid ON UPDATE action for foreign key constraint containing generated column
SQL state: 42601

PostgreSQL不支持该操作，但需要实现父表parent_tab_a或parent_tab_b更新/删除行时，子表child_tab对应行同步更新/删除的效果，求替代方案。

替代方案 #

方法1：用触发器实现级联更新/删除 #

放弃生成列上的外键，改用触发器监听父表的更新、删除操作，主动同步子表数据。

针对parent_tab_a的触发器函数：

CREATE OR REPLACE FUNCTION "SCH_A".sync_child_from_parent_a()
RETURNS TRIGGER AS $$
BEGIN
    -- 更新子表中关联parent_tab_a的记录
    IF TG_OP = 'UPDATE' THEN
        UPDATE "SCH_A".child_tab
        SET parentname = NEW.name
        WHERE parentcode = 'parent_tab_a' AND parentname = OLD.name;
    -- 删除子表中关联parent_tab_a的记录
    ELSIF TG_OP = 'DELETE' THEN
        DELETE FROM "SCH_A".child_tab
        WHERE parentcode = 'parent_tab_a' AND parentname = OLD.name;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

创建触发器绑定到parent_tab_a：

-- 监听name字段更新
CREATE TRIGGER trigger_parent_a_update
AFTER UPDATE OF name ON "SCH_A".parent_tab_a
FOR EACH ROW
EXECUTE FUNCTION "SCH_A".sync_child_from_parent_a();

-- 监听行删除
CREATE TRIGGER trigger_parent_a_delete
AFTER DELETE ON "SCH_A".parent_tab_a
FOR EACH ROW
EXECUTE FUNCTION "SCH_A".sync_child_from_parent_a();

同理为parent_tab_b创建触发器：

CREATE OR REPLACE FUNCTION "SCH_A".sync_child_from_parent_b()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'UPDATE' THEN
        UPDATE "SCH_A".child_tab
        SET parentname = NEW.name
        WHERE parentcode = 'parent_tab_b' AND parentname = OLD.name;
    ELSIF TG_OP = 'DELETE' THEN
        DELETE FROM "SCH_A".child_tab
        WHERE parentcode = 'parent_tab_b' AND parentname = OLD.name;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_parent_b_update
AFTER UPDATE OF name ON "SCH_A".parent_tab_b
FOR EACH ROW
EXECUTE FUNCTION "SCH_A".sync_child_from_parent_b();

CREATE TRIGGER trigger_parent_b_delete
AFTER DELETE ON "SCH_A".parent_tab_b
FOR EACH ROW
EXECUTE FUNCTION "SCH_A".sync_child_from_parent_b();

方法2：调整表结构，用主键ID作为关联键 #

如果业务允许，改用父表的主键id作为关联字段（主键一般不更新），直接使用外键的级联特性：

CREATE TABLE IF NOT EXISTS "SCH_A".child_tab
(
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    name text COLLATE pg_catalog."default" NOT NULL,
    parent_id bigint NOT NULL,    
    parentcode text COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT child_tab_pkey PRIMARY KEY (id),
    -- 部分外键约束（PostgreSQL 12+支持），仅当parentcode匹配时生效
    CONSTRAINT child_parent_a_fk FOREIGN KEY (parent_id)
        REFERENCES "SCH_A".parent_tab_a (id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE
        WHERE parentcode = 'parent_tab_a',
    CONSTRAINT child_parent_b_fk FOREIGN KEY (parent_id)
        REFERENCES "SCH_A".parent_tab_b (id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE
        WHERE parentcode = 'parent_tab_b'
);

方法3：用视图模拟生成列，配合触发器同步 #

如果必须保留name作为关联字段，可以用视图模拟原生成列的展示效果，同时用检查约束保证数据合法性，再配合触发器实现同步：

1. 调整子表结构：

CREATE TABLE IF NOT EXISTS "SCH_A".child_tab
(
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    name text COLLATE pg_catalog."default" NOT NULL,
    parentname text COLLATE pg_catalog."default" NOT NULL,    
    parentcode text COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT child_tab_pkey PRIMARY KEY (id),
    -- 检查parentname对应父表的合法性
    CONSTRAINT check_parent_a_valid CHECK (
        (parentcode = 'parent_tab_a' AND EXISTS (SELECT 1 FROM "SCH_A".parent_tab_a WHERE name = parentname))
        OR parentcode != 'parent_tab_a'
    ),
    CONSTRAINT check_parent_b_valid CHECK (
        (parentcode = 'parent_tab_b' AND EXISTS (SELECT 1 FROM "SCH_A".parent_tab_b WHERE name = parentname))
        OR parentcode != 'parent_tab_b'
    )
);

2. 创建视图模拟生成列：

CREATE VIEW "SCH_A".child_tab_with_parents AS
SELECT
    id,
    name,
    parentname,
    parentcode,
    CASE WHEN parentcode = 'parent_tab_a' THEN parentname ELSE NULL END AS "parent$parent_tab_a",
    CASE WHEN parentcode = 'parent_tab_b' THEN parentname ELSE NULL END AS "parent$parent_tab_b"
FROM "SCH_A".child_tab;

3. 再使用方法1中的触发器实现父表更新/删除时的子表同步。

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