在Oracle 19c中实现复杂唯一约束的方案咨询

表结构 #

create table t (
  id number primary key,
  pid number not null,
  tech varchar2(1)
);

约束需求 #

对pid和tech列施加以下约束：
同一pid的记录组中，tech值不允许出现以下情况：

• 多个相同的非null值
• 多个null值
• null值与非null值共存

换句话说：

• 若tech为null，则该pid下不能存在其他null或非null值；
• 若tech为非null值，则该pid下不能存在其他null或相同值。

希望通过普通唯一索引或其他简单数据库特性实现该需求，若方案过于复杂则考虑在应用层校验。

测试数据 #

（插入给定pid的所有行的预期结果：pid<10的测试用例成功，pid>=90的测试用例失败）

with t(pid, tech) as (
  select 1  , null from dual union all
  select 2  , 'A'  from dual union all
  select 3  , 'A'  from dual union all
  select 3  , 'B'  from dual union all
  select 90 , null from dual union all
  select 90 , null from dual union all
  select 91 , 'A'  from dual union all  
  select 91 , 'A'  from dual union all
  select 92 , null from dual union all  
  select 92 , null from dual union all  
  select 92 , 'A'  from dual union all  
  select 92 , 'B'  from dual union all
  select 93 , null from dual union all  
  select 93 , null from dual union all  
  select 93 , 'A'  from dual union all  
  select 93 , 'A'  from dual union all
  select 94 , null from dual union all  
  select 94 , 'A'  from dual union all  
  select 94 , 'A'  from dual union all
  select 95 , null from dual union all  
  select 95 , null from dual union all  
  select 95 , 'A'  from dual union all
  select 96 , null from dual union all
  select 96 , 'A'  from dual union all
  select 97 , null from dual union all
  select 97 , 'A'  from dual union all
  select 97 , 'B'  from dual
)
select pid
     , case when count(*) = 1 or count(*) = count(tech) and count(*) = count(distinct tech) then 'OK' else 'ERROR' end as count_check
from t
group by pid
order by pid;

测试结果 #

已尝试方案（均未完全生效） #

• 基于辅助值的唯一索引：尝试通过虚拟列vc将null映射为组内已存在的非null值或特定值，再在(pid, vc)上创建唯一索引，以此阻止多个null或null与非null共存的情况。但因虚拟列需要调用操作同表的函数，插入数据时触发ORA-04091: 表发生变化错误；且虚拟列不支持窗口函数，无法绕过该问题。
• 表级约束（物化视图方案）：试图通过物化视图确保表中不存在count_check='ERROR'的行，但遇到权限问题，且新增多个数据库对象的方案不够简洁。

可行解决方案 #

方案1：复合触发器（避免变异表问题） #

通过复合触发器在语句执行后校验受影响的pid是否违反约束，可在数据库层面实现所有规则：

create or replace trigger trg_t_check_constraint
for insert or update or delete on t
compound trigger
  type pid_list is table of number;
  pids_to_check pid_list;

  before statement is
  begin
    pids_to_check := pid_list();
  end before statement;

  after each row is
  begin
    if inserting or updating then
      pids_to_check.extend;
      pids_to_check(pids_to_check.last) := :new.pid;
    end if;
    if deleting or updating then
      pids_to_check.extend;
      pids_to_check(pids_to_check.last) := :old.pid;
    end if;
  end after each row;

  after statement is
    v_error_count number;
  begin
    -- 去重后检查受影响的PID
    for rec in (select distinct pid from table(pids_to_check)) loop
      select count(*) into v_error_count
      from t
      where pid = rec.pid
      group by pid
      having not (count(*) = 1 or (count(*) = count(tech) and count(*) = count(distinct tech)));
      
      if v_error_count > 0 then
        raise_application_error(-20001, 'PID ' || rec.pid || ' 违反约束：同一PID下不能有多个相同非null值、多个null值或null与非null共存');
      end if;
    end loop;
  end after statement;
end trg_t_check_constraint;
/

该触发器会在INSERT/UPDATE/DELETE操作完成后，检查所有受影响的pid是否符合约束规则，若违反则抛出自定义错误，阻止操作提交。

方案2：物化视图+主键约束（纯数据库对象方案） #

若倾向于用数据库对象实现而非触发器，可通过快速刷新物化视图实现表级约束：

1. 创建物化视图日志：

create materialized view log on t with rowid, (pid, tech) including new values;

2. 创建用于校验约束的物化视图：

create materialized view mv_t_constraint_check
refresh fast on commit
as
select 1 as dummy_col
from t
group by pid
having not (count(*) = 1 or (count(*) = count(tech) and count(*) = count(distinct tech)))
having count(*) >= 1;

3. 在物化视图上创建主键约束：

alter table mv_t_constraint_check add constraint pk_mv_t primary key (dummy_col);

当存在违反约束的pid时，物化视图会返回至少一行dummy_col=1的记录，触发主键重复错误，阻止事务提交。需确保用户拥有创建物化视图及物化视图日志的权限。

方案3：应用层校验 #

若上述数据库层面方案过于复杂，可在应用层插入/更新数据前，先执行如下查询校验pid是否符合约束：

select case when count(*) = 1 or (count(*) = count(tech) and count(*) = count(distinct tech)) then 'OK' else 'ERROR' end as check_result
from t
where pid = :target_pid
group by pid;

若返回ERROR则拒绝执行数据操作。

内容的提问来源于stack exchange，提问作者Tomáš Záluský