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

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