如何在PostgreSQL中实现跨表关联字段的共享唯一约束？

方案一：在people表冗余company_id字段（最直接可靠） #

调整后的表结构 #

appointment
-----------
id
name
department_id  -- 外键关联department.id

department
----------
id
name
company_id     -- 外键关联company.id

company
-------
id
name

people
------
id
name
appointment_id -- 外键关联appointment.id
company_id     -- 新增字段，外键关联company.id
personnel      -- 人员编号

约束与一致性保障 #

1. 添加唯一约束满足业务需求：

ALTER TABLE people ADD CONSTRAINT unique_personnel_company UNIQUE (personnel, company_id);

2. 用触发器保证company_id与关联数据一致：
   当appointment的部门关联变化，或department的公司关联变化时，自动同步people表的company_id：

CREATE OR REPLACE FUNCTION sync_people_company_id()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_TABLE_NAME = 'appointment' THEN
    UPDATE people
    SET company_id = (SELECT company_id FROM department WHERE id = NEW.department_id)
    WHERE appointment_id = NEW.id;
    RETURN NEW;
  ELSIF TG_TABLE_NAME = 'department' THEN
    UPDATE people
    SET company_id = NEW.company_id
    WHERE appointment_id IN (SELECT id FROM appointment WHERE department_id = NEW.id);
    RETURN NEW;
  END IF;
END;
$$ LANGUAGE plpgsql;

-- 给appointment表绑定更新触发器
CREATE TRIGGER trigger_appointment_update_company
AFTER UPDATE OF department_id ON appointment
FOR EACH ROW EXECUTE FUNCTION sync_people_company_id();

-- 给department表绑定更新触发器
CREATE TRIGGER trigger_department_update_company
AFTER UPDATE OF company_id ON department
FOR EACH ROW EXECUTE FUNCTION sync_people_company_id();

方案二：函数索引+关联完整性约束（无冗余字段） #

如果严格避免数据冗余，可通过函数索引间接实现跨表唯一约束，但复杂度较高：

1. 创建获取人员所属公司ID的函数：

CREATE OR REPLACE FUNCTION get_people_company_id(p_appointment_id INT)
RETURNS INT AS $$
SELECT company_id
FROM department d
JOIN appointment a ON d.id = a.department_id
WHERE a.id = p_appointment_id;
$$ LANGUAGE sql STABLE;

2. 基于函数创建唯一索引：

CREATE UNIQUE INDEX idx_unique_personnel_company ON people (personnel, get_people_company_id(appointment_id));

注意：需确保appointment与department、department与company的外键约束生效，避免关联关系断裂导致索引异常。

方案对比 #

• 方案一：实现简单，查询性能优，数据一致性由触发器兜底，适合绝大多数业务场景。
• 方案二：无字段冗余，但维护复杂度高，查询性能略低，仅适合对数据冗余要求极高的场景。

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