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

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

找到约束条件

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 and Recreate Foreign Key Constraints

SET NOCOUNT ON

DECLARE @table TABLE(
   RowId INT PRIMARY KEY IDENTITY(1, 1),
   ForeignKeyConstraintName NVARCHAR(200),
   ForeignKeyConstraintTableSchema NVARCHAR(200),
   ForeignKeyConstraintTableName NVARCHAR(200),
   ForeignKeyConstraintColumnName NVARCHAR(200),
   PrimaryKeyConstraintName NVARCHAR(200),
   PrimaryKeyConstraintTableSchema NVARCHAR(200),
   PrimaryKeyConstraintTableName NVARCHAR(200),
   PrimaryKeyConstraintColumnName NVARCHAR(200)    
)

INSERT INTO @table(ForeignKeyConstraintName, ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName, ForeignKeyConstraintColumnName)
SELECT 
   U.CONSTRAINT_NAME, 
   U.TABLE_SCHEMA, 
   U.TABLE_NAME, 
   U.COLUMN_NAME 
FROM 
   INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
      INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
         ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME
WHERE
   C.CONSTRAINT_TYPE = 'FOREIGN KEY'

UPDATE @table SET
   PrimaryKeyConstraintName = UNIQUE_CONSTRAINT_NAME
FROM 
   @table T
      INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R
         ON T.ForeignKeyConstraintName = R.CONSTRAINT_NAME

UPDATE @table SET
   PrimaryKeyConstraintTableSchema  = TABLE_SCHEMA,
   PrimaryKeyConstraintTableName  = TABLE_NAME
FROM @table T
   INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
      ON T.PrimaryKeyConstraintName = C.CONSTRAINT_NAME

UPDATE @table SET
   PrimaryKeyConstraintColumnName = COLUMN_NAME
FROM @table T
   INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
      ON T.PrimaryKeyConstraintName = U.CONSTRAINT_NAME

--SELECT * FROM @table

--DROP CONSTRAINT:
SELECT
   '
   ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + '] 
   DROP CONSTRAINT ' + ForeignKeyConstraintName + '

   GO'
FROM
   @table

--ADD CONSTRAINT:
SELECT
   '
   ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + '] 
   ADD CONSTRAINT ' + ForeignKeyConstraintName + ' FOREIGN KEY(' + ForeignKeyConstraintColumnName + ') REFERENCES [' + PrimaryKeyConstraintTableSchema + '].[' + PrimaryKeyConstraintTableName + '](' + PrimaryKeyConstraintColumnName + ')

   GO'
FROM
   @table

GO

我同意你的观点，哈姆林。当您使用SSIS传输数据或想要复制数据时，似乎很有必要暂时禁用或删除外键约束，然后重新启用或重新创建它们。在这些情况下，引用完整性不是问题，因为它已经在源数据库中进行了维护。所以，这件事你可以放心。

如果你想禁用数据库中的所有约束，只需运行以下代码:

-- disable all constraints
EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

要将它们重新打开，运行:(打印当然是可选的，它只是列出表)

-- enable all constraints
exec sp_MSforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

我发现它在将数据从一个数据库填充到另一个数据库时很有用。这是比放弃约束更好的方法。正如您所提到的，当删除数据库中的所有数据并重新填充它时(例如在测试环境中)，它非常方便。

如果您正在删除所有数据，您可能会发现这个解决方案是有用的。

有时禁用所有触发器也很方便，你可以在这里看到完整的解决方案。

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