在Oracle中强加复杂的唯一约束

Question

我有以下Oracle 19c表:

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或其他简单的数据库功能。基于应用程序的保护是在其他解决方案不可能或难以理解时的后备方案。

测试数据

(预期结果是插入给定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;

COUNT_CHECK好好好

您可以在提交时使用具有快速刷新功能的物化视图并将约束放在那里，但是约束将仅在提交时进行检查。(+在(pid,tech)上的唯一索引)

CREATE MATERIALIZED VIEW LOG ON t
WITH rowid, primary key, COMMIT SCN, SEQUENCE
( pid, tech ) INCLUDING NEW VALUES
;
CREATE MATERIALIZED VIEW mv_t 
REFRESH FAST ON COMMIT 
AS
SELECT pid, 
count(case when tech is null then 1 end ) as c_null,
count(case when tech is not null then 1 end) as c_notnull
FROM t 
GROUP BY pid
;

ALTER TABLE  mv_t 
ADD CONSTRAINT mv_ck_nulls CHECK ( 
(c_null = 0 and c_notnull >= 0)
or 
(c_null = 1 and c_notnull = 0)
) ENABLE
;

问题最终在应用程序级别得到解决。我们认为在较低级别保护约束的好处不值得添加和维护新的数据库对象。谢谢大家的建议。我的问题的答案很简单:no