PostgreSQL 14中如何限制用户关联互斥特性？

现有数据库结构 #

create table users (
  id bigint primary key generated always as identity,
  name text not null
);

create table feature (
  id bigint primary key generated always as identity,
  value text not null check (value != '')
);

create unique index ux1 on feature(value);

-- 用户-特性关联表
create table user_feature (
  id bigint generated always as identity,
  user_id bigint not null references users(id),
  feature_id bigint not null references feature(id)
);

create unique index ux2 on user_feature(user_id, feature_id);

-- 初始化特性数据
insert into feature (value) values ('A'), ('B'), ('C'), ('D');

实现步骤 #

1. 创建互斥规则表 #

先建一张专门存储特性互斥关系的表，方便后续维护（新增/删除互斥对无需修改触发器代码）：

create table feature_mutex (
  id bigint primary key generated always as identity,
  feature1_id bigint not null references feature(id),
  feature2_id bigint not null references feature(id),
  -- 避免重复添加同一互斥组合（如(B,C)和(C,B)视为同一组）
  constraint ux_feature_mutex unique (least(feature1_id, feature2_id), greatest(feature1_id, feature2_id))
);

-- 插入示例互斥对：B和C
insert into feature_mutex (feature1_id, feature2_id)
select f1.id, f2.id from feature f1, feature f2 where f1.value = 'B' and f2.value = 'C';

2. 编写触发器检查函数 #

这个函数会在插入/更新用户-特性关联时，验证当前操作是否违反互斥规则：

create or replace function check_feature_mutex()
returns trigger as $$
begin
  -- 检查当前用户是否已关联与新特性互斥的其他特性
  if exists (
    select 1
    from feature_mutex fm
    join user_feature uf 
      on uf.feature_id = case 
        when fm.feature1_id = new.feature_id then fm.feature2_id
        when fm.feature2_id = new.feature_id then fm.feature1_id
        else null
      end
    where uf.user_id = new.user_id
  ) then
    -- 获取互斥特性名称，生成友好报错信息
    declare
      current_feature text;
      mutex_feature text;
    begin
      select f.value into current_feature from feature f where f.id = new.feature_id;
      select f.value into mutex_feature 
      from feature f 
      join feature_mutex fm 
        on f.id = case 
          when fm.feature1_id = new.feature_id then fm.feature2_id
          else fm.feature1_id
        end;
      raise exception '特性 % 与 % 互斥，同一用户不能同时关联', current_feature, mutex_feature;
    end;
  end if;
  return new;
end;
$$ language plpgsql;

3. 绑定触发器到关联表 #

给user_feature表的插入、更新操作绑定触发器，确保所有修改都经过互斥检查：

-- 插入时检查互斥规则
create trigger trg_user_feature_insert_check_mutex
before insert on user_feature
for each row execute function check_feature_mutex();

-- 更新特性ID时检查互斥规则（如果业务允许修改关联的特性）
create trigger trg_user_feature_update_check_mutex
before update of feature_id on user_feature
for each row execute function check_feature_mutex();

测试验证 #

1. 创建测试用户：

insert into users (name) values ('测试用户');

2. 测试互斥逻辑：

-- 关联特性B，执行成功
insert into user_feature (user_id, feature_id) select 1, id from feature where value = 'B';

-- 尝试关联特性C，触发报错
insert into user_feature (user_id, feature_id) select 1, id from feature where value = 'C';

执行第二条语句会抛出错误：特性 C 与 B 互斥，同一用户不能同时关联，符合预期。

3. 关联非互斥特性（如A和B）：

insert into user_feature (user_id, feature_id) select 1, id from feature where value = 'A';

该语句可正常执行，无报错。

扩展说明 #

• 若后续需要新增其他互斥对（如A和D），只需往feature_mutex表插入对应数据即可，无需修改触发器函数。
• 如果业务不需要动态维护互斥规则，也可以将互斥逻辑硬编码在触发器函数中（直接判断特性ID是否为B/C），但这种方式后期维护成本高，不推荐。

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