ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 建立外键(FK) 以及Oracle9i中三种处理级联删除数据的方式

建立外键(FK) 以及Oracle9i中三种处理级联删除数据的方式

原创 Linux操作系统 作者:tolywang 时间:2009-04-20 11:27:14 0 删除 编辑


            创建外键约束时如果使用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)) ;


一般Oracle默认建立FK 是这样的:
alter table emp add constraint fk_emp_dept foreign key(dept) references dept(deptno) ;

这样在删除dept中的一个deptno 的时候 (假设这个单位deptno解散,不复存在), 就会报错,因为emp表中有外键参照dept中的deptno .     当然如果emp 表中的所有关于这个deptno 的行都已经删除,那么再来删除dept中的这个deptno行还是可以的。 

 

 

然后我们现在分别使用这两个关键字来增加外键试一下,首先我们来试一下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 empadd 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关键字

一般我们较多使用Oracle默认的方式,特殊情况下会选择下面两种 。
 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/35489/viewspace-605323/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 建立主键(PK)
请登录后发表评论 登录
全部评论
Oracle , MySQL, SAP IQ, SAP HANA, PostgreSQL, Tableau 技术讨论,希望在这里一起分享知识,讨论技术,畅谈人生 。

注册时间:2007-12-10

  • 博文量
    5595
  • 访问量
    13203364