oracle级联删除

最新推荐文章于 2024-07-21 00:23:42 发布

houzhanwublog

最新推荐文章于 2024-07-21 00:23:42 发布

阅读量218

点赞数

本文为博主原创文章，未经博主允许不得转载。

本文链接：https://blog.csdn.net/qq_27404929/article/details/53504351

版权

create table dpt(
dptno number(10) not null,
dptname varchar2(30) not null,
constraint pk_dpt primary key(dptno)
)
create table ep(
epno number(10) not null,
epname varchar2(30),
ep_dptno number(10),
constraint pk_ep primary key(epno)
);
方法一：on delete cascade
alter table ep
add constraint fk_ep foreign key(ep_dptno) references dpt(dptno)
on delete cascade;
insert into dpt values(1,'销售部');
insert into dpt values(2,'财务部');
insert into ep values(2,'Mary',1);
insert into ep values(3,'Linda',2);
insert into ep values(4,'Linlin',1);
delete from dpt where dptno=1;
方法二：on delete set null
//alter table ep drop constraint fk_ep(约束名) cascade;#删除约束
alter table ep
add constraint fk_ep foreign key(ep_dptno) references dpt(dptno)
on delete set null
insert into dpt values(1,'销售部');
insert into dpt values(2,'财务部');
insert into ep values(2,'Mary',1);
insert into ep values(3,'Linda',2);
insert into ep values(4,'Linlin',1);
delete from dpt where dptno=1;
方法三：使用触发器
create or replace trigger dpt_ep
before
delete on dpt for each row
begin
delete from ep where ep.ep_dptno=:old.dptno;
end