(摘自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

一个脚本来管理它们:这个脚本将截断和删除命令与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

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

/****** 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

找到约束条件

SELECT * 
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('TABLE_NAME')

执行由此SQL生成的SQL

SELECT 
    'ALTER TABLE ' +  OBJECT_SCHEMA_NAME(parent_object_id) +
    '.[' + OBJECT_NAME(parent_object_id) + 
    '] DROP CONSTRAINT ' + name
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('TABLE_NAME')

西夫韦。

注意:增加了删除约束的解决方案，这样可以删除或修改表而没有任何约束错误。

最好的选择是DROP和CREATE外键约束。

我在这篇文章中没有找到对我“原样”工作的例子，如果外键引用不同的模式，一个不会工作，如果外键引用多列，另一个也不会工作。这个脚本同时考虑多个模式和每个外键的多个列。

下面是生成“ADD CONSTRAINT”语句的脚本，对于多列，它将用逗号分隔它们(确保在执行DROP语句之前保存此输出):

PRINT N'-- CREATE FOREIGN KEY CONSTRAINTS --';

SET NOCOUNT ON;
SELECT '
PRINT N''Creating '+ const.const_name +'...''
GO
ALTER TABLE ' + const.parent_obj + '
    ADD CONSTRAINT ' + const.const_name + ' FOREIGN KEY (
            ' + const.parent_col_csv + '
            ) REFERENCES ' + const.ref_obj + '(' + const.ref_col_csv + ')
GO'
FROM (
    SELECT QUOTENAME(fk.NAME) AS [const_name]
        ,QUOTENAME(schParent.NAME) + '.' + QUOTENAME(OBJECT_name(fkc.parent_object_id)) AS [parent_obj]
        ,STUFF((
                SELECT ',' + QUOTENAME(COL_NAME(fcP.parent_object_id, fcp.parent_column_id))
                FROM sys.foreign_key_columns AS fcP
                WHERE fcp.constraint_object_id = fk.object_id
                FOR XML path('')
                ), 1, 1, '') AS [parent_col_csv]
        ,QUOTENAME(schRef.NAME) + '.' + QUOTENAME(OBJECT_NAME(fkc.referenced_object_id)) AS [ref_obj]
        ,STUFF((
                SELECT ',' + QUOTENAME(COL_NAME(fcR.referenced_object_id, fcR.referenced_column_id))
                FROM sys.foreign_key_columns AS fcR
                WHERE fcR.constraint_object_id = fk.object_id
                FOR XML path('')
                ), 1, 1, '') AS [ref_col_csv]
    FROM sys.foreign_key_columns AS fkc
    INNER JOIN sys.foreign_keys AS fk ON fk.object_id = fkc.constraint_object_id
    INNER JOIN sys.objects AS oParent ON oParent.object_id = fkc.parent_object_id
    INNER JOIN sys.schemas AS schParent ON schParent.schema_id = oParent.schema_id
    INNER JOIN sys.objects AS oRef ON oRef.object_id = fkc.referenced_object_id
    INNER JOIN sys.schemas AS schRef ON schRef.schema_id = oRef.schema_id
    GROUP BY fkc.parent_object_id
        ,fkc.referenced_object_id
        ,fk.NAME
        ,fk.object_id
        ,schParent.NAME
        ,schRef.NAME
    ) AS const
ORDER BY const.const_name

下面是生成“DROP CONSTRAINT”语句的脚本:

PRINT N'-- DROP FOREIGN KEY CONSTRAINTS --';

SET NOCOUNT ON;

SELECT '
PRINT N''Dropping ' + fk.NAME + '...''
GO
ALTER TABLE [' + sch.NAME + '].[' + OBJECT_NAME(fk.parent_object_id) + ']' + ' DROP  CONSTRAINT ' + '[' + fk.NAME + ']
GO'
FROM sys.foreign_keys AS fk
INNER JOIN sys.schemas AS sch ON sch.schema_id = fk.schema_id
ORDER BY fk.NAME

右键单击表设计，进入“关系”，在左侧窗格中选择外键，在右侧窗格中，将强制外键约束设置为“Yes”(启用外键约束)或“No”(禁用外键约束)。