利用函式索引解決複雜的約束問題

今天在pub上看到一個有意思的問題，需要建立一個複雜的約束條件。http://www.itpub.net/showthread.php?s=&threadid=707666

簡單描述一下問題，需要建立條件唯一約束。對於每個ACCOUNT，要求表中只能有一條記錄的標識位為1，標識位為0的記錄沒有限制。

Oracle的普通約束無法滿足這個條件。解決最常見的方法透過觸發器進行設定。帖子中西門吹牛採用了物化檢視的方法，建立一個彙總的ON COMMIT物化檢視，並在物化檢視上建立一個CHECK約束，這樣有問題的資料在提交時會報錯的。缺點是相對比較麻煩，而且錯誤檢查必須放到提交的時刻。

最簡單的解決方法是建立一個唯一函式索引。

看一個簡單的例子：

SQL> CREATE TABLE T (ACCOUNT NUMBER, NAME VARCHAR2(30), FLAG NUMBER(1));

表已建立。

SQL> CREATE UNIQUE INDEX IND_T_ACCOUNT ON T(DECODE(FLAG, 1, ACCOUNT, NULL));

索引已建立。

SQL> INSERT INTO T VALUES (1, 'A', 1);

已建立 1 行。

SQL> INSERT INTO T VALUES (1, 'A', 0);

已建立 1 行。

SQL> INSERT INTO T VALUES (2, 'A', 1);

已建立 1 行。

SQL> INSERT INTO T VALUES (1, 'B', 0);

已建立 1 行。

SQL> INSERT INTO T VALUES (1, 'C', 1);
INSERT INTO T VALUES (1, 'C', 1)
*第 1 行出現錯誤:
ORA-00001: unique constraint (TEST.IND_T_ACCOUNT) violated

開始的時候我給出的方案中，索引的建立方式為：

CREATE UNIQUE INDEX IND_T_ACCOUNT ON T(DECODE(FLAG, 1, ACCOUNT, NULL), DECODE(FLAG, 1, 1, NULL));

經zhouwf0726的提醒，發現索引後半部分沒有意義，因此改成上面的索引建立方法。