小議Oracle外來鍵約束脩改行為（七）

Oracle的外來鍵用來限制子表中參考的欄位的值，必須在主表中存在。而且在主表的記錄發生變化導致外來鍵參考唯一約束值發生了變化時，定義了一系列的動作。

這篇描述一下如何實現RESTRICT。

小議Oracle外來鍵約束脩改行為（一）：http://yangtingkun.itpub.net/post/468/478045

小議Oracle外來鍵約束脩改行為（二）：http://yangtingkun.itpub.net/post/468/478119

小議Oracle外來鍵約束脩改行為（三）：http://yangtingkun.itpub.net/post/468/478172

小議Oracle外來鍵約束脩改行為（四）：http://yangtingkun.itpub.net/post/468/478280

小議Oracle外來鍵約束脩改行為（五）：http://yangtingkun.itpub.net/post/468/478374

小議Oracle外來鍵約束脩改行為（六）：http://yangtingkun.itpub.net/post/468/478437

 

 

前面幾篇文章介紹了Oracle所支援的3種約束行為NO ACTION、DELETE SET NULL和DELETE CASCADE。

至於SQL標準中定義的其他操作，Oracle只能透過觸發器來實現，這裡給出一個簡單的RESTRICT操作的例子。

SQL> DROP TABLE T_C;

表已刪除。

SQL> DROP TABLE T_P;

表已刪除。

SQL> CREATE TABLE T_P (ID NUMBER, NAME VARCHAR2(30));

表已建立。

SQL> ALTER TABLE T_P ADD PRIMARY KEY (ID);

表已更改。

SQL> CREATE TABLE T_C (ID NUMBER, FID NUMBER, NAME VARCHAR2(30));

表已建立。

SQL> ALTER TABLE T_C ADD CONSTRAINT FK_T_C
  2  FOREIGN KEY (FID)
  3  REFERENCES T_P (ID);

表已更改。

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

已建立 1 行。

SQL> INSERT INTO T_P VALUES (2, 'B');

已建立 1 行。

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

已建立 1 行。

SQL> COMMIT;

提交完成。

SQL標準定義的RESTRICT操作其實和NO ACTION操作十分類似。不同之處在於，RESTRICT的檢查在語句執行之前，一旦發現主鍵被引用，就會報錯，阻止更新或刪除操作的執行。

SQL> SELECT * FROM T_P;

        ID NAME
---------- ------------------------------
         1 A
         2 B

SQL> SELECT * FROM T_C;

        ID        FID NAME
---------- ---------- ------------------------------
         1          1 A

SQL> UPDATE T_P SET ID = 3 WHERE ID = 1;
UPDATE T_P SET ID = 3 WHERE ID = 1
*
第 1 行出現錯誤:
ORA-02292: 違反完整約束條件 (YANGTK.FK_T_C) - 已找到子記錄日誌

對於上面這種情況，無論是NO ACTION還是RESTRICT，效果沒有什麼差別，而對於下面的情況就不一樣了：

SQL> UPDATE T_P SET ID = ID - 1;

已更新2行。

SQL> SELECT * FROM T_P;

        ID NAME
---------- ------------------------------
         0 A
         1 B

SQL> UPDATE T_P SET ID = ID + 1;

已更新2行。

SQL> SELECT * FROM T_P;

        ID NAME
---------- ------------------------------
         1 A
         2 B

對於NO ACTION來說，約束的檢查發生在語句之後，所有上面的語句可以順利執行，而對於RESTRICT而言，這個操作會直接報錯。

下面構建一個RESTRICT操作的觸發器：

SQL> CREATE OR REPLACE TRIGGER T_P_RESTRICT
  2  BEFORE DELETE OR UPDATE OF ID ON T_P
  3  FOR EACH ROW
  4  WHEN (NVL(NEW.ID, -1) != NVL(OLD.ID, -1))
  5  DECLARE
  6   V_STR VARCHAR2(32767);
  7   V_COUNT NUMBER;
  8  BEGIN
  9   FOR I IN (SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME
 10    FROM ALL_CONSTRAINTS
 11    WHERE CONSTRAINT_TYPE = 'R'
 12    AND R_OWNER = 'YANGTK'
 13    AND R_CONSTRAINT_NAME IN
 14     (SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'T_P'))
 15   LOOP
 16    V_STR := 'SELECT COUNT(*) FROM  ' || I.TABLE_NAME || ' WHERE '; 
 17    FOR J IN (SELECT COLUMN_NAME FROM ALL_CONS_COLUMNS
 18     WHERE WNER = I.OWNER
 19     AND TABLE_NAME = I.TABLE_NAME
 20     AND CONSTRAINT_NAME = I.CONSTRAINT_NAME)
 21    LOOP
 22     V_STR := V_STR || J.COLUMN_NAME || ' = ' || :OLD.ID;
 23    END LOOP;
 24    EXECUTE IMMEDIATE V_STR INTO V_COUNT;
 25    IF V_COUNT > 0 THEN
 26     RAISE_APPLICATION_ERROR(-20001, '違反完整約束條件限制操作 - 已找到子記錄日誌');
 27    END IF;
 28   END LOOP;
 29  END;
 30  /

觸發器已建立

SQL> SELECT * FROM T_P;

        ID NAME
---------- ------------------------------
         1 A
         2 B

SQL> SELECT * FROM T_C;

        ID        FID NAME
---------- ---------- ------------------------------
         1          1 A

SQL> UPDATE T_P SET ID = ID - 1;
UPDATE T_P SET ID = ID - 1
                   *
第 1 行出現錯誤:
ORA-20001: 違反完整約束條件限制操作 - 已找到子記錄日誌
ORA-06512: 在"YANGTK.T_P_RESTRICT", line 22
ORA-04088: 觸發器 'YANGTK.T_P_RESTRICT' 執行過程中出錯

這種方式可以實現RESTRICT操作，但是對於其他會話的外來鍵的引用，RESTRICT操作是看不到的：

SQL> CONN YANGTK/YANGTK@YTK92
已連線。
SQL> SET SQLP 'SQL2> '
SQL2> INSERT INTO T_C VALUES (2, 2, 'B');

已建立 1 行。

在另一個會話插入一條參考主表ID為2的記錄，且不提交，這時在主表更新或刪除ID為2的記錄時，會被鎖住，RESTRICT操作不會起作用，因為這時看不到其他使用者未提交的修改：

SQL> DELETE T_P WHERE ID = 2;

而如果在第二個會話中提交事務：

SQL2> COMMIT;

提交完成。

則RESTRICT操作會起作用，因為這時已經提交，而當前的會話是可以看到其他會話中已提交資料的：

DELETE T_P WHERE ID = 2
                   *
第 1 行出現錯誤:
ORA-20001: 違反完整約束條件限制操作 - 已找到子記錄日誌
ORA-06512: 在"YANGTK.T_P_RESTRICT", line 22
ORA-04088: 觸發器 'YANGTK.T_P_RESTRICT' 執行過程中出錯

SQL>