[交流]外键开关
最近在做假资料时经常需要删除一些表中的内容。但是:
设置外键后,想删除表中的数据无法删除,这时需删除外键后重建,
或找到外键后用 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