外键开关-数据库专栏,SQL Server

最近在做假资料时经常需要删除一些表中的内容。但是：

设置外键后，想删除表中的数据无法删除，这时需删除外键后重建，
或找到外键后用 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 +@fktable+ nocheck constraint + @fk
  print @s
 end
 else
 begin
  set @s = alter table +@fktable+ 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