Oracle中如何用非默認方式創建外鍵

創建外鍵約束時如果使用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作用：

首先恢復剛才的那幾條數據，然後更改約束：

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

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

delete　from　dept　where　deptno　=　1;

你也會發現除了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關鍵字