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

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

這篇簡單描述一下CASCADE操作。

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

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

 

 

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

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

表已更改。

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;

提交完成。

下面看看CASCADE是如何工作的：

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
         3          1 C

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) - 已找到子記錄日誌

可以看到，和SET TO NULL約束一樣，CASCADE操作也是隻對DELETE語句有效，而對於UPDATE語句不起作用。

另外提一句，和SET TO NULL不同，CASCADE具有傳遞性，主表刪除一條記錄，會導致子表參考的記錄被刪除，而子表的記錄被刪除又會導致子表的子表記錄被刪除：

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

表已更改。

SQL> CREATE TABLE T_C_C (ID NUMBER PRIMARY KEY, FID NUMBER,
  2  CONSTRAINT FK_T_C_C FOREIGN KEY (FID) REFERENCES T_C(ID) ON DELETE CASCADE);

表已建立。

SQL> INSERT INTO T_C_C VALUES (1, 1);

已建立 1 行。

SQL> CREATE TABLE T_C_C_C (ID NUMBER PRIMARY KEY, FID NUMBER,
  2  CONSTRAINT FK_T_C_C_C FOREIGN KEY (FID) REFERENCES T_C_C(ID) ON DELETE CASCADE);

表已建立。

SQL> INSERT INTO T_C_C_C VALUES (1, 1);

已建立 1 行。

SQL> COMMIT;

提交完成。

下面看看級聯刪除的效果：

SQL> SELECT * FROM T_P;

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

SQL> SELECT * FROM T_C;

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

SQL> SELECT * FROM T_C_C;

        ID        FID
---------- ----------
         1          1

SQL> SELECT * FROM T_C_C_C;

        ID        FID
---------- ----------
         1          1

SQL> DELETE T_P;

已刪除 1 行。

SQL> SELECT * FROM T_P;

未選定行

SQL> SELECT * FROM T_C;

未選定行

SQL> SELECT * FROM T_C_C;

未選定行

SQL> SELECT * FROM T_C_C_C;

未選定行