主外來鍵關聯刪除(on delete set null和on delete cascade)

bisal發表於2014-03-10
deleteNull

主外來鍵關聯，當刪除的是父表資料，參照這些要刪除的資料，Oracle有三種處理方式：

1、禁止刪除，也是Oracle預設方法。

2、將參照要刪除資料的子表對應資料置空。

3、將參照要刪除資料的子表對應資料刪除。


對於1，比較容易理解，不解釋。


對於2，需要使用on delete set null建立外來鍵約束。實驗：


create table dept_test
(deptno number(10) not null,
 deptname varchar2(30) not null,
 constraint pk_dept_test primary key(deptno));

create table emp_test
(empno number(10) not null,
 fname varchar2(20) ,
 lname varchar2(20) ,
 dept number(10) ,
 constraint pk_emp_test primary key(empno));



alter table emp_test
add constraint fk_emp_dept_test foreign key(dept) references dept_test(deptno) on delete set null;



insert into dept_test values(1,'銷售部');
insert into dept_test values(2,'財務部');
insert into emp_test values (2,'Mary','Song',1);
insert into emp_test values (3,'Linda','Liu',2);
insert into emp_test values (4,'Linlin','Zhang',1);



delete from dept_test where deptno = 1;
1 row deleted.



SQL> select * from emp_test;
     EMPNO FNAME          LNAME                  DEPT
---------- -------------------- -------------------- ----------
     2 Mary           Song
     3 Linda          Liu                     2
     4 Linlin          Zhang

SQL> select * from dept_test;
    DEPTNO DEPTNAME
---------- ------------------------------
     2 財務部
可以看到emp_test中參照dept_test中deptno=1的兩條記錄對應dept值已經置為空。




SQL> truncate table emp_test;
Table truncated.

SQL> truncate table dept_test;
truncate table dept_test
               *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
此時也是禁止清空dept_test表，drop相同。



如果想要刪除父表，可以有兩種方法：

1、先drop子表，再drop父表。

2、先刪除約束alter table emp_test drop constraint fk_emp_dept_test;，再drop父表。


對於3，需要使用on delete cascade建立外來鍵約束。實驗：


alter table emp_test
add constraint fk_emp_dept_test foreign key(dept) references dept_test(deptno) on delete cascade;



delete from dept_test where deptno = 1;
1 row deleted.



SQL> select * from dept_test;
    DEPTNO DEPTNAME
---------- ------------------------------
     2 財務部

SQL> select * from emp_test;
     EMPNO FNAME          LNAME                  DEPT
---------- -------------------- -------------------- ----------
     3 Linda          Liu                     2
可以看到子表中參照父表的行也被刪除了。

SQL> truncate table emp_test;
Table truncated.

SQL> truncate table dept_test;
truncate table dept_test
               *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
這種刪除表的和上面相同。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7192724/viewspace-1104299/，如需轉載，請註明出處，否則將追究法律責任。

相關文章