PostgreSQL如何实现全局通用外键关联以避免重复数据？

方案1：自定义触发器替代外键约束 #

PostgreSQL原生外键仅支持严格匹配目标表的主键/唯一键，无法直接实现"匹配当前公司记录或全局默认记录"的逻辑，用触发器可实现自定义校验：

1. 删除原有外键约束（如果已存在）

ALTER TABLE transports DROP CONSTRAINT IF EXISTS transports_measure_units_fk;

2. 创建触发器函数，实现校验逻辑

CREATE OR REPLACE FUNCTION check_measure_units_validity()
RETURNS TRIGGER AS $$
BEGIN
  -- 校验：要么存在当前公司的对应measure_units记录，要么存在全局默认（company_id=0）的对应记录
  IF NOT EXISTS (
    SELECT 1 FROM measure_units
    WHERE (company_id = NEW.company_id AND id = NEW.id)
       OR (company_id = 0 AND id = NEW.id)
  ) THEN
    RAISE EXCEPTION '无对应计量单元记录：company_id=%, id=%', NEW.company_id, NEW.id;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

3. 给transports表绑定触发规则

CREATE TRIGGER transports_validate_measure_units
BEFORE INSERT OR UPDATE ON transports
FOR EACH ROW EXECUTE FUNCTION check_measure_units_validity();

方案2：物化视图作为外键目标（适合低变更场景） #

通过物化视图生成所有公司的有效(company_id, id)组合（包含全局默认记录的映射），再将外键指向该视图：

1. 创建物化视图，整合全局记录和公司专属记录

CREATE MATERIALIZED VIEW measure_units_global AS
-- 公司专属记录
SELECT company_id, id FROM measure_units WHERE company_id != 0
UNION ALL
-- 将全局默认记录映射到所有存在的公司
SELECT c.company_id, mu.id 
FROM measure_units mu
CROSS JOIN (SELECT DISTINCT company_id FROM transports) c
WHERE mu.company_id = 0;

2. 给物化视图添加主键约束

ALTER TABLE measure_units_global ADD CONSTRAINT measure_units_global_pk PRIMARY KEY (company_id, id);

3. 给transports表添加外键约束

ALTER TABLE transports ADD CONSTRAINT transports_measure_units_fk
FOREIGN KEY (company_id, id) REFERENCES measure_units_global(company_id, id);

注意：当transports新增公司、measure_units新增/修改全局记录时，需要手动刷新物化视图：REFRESH MATERIALIZED VIEW measure_units_global;

方案3：重构数据模型（长期最优解） #

调整表结构区分全局和公司专属记录，避免逻辑模糊：

• 保留measure_units表存储公司专属记录，主键(company_id, id)（company_id ≠ 0）
• 创建global_measure_units表存储全局默认记录，主键id
• 在transports表中通过触发器校验：确保每条记录要么关联公司专属计量单元，要么关联全局计量单元

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