下面是Oracle 19 c的一个例子：

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

我想对列pid和tech施加以下约束：

相同pid的一组记录中，不允许出现以下tech值的情况：
*多个相同的非空值
*多个null值
*null和非空值

换句话说，对于tech的null值，必须不存在任何其他null或非空值。对于非空值，必须不存在其他null或相同的值。
我的问题是这个任务是否可以使用普通unique index或其他简单的数据库特性来解决。如果其他解决方案不可能或难以理解，基于应用程序的保护是后备方案。

测试数据

(with插入给定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--, count(distinct tech) as d, count(tech) as t, count(*) as c
     , 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;

fiddle

尝试次数

我试过几次，但没有或部分成功：

• 基于辅助值的唯一索引（db fiddle）。该值作为虚拟列计算（vc），以便将nullMap到组内的其他非空现有值或Map到某些不存在的值（这对于设置和依赖来说不是问题）。然后将唯一索引强加在(pid,vc)元组上。在两个null或null一起非空的情况下，这样的索引的唯一性将被违反。问题是，vc列的值需要使用在同一个表上操作的函数来计算，并且在尝试插入时出现ORA-04091: table is mutating错误。（如果在虚拟列表达式中允许窗口函数，则不需要该函数，但不幸的是这是not possible。）
• table-scope constraint.本质上我需要的是在test data sample中没有count s定义的count_check='ERROR'行。将这样的约束作为一个整体施加在表上会很棒。我基于物化视图尝试了solution，但遇到了特权问题，而且两个新的db对象似乎也不太令人信服。