主表:Goods:从表:Purchase:从表Purchase中有如下语句:
PRIMARY KEY (Gno),
FOREIGN KEY (Gno) REFERENCES Goods(Gno)
删除所有Purchase中Num少于20个的商品信息,不包括Purchase中没有的商品(就是删除商品号Gno为1,2,5的)
PRIMARY KEY (Gno),
FOREIGN KEY (Gno) REFERENCES Goods(Gno)
删除所有Purchase中Num少于20个的商品信息,不包括Purchase中没有的商品(就是删除商品号Gno为1,2,5的)
delete goods where Gno in(select Gno from purchase where num<20)--再删除主表
delete purchase where num<20
FROM Goods
WHERE Gno IN (SELECT Gno
FROM Purchase
WHERE Num < 20)
DELETE
FROM Purchase
WHERE Num<20
这样还是提示:DELETE 语句与 REFERENCE 约束"FK__Purchase__Gno__267ABA7A"冲突
Goods是主表,Purchase是从表
delete purchase where num<20--再删除主表
delete goods where Gno not in(select Gno from purchase)
看错了,应该这样
--先删除明细,会把删除的字段值,放到@temp表变量中
delete purchase
output deleted.gno into @temp
where num<20--再删除主表
delete goods where Gno in (select Gno from @temp)
--先删除明细,会把删除的字段值,放到@temp表变量中
delete purchase
output deleted.gno into @temp
where num<20--再删除主表
delete goods where Gno in (select Gno from @temp)
PRIMARY KEY (Gno),
FOREIGN KEY (Gno) REFERENCES Goods(Gno) ON DELETE CASCADE
创建从表时加上后面那句就行了
--先删除明细,会把删除的字段值,放到@temp表变量中
delete purchase
output deleted.gno into @temp
where num<20--再删除主表
delete goods where Gno in (select Gno from @temp)
PRIMARY KEY (Gno),
FOREIGN KEY (Gno) REFERENCES Goods(Gno) ON DELETE CASCADE
创建从表时加上后面那句就行了哦,对对,呵呵,好办法,级联删除。