PostgreSQL复合外键部分字段更新后关联表对应字段自动同步方案问询

解决方案 #

方案1：给外键约束添加级联更新（最推荐） #

你遇到的问题本质是默认外键约束不支持父表主键变更同步到子表，PostgreSQL本身支持ON UPDATE CASCADE级联更新参数，添加后不管你更新复合主键里的多少个字段，数据库都会自动同步到子表对应的所有外键字段，不需要额外写逻辑。
操作步骤如下：

1. 先查询原有引用GridCell的外键约束名

SELECT conname FROM pg_constraint 
WHERE conrelid = 'GridCellOpDependentParticular'::regclass 
AND confrelid = 'GridCell'::regclass;

2. 删除原有外键约束，重建时添加级联更新参数

-- 替换<原外键约束名>为上一步查询到的实际名称
ALTER TABLE GridCellOpDependentParticular DROP CONSTRAINT <原外键约束名>;

-- 新建带级联更新的外键约束
ALTER TABLE GridCellOpDependentParticular 
ADD CONSTRAINT fk_gridcell_ref 
FOREIGN KEY (
    fk_gridCell_fourCornersOfKeyWindowRepresentativeToTreatmentAsGeoJSON,
    fk_gridCell_fourCornersOfKeyWindowRepresentativeToBufferAsGeoJSON,
    fk_gridCell_geometryOfCellRepresentativeToTreatment,
    fk_gridCell_geometryOfCellRepresentativeToBuffer,
    fk_gridCell_fk_site_selectedSiteID,
    fk_gridCell_fk_OpIndependentParticular_isTreatment,
    fk_gridCell_fk_OpIndependentParticular_isBuffer,
    fk_gridCell_fk_OpIndependentParticular_distanceFromCPOfTreatmentToNearestEdge,
    fk_gridCell_fk_OpIndependentParticular_distanceFromCPOfBufferToNearestEdge
) REFERENCES GridCell(
    fourCornersOfKeyWindowRepresentativeToTreatmentAsGeoJSON,
    fourCornersOfKeyWindowRepresentativeToBufferAsGeoJSON,
    geometryOfCellRepresentativeToTreatment,
    geometryOfCellRepresentativeToBuffer,
    fk_site_selectedSiteID,
    fk_OpIndependentParticular_isTreatment,
    fk_OpIndependentParticular_isBuffer,
    fk_OpIndependentParticular_distanceFromCPOfTreatmentToNearestEdge,
    fk_OpIndependentParticular_distanceFromCPOfBufferToNearestEdge
) ON UPDATE CASCADE;

配置完成后直接执行原有更新GridCell字段的SQL即可，子表对应的两个geometry外键字段会自动同步更新。

方案2：触发器实现（仅适用于无法修改外键约束的场景） #

如果业务限制不能修改外键，可以通过GridCell表的更新触发器实现同步：

-- 创建触发器函数
CREATE OR REPLACE FUNCTION sync_gridcell_geometry_to_child()
RETURNS TRIGGER AS $$
BEGIN
    -- 同步treatment字段
    IF OLD.geometryOfCellRepresentativeToTreatment <> NEW.geometryOfCellRepresentativeToTreatment THEN
        UPDATE GridCellOpDependentParticular
        SET fk_gridCell_geometryOfCellRepresentativeToTreatment = NEW.geometryOfCellRepresentativeToTreatment
        WHERE 
            fk_gridCell_fourCornersOfKeyWindowRepresentativeToTreatmentAsGeoJSON = OLD.fourCornersOfKeyWindowRepresentativeToTreatmentAsGeoJSON
            AND fk_gridCell_fourCornersOfKeyWindowRepresentativeToBufferAsGeoJSON = OLD.fourCornersOfKeyWindowRepresentativeToBufferAsGeoJSON
            AND fk_gridCell_geometryOfCellRepresentativeToTreatment = OLD.geometryOfCellRepresentativeToTreatment
            AND fk_gridCell_geometryOfCellRepresentativeToBuffer = OLD.geometryOfCellRepresentativeToBuffer
            AND fk_gridCell_fk_site_selectedSiteID = OLD.fk_site_selectedSiteID
            AND fk_gridCell_fk_OpIndependentParticular_isTreatment = OLD.fk_OpIndependentParticular_isTreatment
            AND fk_gridCell_fk_OpIndependentParticular_isBuffer = OLD.fk_OpIndependentParticular_isBuffer
            AND fk_gridCell_fk_OpIndependentParticular_distanceFromCPOfTreatmentToNearestEdge = OLD.fk_OpIndependentParticular_distanceFromCPOfTreatmentToNearestEdge
            AND fk_gridCell_fk_OpIndependentParticular_distanceFromCPOfBufferToNearestEdge = OLD.fk_OpIndependentParticular_distanceFromCPOfBufferToNearestEdge;
    END IF;

    -- 同步buffer字段
    IF OLD.geometryOfCellRepresentativeToBuffer <> NEW.geometryOfCellRepresentativeToBuffer THEN
        UPDATE GridCellOpDependentParticular
        SET fk_gridCell_geometryOfCellRepresentativeToBuffer = NEW.geometryOfCellRepresentativeToBuffer
        WHERE 上面的WHERE条件和上述一致;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 绑定触发器到GridCell表
CREATE TRIGGER trigger_sync_geometry_after_update
AFTER UPDATE OF geometryOfCellRepresentativeToTreatment, geometryOfCellRepresentativeToBuffer
ON GridCell
FOR EACH ROW EXECUTE FUNCTION sync_gridcell_geometry_to_child();

优化建议 #

你当前使用9个字段的复合主键冗余度极高，关联查询和维护成本都非常高，建议给GridCell表新增一个自增主键grid_cell_id serial primary key，其余关联表仅存储该ID作为外键，可彻底避免复合主键更新的各类问题。

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