外鍵開關
2024-07-21 02:08:05
供稿：網友
最近在做假資料時經常需要刪除一些表中的內容。但是：
設置外鍵后，想刪除表中的數據無法刪除，這時需刪除外鍵后重建，
或找到外鍵后用 alter table 表名 nocheck 外鍵名 來暫時屏蔽外鍵，然后刪除。
干脆寫個存儲過程，設置外鍵的開關。
exec fk_switch '表名',0 屏蔽外鍵
exec fk_switch '表名',1 重啟外鍵
/* usage:
exec fk_switch 'tablename',0
delete tablename where fieldname = 'abc'
-- truncate table tablename
exec fk_switch 'tablename',1
*/
create proc fk_switch @tablename varchar(20),@status bit
as
declare @fk varchar(50),@fktable varchar(20)
declare @s varchar(1000)
declare cur cursor for
select b.name as fkname,c.name as fktablename
from sysforeignkeys a
join sysobjects b on a.constid = b.id
join sysobjects c on a.fkeyid = c.id
join sysobjects d on a.rkeyid = d.id
where d.name = @tablename
open cur
fetch next from cur into @fk,@fktable
while @@fetch_status = 0
begin
if @status = 0
begin
set @s = 'alter table '[email protected]+' nocheck constraint '+ @fk
print @s
end
else
begin
set @s = 'alter table '[email protected]+' check constraint '+ @fk
print @s
end
exec(@s)
fetch next from cur into @fk,@fktable
end
close cur
deallocate cur
go
--以下為測試：
create table a (id int primary key)
go
create table b(id int,
constraint fk_b_a foreign key (id) references a (id))
go
create table c(id int,
constraint fk_c_a foreign key (id) references a (id))
go
insert a values (1)
insert b values(1)
insert c values (1)
--1:
delete a
/*****
服務器: 消息 547，級別 16，狀態 1，行 1
delete statement conflicted with column reference constraint 'fk_b_a'. the conflict occurred in database 'pubs', table 'b', column 'id'.
the statement has been terminated.
*******/
--2:
begin tran
exec fk_switch 'a',0
delete a
exec fk_switch 'a',1
rollback
/*
alter table b nocheck constraint fk_b_a
alter table c nocheck constraint fk_c_a
（所影響的行數為 1 行）
alter table b check constraint fk_b_a
alter table c check constraint fk_c_a
*/
--3: 清除測試表
drop table a,b,c
go
