如何为Postgres关联表添加枚举角色匹配约束？

当然可以通过PostgreSQL的约束或触发器机制，确保user_user表中user_captain_id对应角色为CAPTAIN、user_player_id对应角色为PLAYER。以下是两种常用方案：

方案一：复合外键约束（推荐） #

这种方式利用数据库原生外键约束实现验证，性能更优，逻辑清晰。

步骤1：修正并完善user表结构 #

首先修正原表创建的语法错误，并添加(id, role)的唯一约束（外键需引用唯一/主键列）：

DROP TYPE IF EXISTS USER_ROLE CASCADE;
CREATE TYPE USER_ROLE AS ENUM ('CAPTAIN', 'PLAYER');

CREATE TABLE IF NOT EXISTS "user" (
  id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  role USER_ROLE NOT NULL,
  -- 新增唯一约束，用于复合外键关联
  CONSTRAINT uq_user_id_role UNIQUE (id, role)
);

步骤2：创建带复合外键的user_user表 #

在关联表中添加固定值的角色字段，结合复合外键确保关联用户的角色符合要求：

CREATE TABLE IF NOT EXISTS user_user (
  user_captain_id INT,
  user_player_id INT,
  -- 固定角色字段，用于匹配外键约束
  user_captain_role USER_ROLE DEFAULT 'CAPTAIN' NOT NULL,
  user_player_role USER_ROLE DEFAULT 'PLAYER' NOT NULL,
  
  CONSTRAINT pk_user_user PRIMARY KEY (user_captain_id, user_player_id),
  -- 复合外键：确保captain_id对应用户的角色为CAPTAIN
  CONSTRAINT fk_user_captain FOREIGN KEY (user_captain_id, user_captain_role) 
    REFERENCES "user"(id, role),
  -- 复合外键：确保player_id对应用户的角色为PLAYER
  CONSTRAINT fk_user_player FOREIGN KEY (user_player_id, user_player_role) 
    REFERENCES "user"(id, role),
  -- 强制角色字段值固定，避免被修改
  CONSTRAINT chk_captain_role CHECK (user_captain_role = 'CAPTAIN'),
  CONSTRAINT chk_player_role CHECK (user_player_role = 'PLAYER')
);

方案二：触发器函数验证 #

如果不想在关联表中新增字段，可以用触发器实现动态验证：

步骤1：创建验证触发器函数 #

CREATE OR REPLACE FUNCTION validate_user_association_roles()
RETURNS TRIGGER AS $$
BEGIN
  -- 验证captain_id对应角色为CAPTAIN
  IF NOT EXISTS (
    SELECT 1 FROM "user" 
    WHERE id = NEW.user_captain_id AND role = 'CAPTAIN'
  ) THEN
    RAISE EXCEPTION 'user_captain_id % 必须关联角色为CAPTAIN的用户', NEW.user_captain_id;
  END IF;

  -- 验证player_id对应角色为PLAYER
  IF NOT EXISTS (
    SELECT 1 FROM "user" 
    WHERE id = NEW.user_player_id AND role = 'PLAYER'
  ) THEN
    RAISE EXCEPTION 'user_player_id % 必须关联角色为PLAYER的用户', NEW.user_player_id;
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

步骤2：为关联表绑定触发器 #

CREATE TRIGGER trg_check_user_roles
BEFORE INSERT OR UPDATE ON user_user
FOR EACH ROW EXECUTE FUNCTION validate_user_association_roles();

方案对比 #

• 复合外键方案：数据库原生约束验证，性能高，维护简单；缺点是需要额外存储两个角色字段。
• 触发器方案：无需额外字段，但每次操作都要执行查询，性能略逊，逻辑相对复杂。

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