Oracle之外键(Foreign Key)使用方法具体解释(二)- 级联删除(DELETE CASCADE)

时间:2021-02-12 03:38:17

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;
Oracle之外键(Foreign Key)使用方法具体解释(二)- 级联删除(DELETE CASCADE)

Oracle之外键(Foreign Key)使用方法具体解释(二)- 级联删除(DELETE CASCADE)

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;

Oracle之外键(Foreign Key)使用方法具体解释(二)- 级联删除(DELETE CASCADE)

Oracle之外键(Foreign Key)使用方法具体解释(二)- 级联删除(DELETE CASCADE)

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

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

假设您们在尝试的过程中遇到什么问题或者我的代码有错误的地方,请给予指正,很感谢!

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

版权@:转载请标明出处!
--------------------------------------------------------------------------------------------------------------------