知识点的梳理:
on delete cascade:当主表数据删除时,对应的子表数据同时删除;
on delete set null:当主表数据删除时,对应的子表数据设置为null;
问题1:删除父表数据前需要先删除所有子表的对应数据
由于子表的所有数据都要和父表的数据对应,所以在删除父表数据时需要先将子表中对应的数据删除干净,否则将无法删除
示例1:删除member表中编号为“1”的数据(mid=1),此时没有删除子表(advice)数据
DELETE FROM member WHERE mid=1 ;
member中的mid数据与advice有关联关系,所以只能先删除advice中的数据,才能删除member的数据;先删除子表(advice)中mid=1的数据,之后再删除父表(member)中mid=1的数据
DELETE FROM advice WHERE mid=1 ;
DELETE FROM member WHERE mid=1 ;
COMMIT ;
这样的删除较为复杂,可以通过级联操作来简化操作!
级联操作
1
:级联删除(
on delete cascade
)
级联删除是指在建立外键约束时通过
on delete cascade
子句设置,这样在删除父表数据时,由父表数据关联的所有子表数据都会被同时删除
示例:修改表创建语法,增加级联删除,同时配置测试数据
DROP TABLE advice PURGE ;
DROP TABLE member PURGE ;
CREATE TABLE member (
mid NUMBER ,
name VARCHAR2(200) NOT NULL ,
CONSTRAINT pk_mid PRIMARY KEY (mid)
) ;
CREATE TABLE advice (
adid NUMBER ,
content CLOB NOT NULL ,
mid NUMBER ,
CONSTRAINT pk_adid PRIMARY KEY (adid) ,
CONSTRAINT fk_mid FOREIGN KEY(mid) REFERENCES member(mid) ON DELETE CASCADE
) ;
INSERT INTO member (mid,name) VALUES (1,'李兴华') ;
INSERT INTO member (mid,name) VALUES (2,'董鸣楠') ;
INSERT INTO advice (adid,content,mid) VALUES (1,'应该提倡内部沟通机制,设置总裁邮箱',1) ;
INSERT INTO advice (adid,content,mid) VALUES (3,'要多开展员工培训活动,让员工更加有归属感',2) ;
COMMIT ;
此时删除member表中编号为1的成员信息,可以触发级联操作,完成删除动作;
DELETE FROM member WHERE mid=1 ;
级联操作
2
:级联设置
null
(
on delete set null
)
该级联操作在删除父表时,子表的数据不会被删除,并将与父表关联的字段设置为
null
;
示例:修改表的创建语句,增加
ON DELETE SET NULL
子句
DROP TABLE advice PURGE ;
DROP TABLE member PURGE ;
CREATE TABLE member (
mid NUMBER ,
name VARCHAR2(200) NOT NULL ,
CONSTRAINT pk_mid PRIMARY KEY (mid)
) ;
CREATE TABLE advice (
adid NUMBER ,
content CLOB NOT NULL ,
mid NUMBER ,
CONSTRAINT pk_adid PRIMARY KEY (adid) ,
CONSTRAINT fk_mid FOREIGN KEY(mid) REFERENCES member(mid) ON DELETE SET NULL
) ;
INSERT INTO member (mid,name) VALUES (1,'hey') ;
INSERT INTO member (mid,name) VALUES (2,'yan') ;
INSERT INTO advice (adid,content,mid) VALUES (1,'应该提倡内部沟通机制,设置总裁邮箱',1) ;
INSERT INTO advice (adid,content,mid) VALUES (3,'要多开展员工培训活动,让员工更加有归属感',2) ;
COMMIT ;
删除member表中mid为1的记录
DELETE FROM member WHERE mid=1 ;
问题2:删除父表时需要先将子表删除
上例是删除父表的某一个数据,如果直接删除父表则会出现以下错误:
如果想删除父表,只能先删除子表,在删除父表
示例:先删除子表(
advice
),再删除父表(
member
)
DROP TABLE advice PURGE ;
DROP TABLE member PURGE ;
强制删除父表:
这种删除方式会之间干掉父表,但是子表,以及与父表关联的字段数据都会得到保留;
如果使用了此种方式,就不能再直接使用
purge
选项了;
DROP TABLE member CASCADE CONSTRAINT ;
使用了上面的
sql
删除
member
表后,查询
advice
表。可以发现
mid
依然有父表的值,但它们与父表毫无关系了;