oracle中使用on delete cascade和on delete set null來建立外鍵

oracle中使用on delete cascade和on delete set null來建立外鍵

其面我們介紹了建立外鍵限制時如果使用oracle預設的建立方式，在删除被參照的資料時，将無法被删除，這一點在oracle9i中給了我們更多靈活的選擇，我們可是使用on delete cascade和 on delete set null關鍵字來決定删除被參照資料時是否要将參照這個資料的那些資料一并删除，還是将那些參照這條資料的資料的對應值賦空。

例如下面這兩個表中分别存的時員工的基本資訊和公司的部門資訊。我們為

create table dept

(deptno number(10) not null,

deptname varchar2(30) not null,

constraint pk_dept primary key(deptno));

和

create table emp

( empno number(10) not null,

fname varchar2(20) ,

lname varchar2(20) ,

dept number(10) ,

constraint pk_emp primary key(empno));

然後我們現在分别使用這兩個關鍵字來增加外鍵試一下，首先我們來試一下on delete cascade

alter table emp

add constraint fk_emp_dept foreign key(dept) references dept(deptno) on delete cascade;

先增加外鍵。然後插入資料。

insert into dept values(1,’銷售部’);

insert into dept values(2,’财務部’);

insert into emp values (2,’Mary’,'Song’,1);

insert into emp values (3,’Linda’,'Liu’,2);

insert into emp values (4,’Linlin’,'Zhang’,1);

然後現在我要删除銷售部，會有什麼後果呢？

delete from dept where deptno = 1;

我們發現除了dept中的一條資料被删除了，emp中兩條資料也被删除了，其中emp中的兩條資料是參照了銷售部的這條資料的，這就很容易了解on delete cascade了。

接下來我們再來看on delete set null,顧名思義了，這種方式建立的外鍵限制，當被參照的資料被删除是，參照該資料的那些資料的對應值将會變為空值，下面我們還是通過試驗來證明on delete set null作用：

首先恢複剛才的那幾條資料，然後更改限制：

add constraint fk_emp_dept foreign key(dept) references dept(deptno) on delete set null;

然後我們在執行删除操作：

你也會發現除了dept中的銷售部被删除以外，emp中參照這條資料的兩條資料的dept的值被自動賦空了，這就是on delete set null的作用了。

使用on delete set null有一點需要注意的是，被參參照其他表的那一列必須能夠被賦空，不能有not null限制，對于上面的例子來說是emp中dept列一定不能有not null限制，如果已經定義了not null限制，又使用了on delete set null來删除被參照的資料時，将會發生：ORA-01407: 無法更新 (”DD”.”EMP”.”DEPT”) 為 NULL的錯誤。

總的來講on delete cascade和on delete set null的作用是用來處理級聯删除問題的，如果你需要删除的資料被其他資料所參照，那麼你應該決定到底希望oracle怎麼處理那些參照這些即将要删除資料的資料的，你可以有三種方式：

禁止删除。這也是oracle預設的

将那些參照本值的資料的對應列賦空，這個需要使用on delete set null關鍵字

将那些參照本值的資料一并删除，這個需要使用on delete cascade關鍵字