Oracle如何操作级联删除

级联删除即删除包含主键值的行的操作，该值由其它表的现有行中的外键列引用。在级联删除中，还删除其外键值引用删除的主键值的所有行。

语法：

1 2 3 4 5 6 7 8 9

Foreign  Key   ( column [,...n])   references  referenced_table_name[(ref_column[,...n])]   [ on  delete  cascade ]   [ on  update  cascade ]

　　

注释：

column:列名

referenced_table_name:外键参考的主键表名称

ref_name:外键要参考的表的主键列

on delete:删除级联

on update:更新级联

SQL级联删除——删除主表同时删除从表——同时删除具有主外键关系的表

实例学习：

1 2 3 4 5 6 7 8 9

create  table  a   (   id  varchar (20)  primary  key ,   password  varchar (20)  not  null   )

　　

1 2 3 4 5 6 7 8 9 10 11 12 13

create  table  b   (   id  int  identity(1,1)  primary  key ,   name  varchar (50)  not  null ,   userId  varchar (20),   foreign  key  (userId)  references  a(id)  on  delete  cascade   )

　　

表B创建了外码userId 对应A的主码ID，声明了级联删除。

测试数据：

1 2 3 4 5

insert  a  values  ( '11' , 'aaa' ) insert  a  values ( '23' , 'aaa' ) insert  b  values ( 'da' , '11' ) insert  b  values ( 'das' , '11' ) insert  b  values ( 'ww' , '23' )

　　

删除A表内id为‘11’的数据，发现B表内userId 为“11”也被数据库自动删除了，这就是级联删除

1	delete  a  where  id= '11'

　　

oracle中级联删除

使用on delete cascade和on delete set null来建立外键 。

其面我们介绍了创建外键约束时如果使用oracle默认的创建方式，在删除被参照的数据时，将无法被删除，这一点在oracle9i中给了我们更多灵活的选择，我们可是使用on delete cascade和 on delete set null关键字来决定删除被参照数据时是否要将参照这个数据的那些数据一并删除，还是将那些参照这条数据的数据的对应值赋空。 

例如下面这两个表中分别存的时员工的基本信息和公司的部门信息。我们为 

1 2 3 4 5 6 7 8 9 10 11

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 

1 2	alter  table  emp add  constraint  fk_emp_dept  foreign  key (dept)  references  dept(deptno)  on  delete  cascade ;

　

先增加外键。然后插入数据。 

1 2 3 4 5

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作用： 
首先恢复刚才的那几条数据，然后更改约束： 

1 2	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关键字；

 

本文转自邴越博客园博客，原文链接：http://www.cnblogs.com/binyue/p/3480043.html，如需转载请自行联系原作者