Oracle之外键(Foreign Key)用法详解（二）- 级联删除（DELETE CASCADE），oraclecascade

Oracle外键（Foreign Key)之级联删除（DELETE CASCADE）

目标

示例讲解如何在Oracle外键中使用级联删除

什么是级联删除（DELETE CASCADE）？

级联删除是指当主表(parent table)中的一条记录被删除，子表中关联的记录也相应的自动删除。

外键的级联删除可以在创建表时定义，也可以使用ALTER TABLE语法定义。

创建表时定义级联删除

语法：

CREATE TABLE table_name
(
    column1 datatype null/not null,
    column2 datatype null/not null,
    ...
    CONSTRAINT fk_column
          FOREIGN KEY (column1,column2,...column_n)
          REFERENCES parent_table (column1, column2,... column_n)
          ON DELETE CASECADE
);

示例：

create table tb_supplier
(
  supplier_id number not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT pk_supplier PRIMARY KEY (supplier_id)
);

create table tb_products
(
  product_id number not null,
  product_name varchar2(100),
  supplier_id number not null,
  constraint fk_products_supplier 
     foreign key (supplier_id) 
     references tb_supplier(supplier_id)
     on delete cascade
);

验证：

1) 向表中插入测试数据

--主表插入样例数据
insert into tb_supplier values (1,'microsoft','microsoft');
insert into tb_supplier values (2,'ibm','ibm');
insert into tb_supplier values (3,'linux','linux');
insert into tb_supplier values (4,'dell','dell');
insert into tb_supplier values (5,'lenovo','lenovo');
insert into tb_supplier values (6,'google','google');

--子表插入样例数据
insert into tb_products values (1,'windows',1);
insert into tb_products values (2,'office',1);
insert into tb_products values (3,'informatica',2);
insert into tb_products values (4,'congos',2);
insert into tb_products values (5,'ubuntu',3);
insert into tb_products values (6,'centos',3);
insert into tb_products values (7,'inspiration',4);
insert into tb_products values (8,'thinkpad',5);
insert into tb_products values (9,'y410p',5);
insert into tb_products values (10,'android',6);
insert into tb_products values (11,'chrome',6);
insert into tb_products values (12,'hadoop',6);

--提交
commit;

2) 删除主表supplier_id=1的数据，并验证子表中相关联的数据是否被删除

--删除主表数据
delete from tb_supplier where supplier_id=1;
--提交
commit;
--验证子表数据是否被删除
select * from tb_products;

3) 从上面的结果可以看出，相关联的外键记录已经被级联删除。

使用ALTER TABLE语法定义级联删除

语法：

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
      FOREIGN KEY (column1, column2,... column_n)
      REFERENCES parent_table (column1, column2, ... column_n)
      ON DELETE CASCADE;

示例：

1) 删除之前的样例表

--删除之前的样例表
drop table tb_products;
drop table tb_supplier;

2) 重建之前的样例表

--重建样例表
create table tb_supplier
(
  supplier_id number not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT pk_supplier PRIMARY KEY (supplier_id)
);

create table tb_products
(
  product_id number not null,
  product_name varchar2(100),
  supplier_id number not null
);

3) 为表增加外键

alter table tb_products
add constraint fk_products_supplier
    foreign key(supplier_id)
    references tb_supplier(supplier_id)
    on delete cascade;

4) 插入样例数据

--主表插入样例数据
insert into tb_supplier values (1,'microsoft','microsoft');
insert into tb_supplier values (2,'ibm','ibm');
insert into tb_supplier values (3,'linux','linux');
insert into tb_supplier values (4,'dell','dell');
insert into tb_supplier values (5,'lenovo','lenovo');
insert into tb_supplier values (6,'google','google');

--子表插入样例数据
insert into tb_products values (1,'windows',1);
insert into tb_products values (2,'office',1);
insert into tb_products values (3,'informatica',2);
insert into tb_products values (4,'congos',2);
insert into tb_products values (5,'ubuntu',3);
insert into tb_products values (6,'centos',3);
insert into tb_products values (7,'inspiration',4);
insert into tb_products values (8,'thinkpad',5);
insert into tb_products values (9,'y410p',5);
insert into tb_products values (10,'android',6);
insert into tb_products values (11,'chrome',6);
insert into tb_products values (12,'hadoop',6);
--提交
commit;

5) 测试级联删除

--删除主表数据
delete from tb_supplier where supplier_id=1;
--提交
commit;

6) 验证：

--验证子表数据是否被删除
select * from tb_products;

7) 从上面的结果可以看出，相关联的外键记录已经被级联删除。

------------------------------------------------------------------------------------------------------------------------------------------------------

如果您们在尝试的过程中遇到什么问题或者我的代码有错误的地方，请给予指正，非常感谢！

联系方式：david.louis.tian@outlook.com

版权＠：转载请标明出处！
--------------------------------------------------------------------------------------------------------------------

先把原来带删除级联的外键删掉，重新创建带No Delete Set Null的外键。
例如：
alter table Emp drop constraint FK_Emp;
alter table Emp add constraint FK_Emp foreign key (DEPTNO)
references Dept (DEPTNO) on delete set null;
 

两种方法，个人建议你选择方法一，简单方便

方法一：触发器解决（下面的代码可以不用修改，copy直接用）
create or replace trigger delete_dept
before delete on DEPT
for each row
begin
delete from EMP where DEPT_NO = :old.DEPT_NO;
delete from POS where DEPT_NO = :old.DEPT_NO;
end;
/

方法二：修改你的外键设置，达到级联删除的目的，具体实现如下：
a)先查询出EMP表和POS表中 外键的名称(如果你知道 外键名这一步可以省略)
select CONSTRAINT_NAME,TABLE_NAME from user_constraints where CONSTRAINT_TYPE ='R' and TABLE_NAME in('EMP','POS');

b)删除EMP表和POS表上的外键后 重新建立允许级联删除的外键模式
alter table EMP drop constraint 外键名;
alter table POS drop constraint 外键名;
alter table EMP add constraint 外键名 foreign key(DEPT_NO) references DEPT(DEPT_NO) on delete cascade;
alter table POS add constraint 外键名 foreign key(DEPT_NO) references DEPT(DEPT_NO) on delete cascade;

---
以上，希望对你有所帮助。