(摘自http://www.sqljunkies.com/WebLog/roman/archive/2005/01/30/7037.aspx， 现在存档在时光倒流机中)

Foreign key constraints and check constraint are very useful for enforcing data integrity and business rules. There are certain scenarios though where it is useful to temporarily turn them off because their behavior is either not needed or could do more harm than good. I sometimes disable constraint checking on tables during data loads from external sources or when I need to script a table drop/recreate with reloading the data back into the table. I usually do it in scenarios where I don't want a time consuming process to fail because one or a few of many million rows have bad data in it. But I always turn the constraints back on once the process is finished and also in some cases I run data integrity checks on the imported data.

如果禁用外键约束，则可以插入父表中不存在的值。如果禁用检查约束，则可以将值放入列中，就像检查约束不存在一样。下面是禁用和启用表约束的一些例子:

——禁用所有表约束 修改表MyTable NOCHECK约束 ——启用所有表约束 修改MyTable，检查检查约束全部 ——禁用单个约束 修改表MyTable NOCHECK约束 ——启用单一约束 ALTER TABLE MyTable WITH CHECK CHECK CONSTRAINT MyConstraint

如果你感兴趣，我有一个更有用的版本。我从这里提取了一些代码，一个网站的链接不再活跃。我修改了它，以允许存储过程中有一个表数组，并在执行所有语句之前填充删除、截断和添加语句。这使您可以控制决定哪些表需要截断。

/****** Object:  UserDefinedTableType [util].[typ_objects_for_managing]    Script Date: 03/04/2016 16:42:55 ******/
CREATE TYPE [util].[typ_objects_for_managing] AS TABLE(
    [schema] [sysname] NOT NULL,
    [object] [sysname] NOT NULL
)
GO

create procedure [util].[truncate_table_with_constraints]
@objects_for_managing util.typ_objects_for_managing readonly

--@schema sysname
--,@table sysname

as 
--select
--    @table = 'TABLE',
--    @schema = 'SCHEMA'

declare @exec_table as table (ordinal int identity (1,1), statement nvarchar(4000), primary key (ordinal));

--print '/*Drop Foreign Key Statements for ['+@schema+'].['+@table+']*/'

insert into @exec_table (statement)
select
          'ALTER TABLE ['+SCHEMA_NAME(o.schema_id)+'].['+ o.name+'] DROP CONSTRAINT ['+fk.name+']'
from sys.foreign_keys fk
inner join sys.objects o
          on fk.parent_object_id = o.object_id
where 
exists ( 
select * from @objects_for_managing chk 
where 
chk.[schema] = SCHEMA_NAME(o.schema_id)  
and 
chk.[object] = o.name
) 
;
          --o.name = @table and
          --SCHEMA_NAME(o.schema_id)  = @schema

insert into @exec_table (statement) 
select
'TRUNCATE TABLE ' + src.[schema] + '.' + src.[object] 
from @objects_for_managing src
; 

--print '/*Create Foreign Key Statements for ['+@schema+'].['+@table+']*/'
insert into @exec_table (statement)
select 'ALTER TABLE ['+SCHEMA_NAME(o.schema_id)+'].['+o.name+'] ADD CONSTRAINT ['+fk.name+'] FOREIGN KEY (['+c.name+']) 
REFERENCES ['+SCHEMA_NAME(refob.schema_id)+'].['+refob.name+'](['+refcol.name+'])'
from sys.foreign_key_columns fkc
inner join sys.foreign_keys fk
          on fkc.constraint_object_id = fk.object_id
inner join sys.objects o
          on fk.parent_object_id = o.object_id
inner join sys.columns c
          on      fkc.parent_column_id = c.column_id and
                   o.object_id = c.object_id
inner join sys.objects refob
          on fkc.referenced_object_id = refob.object_id
inner join sys.columns refcol
          on fkc.referenced_column_id = refcol.column_id and
                   fkc.referenced_object_id = refcol.object_id
where 
exists ( 
select * from @objects_for_managing chk 
where 
chk.[schema] = SCHEMA_NAME(o.schema_id)  
and 
chk.[object] = o.name
) 
;

          --o.name = @table and
          --SCHEMA_NAME(o.schema_id)  = @schema



declare @looper int , @total_records int, @sql_exec nvarchar(4000)

select @looper = 1, @total_records = count(*) from @exec_table; 

while @looper <= @total_records 
begin

select @sql_exec = (select statement from @exec_table where ordinal =@looper)
exec sp_executesql @sql_exec 
print @sql_exec 
set @looper = @looper + 1
end

一个脚本来管理它们:这个脚本将截断和删除命令与sp_MSforeachtable结合在一起，这样您就可以避免删除和重新创建约束——只需指定需要删除而不是截断的表，为了便于度量，我还包含了一个额外的模式过滤器(在2008r2中测试)。

declare @schema nvarchar(max) = 'and Schema_Id=Schema_id(''Value'')'
declare @deletiontables nvarchar(max) = '(''TableA'',''TableB'')'
declare @truncateclause nvarchar(max) = @schema + ' and o.Name not in ' +  + @deletiontables;
declare @deleteclause nvarchar(max) = @schema + ' and o.Name in ' + @deletiontables;        

exec sp_MSforeachtable 'alter table ? nocheck constraint all', @whereand=@schema
exec sp_MSforeachtable 'truncate table ?', @whereand=@truncateclause
exec sp_MSforeachtable 'delete from ?', @whereand=@deleteclause
exec sp_MSforeachtable 'alter table ? with check check constraint all', @whereand=@schema

标记为“905”的答案看起来不错，但不能工作。

下面的方法对我很有效。不能禁用任何主键、唯一键或默认约束。事实上，如果“sp_helpconstraint”在status_enabled中显示“n/a”-意味着它不能被启用/禁用。

生成脚本为DISABLE

select 'ALTER TABLE ' + object_name(id) + ' NOCHECK CONSTRAINT [' + object_name(constid) + ']'
from sys.sysconstraints 
where status & 0x4813 = 0x813 order by object_name(id)

——生成脚本以启用

select 'ALTER TABLE ' + object_name(id) + ' CHECK CONSTRAINT [' + object_name(constid) + ']'
from sys.sysconstraints 
where status & 0x4813 = 0x813 order by object_name(id)

您可以暂时禁用表上的约束，进行工作，然后重新构建它们。

这里有一个简单的方法…

禁用所有索引，包括主键，这将禁用所有外键，然后重新启用主键，以便您可以使用它们…

DECLARE @sql AS NVARCHAR(max)=''
select @sql = @sql +
    'ALTER INDEX ALL ON [' + t.[name] + '] DISABLE;'+CHAR(13)
from  
    sys.tables t
where type='u'

select @sql = @sql +
    'ALTER INDEX ' + i.[name] + ' ON [' + t.[name] + '] REBUILD;'+CHAR(13)
from  
    sys.key_constraints i
join
    sys.tables t on i.parent_object_id=t.object_id
where
    i.type='PK'


exec dbo.sp_executesql @sql;
go

[做一些事情，比如加载数据]

然后重新启用和重建索引……

DECLARE @sql AS NVARCHAR(max)=''
select @sql = @sql +
    'ALTER INDEX ALL ON [' + t.[name] + '] REBUILD;'+CHAR(13)
from  
    sys.tables t
where type='u'

exec dbo.sp_executesql @sql;
go

实际上，你应该能够像暂时禁用其他约束一样禁用外键约束:

Alter table MyTable nocheck constraint FK_ForeignKeyConstraintName

只需确保在约束名称中列出的第一个表上禁用了约束。例如，如果我的外键约束是FK_LocationsEmployeesLocationIdEmployeeId，我想使用以下:

Alter table Locations nocheck constraint FK_LocationsEmployeesLocationIdEmployeeId

尽管违反这个约束会产生一个错误，这个错误不一定会将该表作为冲突的来源。