在这种情况下，我如何处理独特性

Question

我有一个这样的表：

create table my_table
(
type1 varchar2(10 char),
type2 varchar2(10 char) 
);

我想要这样的独特性；

1. 如果type1列具有"GENERIC"值，则只有type2的列对于表必须是唯一的。例如
   • type1列具有"GENERIC"值，type2列具有"value_x"，则任何type2列的值都不得等于"value_x">
2. 但另一个独特之处是同时寻找这两个专栏。我的意思是，它应该是唯一的类型1和类型2列。(当然，第一条规则是不变的(

我试着用触发器；

CREATE OR REPLACE trigger my_trigger
BEFORE INSERT OR UPDATE 
ON my_table
FOR EACH ROW 
DECLARE 
lvn_count NUMBER :=0;
lvn_count2 NUMBER :=0;
errormessage clob;
MUST_ACCUR_ONE EXCEPTION;
--   PRAGMA AUTONOMOUS_TRANSACTION; --without this it gives mutating error but I cant use this because it will conflict on simultaneous connections
BEGIN
IF :NEW.type1 = 'GENERIC' THEN
SELECT count(1) INTO lvn_count FROM my_table
WHERE type2= :NEW.type2;

ELSE 

SELECT count(1) INTO lvn_count2 FROM my_table
WHERE type1= :NEW.type1 and type2= :NEW.type2;

END IF;     

IF (lvn_count >= 1 or lvn_count2 >= 1) THEN
RAISE MUST_ACCUR_ONE; 
END IF;
END;

但在没有pragma的情况下，它会产生突变错误。由于同时连接的冲突，我不想使用它。(错误，因为我在触发器上使用相同的表(

我试着用唯一的索引做它，但我做不到。

CREATE UNIQUE INDEX my_table_unique_ix 
ON my_table (case when type1= 'GENERIC' then 'some_logic_here' else type1 end, type2);  -- I know it does not make sense but maybe there is something different that I can use in here.

示例；

**Example 1**
insert into my_table (type1,type2) values ('a','b'); -- its ok no problem
insert into my_table (type1,type2) values ('a','c'); -- its ok no problem
insert into my_table (type1,type2) values ('c','b'); -- its ok no problem
insert into my_table (type1,type2) values ('GENERIC','b'); -- it should be error because b is exist before (i look just second column because first column value is 'GENERIC')

EXAMPLE 2:
insert into my_table (type1,type2) values ('GENERIC','b'); -- its ok no problem
insert into my_table (type1,type2) values ('a','c'); -- its ok no problem
insert into my_table (type1,type2) values ('d','c'); -- its ok no problem
insert into my_table (type1,type2) values ('d','b'); -- it should be error because second column can not be same as the second column value that first column value is 'GENERIC' 

Answer 1

在Oracle中，您尝试做的事情并不简单。一种可能的(尽管有些麻烦(方法是使用的组合

• 带有refresh (on commit)的附加物化视图
• 计算每组不同值数量的窗口函数
• 计算每组GENERIC行数的窗口函数
• 一个检查约束，以确保我们只有一个DISTINCT值，或者在同一组中没有GENERIC

这应该有效：

create materialized view mv_my_table 
refresh on commit
as
select 
type1,
type2,
count(distinct type1) over (partition by type2) as distinct_type1_cnt,
count(case when type1 = 'GENERIC' then 1 else null end) 
over (partition by type2) as generic_cnt
from my_table;
alter table mv_my_table add constraint chk_type1 
CHECK (distinct_Type1_cnt = 1 or generic_cnt = 0);

现在，插入重复项不会立即失败，但后续的COMMIT会失败，因为它会触发物化视图刷新，这将导致激发检查约束。

缺点

• 重复的INSERT不会立即失败(使调试更加痛苦(
• 根据表的大小，MView刷新可能会大大降低COMMIT的速度

链接

有关此方法的更详细讨论，请参阅AskTom关于跨行约束

Answer 2

这样试试：

CREATE TABLE my_table (
type1 VARCHAR2(10 CHAR),
type2 VARCHAR2(10 CHAR),
type1_unique VARCHAR2(10 CHAR) GENERATED ALWAYS AS ( NULLIF(type1, 'GENERIC') ) VIRTUAL
);
ALTER TABLE MY_TABLE ADD (CONSTRAINT my_table_unique_ix UNIQUE (type1_unique, type2) USING INDEX)

或者像这样的索引也应该起作用：

CREATE UNIQUE INDEX my_table_unique_ix ON MY_TABLE (NULLIF(type1, 'GENERIC'), type2);

或者按照你的风格来做(你只错过了END(：

CREATE UNIQUE INDEX my_table_unique_ix ON my_table (case when type1= 'GENERIC' then null else type1 end, type2);

Answer 3

除非我遗漏了一些明显的东西，否则@Frank Schmitt的答案中的逻辑也可以使用语句级触发器来实现。它的实现要简单得多，而且没有Frank提到的缺点。

create or replace TRIGGER my_table_t
AFTER INSERT OR UPDATE OR DELETE 
ON my_table
DECLARE
l_dummy NUMBER;
MUST_ACCUR_ONE EXCEPTION;  
BEGIN
WITH constraint_violated AS
(
select 
type1,
type2,
count(distinct type1) over (partition by type2) as distinct_type1_cnt,
count(case when type1 = 'GENERIC' then 1 else null end) 
over (partition by type2) as generic_cnt
from my_table
)
SELECT 1 INTO l_dummy 
FROM constraint_violated
WHERE NOT (distinct_type1_cnt = 1 or generic_cnt = 0) FETCH FIRST 1 ROWS ONLY;
RAISE MUST_ACCUR_ONE; 
EXCEPTION WHEN NO_DATA_FOUND THEN
NULL;
END;
/