利用虛擬列實現虛擬刪除的主外來鍵約束

以前寫過一篇文章，描述了當結論不從表中真正刪除，而是利用DELETE_FLAG來表示刪除時，無法再利用Oracle的主鍵鍵進行約束。不過前兩天newkid版主巧妙了利用了11g的虛擬列來解決了這個問題。

這裡給出完整的虛擬列實現的主子表DELETE_FLAG方式的外來鍵約束。

利用11G的虛擬列實現複雜的外來鍵約束：http://www.itpub.net/thread-1322611-1-1.html

無法利用主外來鍵時控制主子表的併發訪問（一）：http://yangtingkun.itpub.net/post/468/453295

無法利用主外來鍵時控制主子表的併發訪問（二）：http://yangtingkun.itpub.net/post/468/453397

無法利用主外來鍵時控制主子表的併發訪問（三）：http://yangtingkun.itpub.net/post/468/453590

 

 

看一個簡單的例子：

SQL> CREATE TABLE T_FATHER
  2  (ID NUMBER,
  3  NAME VARCHAR2(30),
  4  DELETE_FLAG VARCHAR2(1));

表已建立。

SQL> CREATE TABLE T_CHILDREN
  2  (ID NUMBER,
  3  FID NUMBER,
  4  F_FLAG AS (CAST (DECODE(DELETE_FLAG, 'Y', NULL, 'N') AS VARCHAR2(1))),
  5  DELETE_FLAG VARCHAR2(1));

表已建立。

表中包含了DELETE_FLAG，資料並不會真正的刪除，當刪除資料的時候，將DELETE_FLAG置為Y。顯然普通的主外來鍵無法滿足這種設計的需要，因為將主表的記錄刪除的操作只是修改DELETE_FLAG列，而主表的記錄仍然存在，因此子表新增的記錄仍然可以參考主表已經產生的記錄。

不過利用子表的虛擬列來建立外來鍵越是，可以解決這個問題：

SQL> ALTER TABLE T_FATHER ADD                 
  2  PRIMARY KEY (ID, DELETE_FLAG);

表已更改。

SQL> ALTER TABLE T_CHILDREN ADD
  2  FOREIGN KEY (FID, F_FLAG)
  3  REFERENCES T_FATHER;

表已更改。

下面測試一下外來鍵是否可以正常運作：

SQL> INSERT INTO T_CHILDREN (ID, FID, DELETE_FLAG) VALUES (1, 1, 'N');
INSERT INTO T_CHILDREN (ID, FID, DELETE_FLAG) VALUES (1, 1, 'N')
*
第 1 行出現錯誤:
ORA-02291: 違反完整約束條件 (TEST.SYS_C0011285) - 未找到父項關鍵字

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

已建立 1 行。

SQL> INSERT INTO T_CHILDREN (ID, FID, DELETE_FLAG) VALUES (1, 1, 'N');

已建立 1 行。

SQL> INSERT INTO T_CHILDREN (ID, FID, DELETE_FLAG) VALUES (2, 1, 'Y');

已建立 1 行。

SQL> UPDATE T_FATHER
  2  SET DELETE_FLAG = 'Y'
  3  WHERE ID = 1;
UPDATE T_FATHER
*
第 1 行出現錯誤:
ORA-02292: 違反完整約束條件 (TEST.SYS_C0011285) - 已找到子記錄

SQL> UPDATE T_CHILDREN
  2  SET DELETE_FLAG = 'Y'
  3  WHERE ID = 1;

已更新 1 行。

SQL> UPDATE T_FATHER
  2  SET DELETE_FLAG = 'Y'
  3  WHERE ID = 1;

已更新 1 行。

SQL> INSERT INTO T_CHILDREN (ID, FID, DELETE_FLAG) VALUES (3, 1, 'N');
INSERT INTO T_CHILDREN (ID, FID, DELETE_FLAG) VALUES (3, 1, 'N')
*
第 1 行出現錯誤:
ORA-02291: 違反完整約束條件 (TEST.SYS_C0011285) - 未找到父項關鍵字

SQL> INSERT INTO T_CHILDREN (ID, FID, DELETE_FLAG) VALUES (3, 1, 'Y');

已建立 1 行。

SQL> UPDATE T_FATHER
  2  SET DELETE_FLAG = 'N'
  3  WHERE ID = 1;

已更新 1 行。

SQL> UPDATE T_CHILDREN
  2  SET DELETE_FLAG = 'N'
  3  WHERE ID = 2;

已更新 1 行。

SQL> COMMIT;

提交完成。

可以看到，利用虛擬列配合主表的DELETE_FLAG建立的聯合主外來鍵，完全滿足這種虛擬刪除的主子表關係，可惜的是，只有11g採用虛擬列的功能，11g以前，必須利用觸發器來構造真實的表列。