在這種情況下如何處理唯一性？

我有一張這樣的桌子：

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. 但其他獨特性正在尋找這兩列。我的意思是 type1 和 type2 列應該是唯一的。（當然第一條規則是不變的）

我試著用觸發器來做；

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 null else type1 end, type2);  -- I know it does not make sense but maybe there is something different that I can use in here.

Examples;

**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' 

uj5u.com熱心網友回復：

您嘗試做的事情在 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

uj5u.com熱心網友回復：

試試這樣：

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);

標籤：

上一篇：如何使用鍵的值組合字典串列？

下一篇：如何保持嵌套SQL陳述句？