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
版权@:转载请标明出处!--------------------------------------------------------------------------------------------------------------------