Oracle外鍵串聯刪除和串聯更新，oracle鍵級更新

Oracle外鍵串聯刪除和串聯更新，oracle鍵級更新

1 串聯刪除

Oracle在外鍵的刪除上有NO ACTION(類似RESTRICT)、CASCADE和SET NULL三種行為。

下面以學生-班級為例說明不同情況下的外鍵刪除，學生屬於班級，班級的主鍵是學生的外鍵。

-- 班級表CRATE TABLE TB_CLASS(  ID    NUMBER NOT NULL, --班級主鍵  NAME  VARCHAR2(50), --班級名稱  CONSTRAINT PK_TB_CLASS PRIMARY KEY (ID));-- 學生表CREATE TABLE TB_STUDENT(  ID        NUMBER NOT NULL,   --學生主鍵  NAME      VARCHAR2(50),      --學生姓名  CLASS_ID  NUMBER,            --學生所屬班級，外鍵    --主鍵約束  CONSTRAINT PK_TB_STUDENT PRIMARY KEY (ID),    --外鍵約束  --設定串聯刪除為NO ACTION  CONSTRAINT FK_TB_STUDENT_CLASS_ID FOREIGN KEY (CLASS_ID) REFERENCES TB_CLASS (ID));-- 添加班級資料INSERT INTO TB_CLASS (ID, NAME) VALUES (1, '一班');INSERT INTO TB_CLASS (ID, NAME) VALUES (2, '二班');INSERT INTO TB_CLASS (ID, NAME) VALUES (3, '三班');-- 新增學生資料INSERT INTO TB_STUDENT (ID, NAME, CLASS_ID) VALUES (1, '小明', 1);INSERT INTO TB_STUDENT (ID, NAME, CLASS_ID) VALUES (2, '小剛', 1);INSERT INTO TB_STUDENT (ID, NAME, CLASS_ID) VALUES (3, '小王', 1);INSERT INTO TB_STUDENT (ID, NAME, CLASS_ID) VALUES (4, '二明', 2);INSERT INTO TB_STUDENT (ID, NAME, CLASS_ID) VALUES (5, '二剛', 2);INSERT INTO TB_STUDENT (ID, NAME, CLASS_ID) VALUES (6, '二王', 2);INSERT INTO TB_STUDENT (ID, NAME, CLASS_ID) VALUES (7, '大明', 3);INSERT INTO TB_STUDENT (ID, NAME, CLASS_ID) VALUES (8, '大剛', 3);INSERT INTO TB_STUDENT (ID, NAME, CLASS_ID) VALUES (9, '大王', 3);

初始班級資料

初始學生資料

1.1 NO ACTION

NO ACTION指當刪除主表中被引用列的資料時，如果子表的引用列中包含該值，則禁止該操作執行。

現在學生外鍵串聯刪除是NO ACTION，執行刪除班級操作。

--刪除三班DELETE FROM TB_CLASS WHERE ID=3;

Oracle會提示違反完整性條件約束，。

如果想要刪除三班，必須先刪除三班的學生。

--刪除三班學生DELETE FROM TB_STUDENT WHERE CLASS_ID=3;--刪除三班DELETE FROM TB_CLASS WHERE ID=3;

1.2 SET NULL
SET NULL指當刪除主表中被引用列的資料時，將子表中相應引用列的值設定為NULL值。SET NULL有個前提就是外鍵引用列必須可以設定為NULL。

把學生表(TB_STUDENT)的外鍵刪除行為改為SET NULL。ORACLE似乎沒有MODIFY CONSTRAINT操作，只能先刪除外鍵，然後建立新的。

--刪除學生表(TB_STUDENT)表的外鍵ALTER TABLE TB_STUDENT DROP CONSTRAINT FK_TB_STUDENT_CLASS_ID;--刪除添加ON DELETE SET NULL外鍵ALTER TABLE TB_STUDENT ADD CONSTRAINT FK_TB_STUDENT_CLASS_ID FOREIGN KEY (CLASS_ID) REFERENCES TB_CLASS (ID) ON DELETE SET NULL;--刪除一班DELETE FROM TB_CLASS WHERE ID=1;

由於外鍵的ON DELETE是SET NULL，所以當刪除一班時，一班學生的CLASS_ID被設定為NULL，。

1.3 CASCADE

CASCADE指當刪除主表中被引用列的資料時，串聯刪除子表中相應的資料行。

把學生表(TB_STUDENT)的外鍵刪除行為改為CASCADE。

--刪除TB_STUDENT表上的NO ACTION外鍵ALTER TABLE TB_STUDENT DROP CONSTRAINT FK_TB_STUDENT_CLASS_ID;--刪除添加ON DELETE CASCADE外鍵ALTER TABLE TB_STUDENT ADD CONSTRAINT FK_TB_STUDENT_CLASS_ID FOREIGN KEY (CLASS_ID) REFERENCES TB_CLASS (ID) ON DELETE CASCADE;--刪除二班DELETE FROM TB_CLASS WHERE ID=2;

由於外鍵的ON DELETE是CASCADE，所以當刪除二班時，二班下的學生也會被刪除。

2 串聯更新

Oracle本身並不支援外鍵的串聯更新，不過可以按照如下方法達到串聯更新的效果。

首先要先瞭解Oracle延遲約束和非延遲約束。非延遲約束就是在修改記錄的時候會立刻進行約束條件的查看，是否因為違反了某些約束條件而不能執行修改。延遲約束不會在剛進行修改的時候進行約束查看，只有提交的時候才會檢查。Oracle的串聯更新就是使用這個特性來實現的。

Oracle的外鍵預設是非延遲約束，修改學生的外鍵為延遲約束。

--刪除學生表(TB_STUDENT)上的已有外鍵ALTER TABLE TB_STUDENT DROP CONSTRAINT FK_TB_STUDENT_CLASS_ID;--添加延遲約束外鍵ALTER TABLE TB_STUDENT ADD CONSTRAINT FK_TB_STUDENT_CLASS_ID FOREIGN KEY (CLASS_ID) REFERENCES TB_CLASS (ID) ON DELETE CASCADE DEFERRABLE;

設定觸發器，當班級表(TB_CLASS)的主鍵改變了，就更新學生表(TB_STUDENT)的外鍵(CLASS_ID)。

CREATE OR REPLACE TRIGGER TGR_TB_CLASS_UPDATE AFTER UPDATE OF ID ON TB_CLASSFOR EACH ROWBEGIN  IF :OLD.ID<>:NEW.ID THEN    UPDATE TB_STUDENT SET CLASS_ID=:NEW.ID WHERE CLASS_ID=:OLD.ID;  END IF;END;

注意：

Oracle外鍵串聯更新方法可以用於外鍵和外鍵引用的主鍵在不同表上。不過會經常遇到以下情況，就是在資料庫中儲存具有層級關係的資料時，表的外鍵引用同一個表的主鍵。這時候無法用觸發器實現串聯更新。

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。