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

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

這篇簡單描述一下SET TO NULL操作。

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

 

 

上一篇描述了Oracle外來鍵處理預設操作：No Action，這裡簡單介紹一下SET TO NULL操作。還是利用前面例子的表，不過約束需要重建。

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)
  4  ON DELETE SET NULL;

表已更改。

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> INSERT INTO T_C VALUES (2, 2, 'B');

已建立 1 行。

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

已建立 1 行。

SQL> COMMIT;

提交完成。

下面檢查一下DELETE SET NULL是如何工作的：

SQL> SELECT * FROM T_P;

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

SQL> SELECT * FROM T_C;

        ID        FID NAME
---------- ---------- ------------------------------
         1          1 A
         2          2 B
         3          1 C

SQL> DELETE T_P WHERE ID = 2;

已刪除 1 行。

SQL> SELECT * FROM T_C;

        ID        FID NAME
---------- ---------- ------------------------------
         1          1 A
         2            B
         3          1 C

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

可以看到這個Set no null的操作和語法中的名稱一樣，只對DELETE操作有效，而對於UPDATE操作無效。

這個約束操作還有一個前提，就是要求子表的外來鍵列允許為空，否則對主表的DELETE操作會報錯：

SQL> SELECT * FROM T_C;

        ID        FID NAME
---------- ---------- ------------------------------
         1          1 A
         2            B
         3          1 C

SQL> DELETE T_C WHERE ID = 2;

已刪除 1 行。

SQL> ALTER TABLE T_C MODIFY FID NOT NULL;

表已更改。

SQL> DELETE T_P;
DELETE T_P
       *
第 1 行出現錯誤:
ORA-01407: 無法更新 ("YANGTK"."T_C"."FID") 為 NULL

從這裡也可以看到，雖然Oracle支援Set to null，但是隻是實現了DELETE語句，而沒有實現UPDATE語句，這和SQL標準的定義還是有區別的。