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
参考地址:http://bbs.csdn.net/topics/390019991