PostgreSQL中如何为SCD Type 2表的当前版本创建外键？

PostgreSQL的标准外键约束不支持直接添加WHERE过滤条件，但可以通过以下两种方法实现需求：

方法一：利用部分唯一索引+复合外键 #

步骤1：升级location表的唯一约束 #

你已有的部分唯一索引需要调整为可被外键引用的部分唯一约束（外键仅能关联主键或唯一约束）：

ALTER TABLE location ADD CONSTRAINT unique_current_location UNIQUE (id) WHERE (checkout IS NULL);

该约束和原索引作用一致，保证同一id仅存在一条当前版本（checkout IS NULL）的记录，同时满足外键引用的要求。

步骤2：创建带辅助字段的event表并添加复合外键 #

要匹配location表的过滤条件，需要在event表中添加一个固定为NULL的辅助字段，通过复合外键关联：

CREATE TABLE event (
    id UUID PRIMARY KEY,
    location_id UUID,
    -- 辅助字段，固定为NULL，匹配location表当前版本的checkout值
    location_checkout_marker BOOLEAN GENERATED ALWAYS AS (NULL) STORED,
    title TEXT,
    -- 复合外键，仅关联location的当前版本记录
    FOREIGN KEY (location_id, location_checkout_marker) 
        REFERENCES location (id, checkout)
);

验证效果 #

• 关联当前版本location的插入操作会成功：

INSERT INTO location VALUES ('41af871f-a939-46d1-8cac-dd8489ca3248', 'Jupiter', '2024-01-01 00:00:00', NULL);
INSERT INTO event VALUES ('56ab1bf0-36bc-4380-a9d2-fccb88f5ca03', '41af871f-a939-46d1-8cac-dd8489ca3248', NULL, 'Success');

• 关联已过期location（checkout不为NULL）的插入操作会失败：

INSERT INTO location VALUES ('529d1030-0db1-4299-8557-055471820b66', 'Mars', '2023-01-01 00:00:00', '2024-01-01 00:00:00');
INSERT INTO event VALUES ('da5ecc5f-d63f-4803-883f-70cda9da7583', '529d1030-0db1-4299-8557-055471820b66', NULL, 'Failure');

方法二：使用触发器验证 #

如果不想添加辅助字段，可以通过触发器在插入/更新event时验证关联的location状态：

步骤1：创建验证函数 #

CREATE OR REPLACE FUNCTION validate_event_location_current()
RETURNS TRIGGER AS $$
BEGIN
    -- 检查关联的location是否存在且为当前版本
    IF NOT EXISTS (
        SELECT 1 FROM location 
        WHERE id = NEW.location_id AND checkout IS NULL
    ) THEN
        RAISE EXCEPTION '关联的location不存在或已过期';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

步骤2：给event表绑定触发器 #

CREATE TRIGGER trigger_event_location_current
BEFORE INSERT OR UPDATE OF location_id ON event
FOR EACH ROW EXECUTE FUNCTION validate_event_location_current();

验证效果 #

和方法一一致，插入关联过期location的操作会触发异常，阻止执行。

两种方法对比 #

• 方法一：基于数据库原生约束，性能更优，属于声明式约束，推荐优先使用。缺点是需要新增一个辅助字段。
• 方法二：无需修改表结构，但触发器属于过程化逻辑，性能略低；若location表的当前版本被标记为过期，还需额外处理event的关联逻辑（比如禁止更新或同步调整event）。

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