PostgreSQL在schema层实现项目经理分配约束的方法

首先修正现有Schema的笔误 #

你当前worker_project表的worker_id外键配置错误，误引用了teams(id)，实际应该关联员工表workers(id)，否则无法正确关联员工数据。

核心约束实现方案 #

要在数据库层面强制实现「单个项目中每个团队仅能有1名经理」的规则，不需要依赖业务层校验，最稳妥的方式是冗余少量必要字段+部分唯一索引，性能远高于触发器，且属于数据库原生约束，可靠性最高：

1. 在worker_project表中冗余存储team_id字段，通过联合外键强制该字段与员工所属团队完全一致，避免数据不一致
2. 给is_manager字段设置默认值false，适配普通员工分配的场景，插入普通成员记录时不需要额外传值
3. 创建带条件的部分唯一索引，仅对is_manager = true的记录生效，限制同一项目+同一团队只能有1条经理记录

最终可用的表结构如下：

-- 项目表保持原有逻辑
CREATE TABLE projects (
    id INT PRIMARY KEY
);

-- 团队表保持原有逻辑
CREATE TABLE teams (
    id INT PRIMARY KEY
);

CREATE TABLE workers (
    id INT PRIMARY KEY,
    team_id INT NOT NULL REFERENCES teams(id),
    -- 新增联合唯一约束，供worker_project的联合外键引用
    UNIQUE (id, team_id)
);

CREATE TABLE worker_project (
    id INT PRIMARY KEY,
    worker_id INT NOT NULL,
    team_id INT NOT NULL,
    project_id INT NOT NULL REFERENCES projects(id),
    is_manager BOOLEAN NOT NULL DEFAULT false,
    -- 保持原有员工-项目唯一约束，禁止重复分配
    UNIQUE (worker_id, project_id),
    -- 联合外键强制team_id必须对应该员工所属的团队，杜绝冗余字段数据不一致
    -- 如需支持员工跨团队调动，可加ON UPDATE CASCADE实现team_id自动同步
    FOREIGN KEY (worker_id, team_id) REFERENCES workers(id, team_id) ON UPDATE CASCADE
);

-- 核心约束：单个项目下，每个团队最多只能有1名经理
CREATE UNIQUE INDEX idx_unique_team_manager_per_project
ON worker_project(project_id, team_id)
WHERE is_manager = true;

约束效果说明 #

所有业务规则都会被数据库原生强制拦截，不需要业务层写任何校验逻辑：

• 插入不存在的员工/项目：直接触发外键约束报错
• 给同一员工重复分配同一项目：触发(worker_id, project_id)唯一约束报错
• 写入错误的team_id、或随意修改员工所属团队：触发联合外键约束报错，加ON UPDATE CASCADE后会自动同步关联的team_id，不会产生脏数据
• 给同一项目下的同一个团队设置第二名经理：直接触发部分唯一索引报错
• 普通员工（is_manager = false）的分配不受任何限制，同一项目下同一团队可以有任意数量的普通成员
• 被设为经理的员工必然已经分配到对应项目：因为is_manager是项目分配表的字段，没有分配记录就不存在设置经理的可能，天然满足规则

如果不想冗余team_id字段，也可以通过BEFORE INSERT OR UPDATE触发器查询关联员工的team_id做校验，但触发器方案性能更差，且在批量写入、并发更新场景下更容易出现漏判，优先推荐上述冗余字段+部分索引的方案。

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