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

主外鍵關聯，當删除的是父表資料，參照這些要删除的資料，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建立外鍵限制。實驗：

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

可以看到子表中參照父表的行也被删除了。

這種删除表的和上面相同。