主外键关联,当删除的是父表数据,参照这些要删除的数据,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
这种删除表的和上面相同。