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