我创建了如下两个表,然后创建触发器进行级联删除,在删除table1中的数据时,table2表中对应的数据也删除掉,但是从table1中删除数据时,产生如下错误:DELETE 语句与 REFERENCE 约束"FK_pID"冲突。该冲突发生于数据库"db_bank",表"dbo.table2", column 'pID'。语句已终止。
请问如何解决。
create table table1
(
provinceID int primary key,
Name varchar(50)
)create table table2
(
cityID int primary key,
provinceID int,
Name varchar(50)
constraint FK_pID foreign key(provinceID)
references table1(provinceID)
)create trigger trig_delete
on table1
after delete
as
delete from table2
where pID=(select pID from deleted)
请问如何解决。
create table table1
(
provinceID int primary key,
Name varchar(50)
)create table table2
(
cityID int primary key,
provinceID int,
Name varchar(50)
constraint FK_pID foreign key(provinceID)
references table1(provinceID)
)create trigger trig_delete
on table1
after delete
as
delete from table2
where pID=(select pID from deleted)
--用触发器也可以。
create trigger trig_delete
on table1
after delete
as
delete table2 from deleted where table2.provinceID=deleted.provinceID
alter table table2 add constraint FK_pID foreign key(provinceID) references table1(provinceID) on delete cascade
create trigger trig_delete
on table1
for delete
as
delete from table2
where table2.provinceID=(select provinceID from deleted)
create trigger trig_delete
on table1
instead of delete
as
delete table2 from deleted where table2.provinceID=deleted.provinceID
delete table1 from deleted where table1.provinceID=deleted.provinceID
或用instead of 触发器,
或者不用外键.