禁用、启用外键约束和触发器

参考资料：http://huangqiqing123.iteye.com/blog/1420465

【一、SQL命令】
--- 禁用指定表所有外键约束 
alter table [表名] NOCHECK constraint all 

--- 启用指定表所有外键约束 
alter table [表名] CHECK constraint all

 【二、拼装SQL语句】
-- 禁用指定表外键约束的sql
select 'ALTER TABLE '+b.name+' NOCHECK CONSTRAINT '+a.name+';' 
from sysobjects a
inner join sysobjects b onb.id=a.parent_obj
where a.xtype='f'andb.name='表名'  

 -- 启用指定表外键约束的sql
select 'ALTER TABLE '+b.name+' CHECK CONSTRAINT '+a.name+';' 
from sysobjects a
inner join sysobjects b onb.id=a.parent_obj
where a.xtype='f'andb.name='表名' 

【三、查询字典表sys.foreign_keys】
         查看约束状态（查询字典表sys.foreign_keys，该字典表开始出现于sqlserver2005及以上版本）：
select name , is_disabled from sys.foreign_keys order by name

其中：
name  ：外键约束名称
is_disabled :是否已禁用（这个值很重要，如下图）。

 

【使用案例】
  要求：
            我们现在要做一些单据记录（包括单头与单据明细）导入到转到历史表中，导入完后删除当前表的记录。

  遇到问题：
          因为单头表与单据明细表作了约束，所以无论导数到历史表或者删除当前表的记录都涉及约束的问题。我们非常清楚每个表的记录依赖关系，必须先导单头记录，再导单据明细记录，否则就会违返约束不能继续；删除当前表的记录时即相反，先要删除单据明细记录，再删除单头记录，否则同样会违返约束不能继续。

  解决方法：
          在导数和删除记录前将约束停用，在导数和删除记录后重新开启约束。（当然触发器也要作相同的处理）

 

--Sql脚本：
use [数据库]
Go 

SET XACT_ABORT ON
begin tran 
-- 1.禁用FOREIGN KEY约束（即禁用要求“强制外键约束”的关系）
DECLARE @TableName sysname, @ConstraintName sysname,@SQLString nvarchar(500) 

SELECT object_name(parent_obj) as TableName, name as ConstraintName
into [_MyForeignKey]
FROM sysobjects f   
WHERE 
    exists(select * from sys.foreign_keys t where t.[is_disabled]=0 and t.[name]=f.name and t.[Object_ID]=f.ID) --有“强制外键约束”
    and f.xtype=N'F' 

DECLARE cTmp CURSOR local static read_only forward_only FOR 
    SELECT TableName, ConstraintName
    FROM [_MyForeignKey]
OPEN cTmp
FETCH cTmp INTO @TableName, @ConstraintName
WHILE (@@FETCH_STATUS= 0)
BEGIN
    SET @SQLString='ALTER TABLE '+@TableName+ ' NOCHECK CONSTRAINT '+ @ConstraintName
    EXEC sp_executesql @SQLString
    -- Next
    FETCH cTmp INTO @TableName, @ConstraintName
END
CLOSE cTmp
DEALLOCATE cTmp

 -- 2.关闭触发器
DECLARE cTmp CURSOR local static read_only forward_only FOR  
    SELECT name FROM sysobjects WHERE xtype= N'U'
OPEN cTmp
FETCH cTmp INTO @TableName
WHILE (@@FETCH_STATUS= 0)
BEGIN
    SET @SQLString='ALTER TABLE '+@TableName+ ' DISABLE TRIGGER all'
    EXEC sp_executesql @SQLString
    -- Next
    FETCH cTmp INTO @TableName
END
CLOSE cTmp
DEALLOCATE cTmp

-- 3.导数并删除记录（ 注意不能用 trucate table, 只能用 delete)
-- 危险操作，暂时注释
--DECLARE cTmp CURSOR local static read_only forward_only FOR    
--    SELECT name FROM sysobjects 
--    WHERE xtype= N'U' and name <> '_MyForeignKey'  -- 注意：必须排除[_MyForeignKey]表，否则无法恢复外键约束    
--    order by name
--OPEN cTmp  
--FETCH cTmp INTO @TableName  
--WHILE (@@FETCH_STATUS= 0)  
--BEGIN  
--    SET @SQLString='delete '+@TableName + ' '  
--    print @SQLString    
--    EXEC sp_executesql @SQLString  
--    
--    -- Next  
--    FETCH cTmp INTO @TableName  
--END  
--CLOSE cTmp  
--DEALLOCATE cTmp   

-- 4.恢复触发器及外键约束
DECLARE cTmp CURSOR local static read_only forward_only FOR 
   SELECT name FROM sysobjects WHERE xtype= N'U'
OPEN cTmp
FETCH cTmp INTO @TableName
WHILE (@@FETCH_STATUS= 0)
BEGIN
    SET @SQLString='ALTER TABLE '+@TableName+ ' ENABLE TRIGGER all'
    EXEC sp_executesql @SQLString
    -- Next
    FETCH cTmp INTO @TableName
END
CLOSE cTmp
DEALLOCATE cTmp

 -- 5.恢复FOREIGN KEY约束（即恢复要求“强制外键约束”的关系）
DECLARE cTmp CURSOR local static read_only forward_only FOR 
    SELECT TableName,ConstraintName
    FROM [_MyForeignKey]
OPEN cTmp
FETCH cTmp INTO @TableName, @ConstraintName
WHILE (@@FETCH_STATUS= 0)
BEGIN
    SET @SQLString='ALTER TABLE '+@TableName+ ' CHECK CONSTRAINT '+ @ConstraintName
    EXEC sp_executesql @SQLString
    -- Next
    FETCH cTmp INTO @TableName, @ConstraintName
END
CLOSE cTmp
DEALLOCATE cTmp

 -- 6.删除临时表
drop table[_MyForeignKey]

commit tran
SET XACT_ABORT OFF