SQL sever中要删除两个相关联的表该怎么进行级联删除
SQL sever中要删除两个相关联的表该如何进行级联删除
如题,举个例子,现有P表(pno,pname),SPJ表(sno,pno),现在要删除p表中pname为螺丝的元组记录,顺便把在spj表相应的记录删除,而delete又不能同时删两个表,那该如何进行级联删除?
------解决方案--------------------
--1、建立一个触发器(推荐)
create trigger on p for delete
as
delete from spj where pno = (select pno from deleted)
go
--执行删除
delete from p where pname='螺丝'
--2、级联删除
alter table p add constraint pk_p_id primary key (pno)
go
--为tb创建外健,并指定级联删除
alter table spj add constraint fk_spj_aid foreign key (pno) references p(pno) on delete cascade
go
------解决方案--------------------
--推荐触发器控制,可控性比较强
如题,举个例子,现有P表(pno,pname),SPJ表(sno,pno),现在要删除p表中pname为螺丝的元组记录,顺便把在spj表相应的记录删除,而delete又不能同时删两个表,那该如何进行级联删除?
------解决方案--------------------
--1、建立一个触发器(推荐)
create trigger on p for delete
as
delete from spj where pno = (select pno from deleted)
go
--执行删除
delete from p where pname='螺丝'
--2、级联删除
alter table p add constraint pk_p_id primary key (pno)
go
--为tb创建外健,并指定级联删除
alter table spj add constraint fk_spj_aid foreign key (pno) references p(pno) on delete cascade
go
------解决方案--------------------
--推荐触发器控制,可控性比较强
- SQL code
--1、建立一个触发器(推荐) create trigger on p for delete as delete from spj where pno = (select pno from deleted) go --执行删除 delete from p where pname='螺丝' --2、级联删除 alter table p add constraint pk_p_id primary key (pno) go --为tb创建外健,并指定级联删除 alter table spj add constraint fk_spj_aid foreign key (pno) references p(pno) on delete cascade go
------解决方案--------------------
建立测试数据
- SQL code
if object_id('dbo.SPJ') is not null drop table dbo.SPJ; go if object_id('dbo.P') is not null drop table dbo.P; go create table dbo.P ( pno int not null primary key, pname nvarchar(20) not null ); go create table dbo.SPJ ( sno int not null primary key, pno int not null ); insert into dbo.P select 1, 'type-a' union all select 2, 'type-b' union all select 3, 'type-c'; go insert into dbo.SPJ select 1, 1 union all select 2, 1 union all select 3, 1 union all select 4, 2 union all select 5, 3 union all select 6, 3; go
------解决方案--------------------
建议用外键约束
先删除子表在删除父表
------解决方案--------------------
个人建议用事务处理。
- SQL code
begin tran trPDel delete from spj where pno = (select top 1 pno from p where pname='螺丝') delete from p where pname='螺丝' if @@error<>0 begin rollback tran trPDel return end commit tran trPDel