如何使用T-SQL暂时禁用外键约束？

在SQLServer中是否支持禁用和启用外键约束？或者我唯一的选择是drop然后re-#1约束？

717721 次浏览
小开

（从http://www.sqljunkies.com/WebLog/roman/archive/2005/01/30/7037.aspx复制，现在存档在Wayback机器里

外键约束和检查约束对于强制执行数据完整性和业务规则非常有用。尽管在某些情况下暂时关闭它们是有用的，因为它们的行为要么不需要，要么弊大于利。我有时会在从外部源加载数据期间禁用对表的约束检查，或者当我需要编写脚本删除/重新创建表并将数据重新加载回表中时。我通常在不想因为数百万行中的一行或几行中有坏数据而导致耗时的进程失败的情况下这样做。但我总是在流程完成后重新打开约束，并且在某些情况下，我对导入的数据运行数据完整性检查。

如果禁用外键约束，您将能够插入父表中不存在的值。如果禁用检查约束，您将能够将值放在列中，就好像检查约束不存在一样。以下是禁用和启用表约束的几个示例：

   -- Disable all table constraintsALTER TABLE MyTable NOCHECK CONSTRAINT ALL
-- Enable all table constraintsALTER TABLE MyTable WITH CHECK CHECK CONSTRAINT ALL    
-- Disable single constraint   
ALTER TABLE MyTable NOCHECK CONSTRAINT MyConstraint   
-- Enable single constraintALTER TABLE MyTable WITH CHECK CHECK CONSTRAINT MyConstraint
小开
最佳答案

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

-- disable all constraintsEXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

要重新打开它们，请运行：（打印当然是可选的，它只是列出表格）

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

我发现它在将数据从一个数据库填充到另一个数据库时很有用。这比删除约束要好得多。正如你提到的，当删除数据库中的所有数据并重新填充它时（例如在测试环境中），它会很方便。

如果您删除所有数据，您可能会发现这个解决方案很有帮助。

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

小开

SQL-92标准允许将常量声明为DEFERRABLE，以便它可以在事务范围内延迟（隐式或显式）。遗憾的是，SQLServer仍然缺少SQL-92功能。

对我来说，将约束更改为NOCHECK类似于动态更改数据库结构——删除约束当然是——并且需要避免一些事情（例如用户需要增加权限）。

小开 
   --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)SELECTU.CONSTRAINT_NAME,U.TABLE_SCHEMA,U.TABLE_NAME,U.COLUMN_NAMEFROMINFORMATION_SCHEMA.KEY_COLUMN_USAGE UINNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS CON U.CONSTRAINT_NAME = C.CONSTRAINT_NAMEWHEREC.CONSTRAINT_TYPE = 'FOREIGN KEY'
UPDATE @table SETPrimaryKeyConstraintName = UNIQUE_CONSTRAINT_NAMEFROM@table TINNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RON T.ForeignKeyConstraintName = R.CONSTRAINT_NAME
UPDATE @table SETPrimaryKeyConstraintTableSchema  = TABLE_SCHEMA,PrimaryKeyConstraintTableName  = TABLE_NAMEFROM @table TINNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS CON T.PrimaryKeyConstraintName = C.CONSTRAINT_NAME
UPDATE @table SETPrimaryKeyConstraintColumnName = COLUMN_NAMEFROM @table TINNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE UON 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

Hamlin，我同意你的观点。当你使用SSIS传输数据或想要复制数据时，似乎有必要暂时禁用或删除外键约束，然后重新启用或重新创建它们。在这种情况下，参照完整性不是问题，因为它已经在源数据库中维护。因此，关于此事，你可以放心。

小开 
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),UpdateRule NVARCHAR(100),DeleteRule NVARCHAR(100))
INSERT INTO @table(ForeignKeyConstraintName, ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName, ForeignKeyConstraintColumnName)SELECTU.CONSTRAINT_NAME,U.TABLE_SCHEMA,U.TABLE_NAME,U.COLUMN_NAMEFROMINFORMATION_SCHEMA.KEY_COLUMN_USAGE UINNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS CON U.CONSTRAINT_NAME = C.CONSTRAINT_NAMEWHEREC.CONSTRAINT_TYPE = 'FOREIGN KEY'
UPDATE @table SETT.PrimaryKeyConstraintName = R.UNIQUE_CONSTRAINT_NAME,T.UpdateRule = R.UPDATE_RULE,T.DeleteRule = R.DELETE_RULEFROM@table TINNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RON T.ForeignKeyConstraintName = R.CONSTRAINT_NAME
UPDATE @table SETPrimaryKeyConstraintTableSchema  = TABLE_SCHEMA,PrimaryKeyConstraintTableName  = TABLE_NAMEFROM @table TINNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS CON T.PrimaryKeyConstraintName = C.CONSTRAINT_NAME
UPDATE @table SETPrimaryKeyConstraintColumnName = COLUMN_NAMEFROM @table TINNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE UON T.PrimaryKeyConstraintName = U.CONSTRAINT_NAME
--SELECT * FROM @table
SELECT 'BEGIN TRANSACTIONBEGIN TRY'
--DROP CONSTRAINT:SELECT'ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + ']DROP CONSTRAINT ' + ForeignKeyConstraintName + ''FROM@table
SELECT 'END TRY
BEGIN CATCHROLLBACK TRANSACTIONRAISERROR(''Operation failed.'', 16, 1)END CATCH
IF(@@TRANCOUNT != 0)BEGINCOMMIT TRANSACTIONRAISERROR(''Operation completed successfully.'', 10, 1)END'
--ADD CONSTRAINT:SELECT 'BEGIN TRANSACTIONBEGIN TRY'
SELECT'ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + ']ADD CONSTRAINT ' + ForeignKeyConstraintName + ' FOREIGN KEY(' + ForeignKeyConstraintColumnName + ') REFERENCES [' + PrimaryKeyConstraintTableSchema + '].[' + PrimaryKeyConstraintTableName + '](' + PrimaryKeyConstraintColumnName + ') ON UPDATE ' + UpdateRule + ' ON DELETE ' + DeleteRule + ''FROM@table
SELECT 'END TRY
BEGIN CATCHROLLBACK TRANSACTIONRAISERROR(''Operation failed.'', 16, 1)END CATCH
IF(@@TRANCOUNT != 0)BEGINCOMMIT TRANSACTIONRAISERROR(''Operation completed successfully.'', 10, 1)END'
GO
小开

要禁用约束，您必须使用NOCHECK对表进行ALTER

ALTER TABLE [TABLE_NAME] NOCHECK CONSTRAINT [ALL|CONSTRAINT_NAME]

要使您必须使用双检查：

ALTER TABLE [TABLE_NAME] WITH CHECK CHECK CONSTRAINT [ALL|CONSTRAINT_NAME]
  • 启用时注意双检查检查。
  • ALL表示表中的所有约束。

完成后，如果您需要检查状态，请使用此脚本列出约束状态。将非常有帮助：

    SELECT (CASEWHEN OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0 THEN 'ENABLED'ELSE 'DISABLED'END) AS STATUS,OBJECT_NAME(CONSTID) AS CONSTRAINT_NAME,OBJECT_NAME(FKEYID) AS TABLE_NAME,COL_NAME(FKEYID, FKEY) AS COLUMN_NAME,OBJECT_NAME(RKEYID) AS REFERENCED_TABLE_NAME,COL_NAME(RKEYID, RKEY) AS REFERENCED_COLUMN_NAMEFROM SYSFOREIGNKEYSORDER BY TABLE_NAME, CONSTRAINT_NAME,REFERENCED_TABLE_NAME, KEYNO
小开

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

我在这篇文章中没有找到适合我的“原样”示例，如果外键引用不同的模式，一个将不起作用，如果外键引用多列，另一个将不起作用。此脚本同时考虑多个模式和每个外键的多列。

这是生成“ADD CONSTRAINT”语句的脚本，对于多个列，它将用逗号（确保在执行DROP语句之前保存此输出）分隔它们：

PRINT N'-- CREATE FOREIGN KEY CONSTRAINTS --';
SET NOCOUNT ON;SELECT 'PRINT N''Creating '+ const.const_name +'...''GOALTER 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 fcPWHERE fcp.constraint_object_id = fk.object_idFOR 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 fcRWHERE fcR.constraint_object_id = fk.object_idFOR XML path('')), 1, 1, '') AS [ref_col_csv]FROM sys.foreign_key_columns AS fkcINNER JOIN sys.foreign_keys AS fk ON fk.object_id = fkc.constraint_object_idINNER JOIN sys.objects AS oParent ON oParent.object_id = fkc.parent_object_idINNER JOIN sys.schemas AS schParent ON schParent.schema_id = oParent.schema_idINNER JOIN sys.objects AS oRef ON oRef.object_id = fkc.referenced_object_idINNER JOIN sys.schemas AS schRef ON schRef.schema_id = oRef.schema_idGROUP BY fkc.parent_object_id,fkc.referenced_object_id,fk.NAME,fk.object_id,schParent.NAME,schRef.NAME) AS constORDER BY const.const_name

以下是生成“DROP CONSTRAINT”语句的脚本：

PRINT N'-- DROP FOREIGN KEY CONSTRAINTS --';
SET NOCOUNT ON;
SELECT 'PRINT N''Dropping ' + fk.NAME + '...''GOALTER TABLE [' + sch.NAME + '].[' + OBJECT_NAME(fk.parent_object_id) + ']' + ' DROP  CONSTRAINT ' + '[' + fk.NAME + ']GO'FROM sys.foreign_keys AS fkINNER JOIN sys.schemas AS sch ON sch.schema_id = fk.schema_idORDER BY fk.NAME
小开

找到约束

SELECT *FROM sys.foreign_keysWHERE 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 ' + nameFROM sys.foreign_keysWHERE referenced_object_id = object_id('TABLE_NAME')

西夫韦。

注意：添加了删除约束的解决方案，以便可以删除或修改表而不会出现任何约束错误。

小开

第一篇文章：）

对于OP，kristof的解决方案将有效，除非在大删除上存在大量数据和事务日志气球问题。此外，即使有tlog存储空间，由于删除写入tlog，对于具有数亿行的表，操作可能需要非常长的时间。

我经常使用一系列游标来截断和重新加载我们一个巨大的生产数据库的大型副本。该解决方案设计了多个模式、多个外键列，最重要的是可以在SSIS中使用。

它涉及创建三个暂存表（真实表）来容纳DROP、CREATE和CHECK FK脚本，创建这些脚本并将其插入表中，然后循环遍历表并执行它们。附加的脚本包括四个部分：1.）三个暂存（真实）表中脚本的创建和存储，2.）通过光标逐个执行拖放FK脚本，3.）使用sp_MSforeachtable截断数据库中除三个暂存表之外的所有表，以及4.）在ETL SSIS包的末尾执行创建FK和检查FK脚本。

在SSIS中的执行SQL任务中运行脚本创建部分。在第二个执行SQL任务中运行“执行删除FK脚本”部分。将截断脚本放在第三个执行SQL任务中，然后在将CREATE和CHECK脚本附加到控制流末尾的最终执行SQL任务（如果需要，可以执行两个）之前执行所需的任何其他ETL进程。

事实证明，当重新应用外键失败时，在真实表中存储脚本是非常宝贵的，因为您可以从sync_CreateFK中选择*，复制/粘贴到查询窗口中，一次运行一个，并在发现失败/仍然无法重新应用的数据问题后修复数据问题。

如果脚本失败，请不要再次重新运行脚本，而不确保在此之前重新应用所有外键/检查，否则您很可能会丢失一些创建并检查fk脚本，因为我们的暂存表在创建要执行的脚本之前被删除并重新创建。

----------------------------------------------------------------------------1)/*Author:         DenmachDateCreated:    2014-04-23Purpose:        Generates SQL statements to DROP, ADD, and CHECK existing constraints for adatabase.  Stores scripts in tables on target database for execution.  Executesthose stored scripts via independent cursors.DateModified:ModifiedByComments:       This will eliminate deletes and the T-log ballooning associated with it.*/
DECLARE @schema_name SYSNAME;DECLARE @table_name SYSNAME;DECLARE @constraint_name SYSNAME;DECLARE @constraint_object_id INT;DECLARE @referenced_object_name SYSNAME;DECLARE @is_disabled BIT;DECLARE @is_not_for_replication BIT;DECLARE @is_not_trusted BIT;DECLARE @delete_referential_action TINYINT;DECLARE @update_referential_action TINYINT;DECLARE @tsql NVARCHAR(4000);DECLARE @tsql2 NVARCHAR(4000);DECLARE @fkCol SYSNAME;DECLARE @pkCol SYSNAME;DECLARE @col1 BIT;DECLARE @action CHAR(6);DECLARE @referenced_schema_name SYSNAME;


--------------------------------Generate scripts to drop all foreign keys in a database --------------------------------
IF OBJECT_ID('dbo.sync_dropFK') IS NOT NULLDROP TABLE sync_dropFK
CREATE TABLE sync_dropFK(ID INT IDENTITY (1,1) NOT NULL, Script NVARCHAR(4000))
DECLARE FKcursor CURSOR FOR
SELECTOBJECT_SCHEMA_NAME(parent_object_id), OBJECT_NAME(parent_object_id), nameFROMsys.foreign_keys WITH (NOLOCK)ORDER BY1,2;
OPEN FKcursor;
FETCH NEXT FROM FKcursor INTO@schema_name, @table_name, @constraint_name
WHILE @@FETCH_STATUS = 0
BEGINSET @tsql = 'ALTER TABLE '+ QUOTENAME(@schema_name)+ '.'+ QUOTENAME(@table_name)+ ' DROP CONSTRAINT '+ QUOTENAME(@constraint_name)+ ';';--PRINT @tsql;INSERT sync_dropFK  (Script)VALUES (@tsql)
FETCH NEXT FROM FKcursor INTO@schema_name, @table_name, @constraint_name;
END;
CLOSE FKcursor;
DEALLOCATE FKcursor;

---------------Generate scripts to create all existing foreign keys in a database ------------------------------------------------------------------------------------------------------------------------------------------IF OBJECT_ID('dbo.sync_createFK') IS NOT NULLDROP TABLE sync_createFK
CREATE TABLE sync_createFK(ID INT IDENTITY (1,1) NOT NULL, Script NVARCHAR(4000))
IF OBJECT_ID('dbo.sync_createCHECK') IS NOT NULLDROP TABLE sync_createCHECK
CREATE TABLE sync_createCHECK(ID INT IDENTITY (1,1) NOT NULL, Script NVARCHAR(4000))
DECLARE FKcursor CURSOR FOR
SELECTOBJECT_SCHEMA_NAME(parent_object_id), OBJECT_NAME(parent_object_id), name, OBJECT_NAME(referenced_object_id), OBJECT_ID, is_disabled, is_not_for_replication, is_not_trusted, delete_referential_action, update_referential_action, OBJECT_SCHEMA_NAME(referenced_object_id)
FROMsys.foreign_keys WITH (NOLOCK)
ORDER BY1,2;
OPEN FKcursor;
FETCH NEXT FROM FKcursor INTO@schema_name, @table_name, @constraint_name, @referenced_object_name, @constraint_object_id, @is_disabled, @is_not_for_replication, @is_not_trusted, @delete_referential_action, @update_referential_action, @referenced_schema_name;
WHILE @@FETCH_STATUS = 0
BEGIN
BEGINSET @tsql = 'ALTER TABLE '+ QUOTENAME(@schema_name)+ '.'+ QUOTENAME(@table_name)+   CASE@is_not_trustedWHEN 0 THEN ' WITH CHECK 'ELSE ' WITH NOCHECK 'END+ ' ADD CONSTRAINT '+ QUOTENAME(@constraint_name)+ ' FOREIGN KEY (';
SET @tsql2 = '';
DECLARE ColumnCursor CURSOR FOR
SELECTCOL_NAME(fk.parent_object_id, fkc.parent_column_id), COL_NAME(fk.referenced_object_id, fkc.referenced_column_id)
FROMsys.foreign_keys fk WITH (NOLOCK)INNER JOIN sys.foreign_key_columns fkc WITH (NOLOCK) ON fk.[object_id] = fkc.constraint_object_id
WHEREfkc.constraint_object_id = @constraint_object_id
ORDER BYfkc.constraint_column_id;
OPEN ColumnCursor;
SET @col1 = 1;
FETCH NEXT FROM ColumnCursor INTO @fkCol, @pkCol;
WHILE @@FETCH_STATUS = 0
BEGINIF (@col1 = 1)SET @col1 = 0;ELSEBEGINSET @tsql = @tsql + ',';SET @tsql2 = @tsql2 + ',';END;
SET @tsql = @tsql + QUOTENAME(@fkCol);SET @tsql2 = @tsql2 + QUOTENAME(@pkCol);--PRINT '@tsql = ' + @tsql--PRINT '@tsql2 = ' + @tsql2FETCH NEXT FROM ColumnCursor INTO @fkCol, @pkCol;--PRINT 'FK Column ' + @fkCol--PRINT 'PK Column ' + @pkColEND;
CLOSE ColumnCursor;DEALLOCATE ColumnCursor;
SET @tsql = @tsql + ' ) REFERENCES '+ QUOTENAME(@referenced_schema_name)+ '.'+ QUOTENAME(@referenced_object_name)+ ' ('+ @tsql2 + ')';
SET @tsql = @tsql+ ' ON UPDATE '+CASE @update_referential_actionWHEN 0 THEN 'NO ACTION 'WHEN 1 THEN 'CASCADE 'WHEN 2 THEN 'SET NULL 'ELSE 'SET DEFAULT 'END
+ ' ON DELETE '+CASE @delete_referential_actionWHEN 0 THEN 'NO ACTION 'WHEN 1 THEN 'CASCADE 'WHEN 2 THEN 'SET NULL 'ELSE 'SET DEFAULT 'END
+CASE @is_not_for_replicationWHEN 1 THEN ' NOT FOR REPLICATION 'ELSE ''END+ ';';
END;
--  PRINT @tsqlINSERT sync_createFK(Script)VALUES (@tsql)
-------------------Generate CHECK CONSTRAINT scripts for a database ----------------------------------------------------------------------------------------------------------------------------------------
BEGIN
SET @tsql = 'ALTER TABLE '+ QUOTENAME(@schema_name)+ '.'+ QUOTENAME(@table_name)+CASE @is_disabledWHEN 0 THEN ' CHECK 'ELSE ' NOCHECK 'END+ 'CONSTRAINT '+ QUOTENAME(@constraint_name)+ ';';--PRINT @tsql;INSERT sync_createCHECK(Script)VALUES (@tsql)END;
FETCH NEXT FROM FKcursor INTO@schema_name, @table_name, @constraint_name, @referenced_object_name, @constraint_object_id, @is_disabled, @is_not_for_replication, @is_not_trusted, @delete_referential_action, @update_referential_action, @referenced_schema_name;
END;
CLOSE FKcursor;
DEALLOCATE FKcursor;
--SELECT * FROM sync_DropFK--SELECT * FROM sync_CreateFK--SELECT * FROM sync_CreateCHECK---------------------------------------------------------------------------2.)---------------------------------------------------------------------------------------------------------------------------------------------execute Drop FK Scripts --------------------------------------------------
DECLARE @scriptD NVARCHAR(4000)
DECLARE DropFKCursor CURSOR FORSELECT ScriptFROM sync_dropFK WITH (NOLOCK)
OPEN DropFKCursor
FETCH NEXT FROM DropFKCursorINTO @scriptD
WHILE @@FETCH_STATUS = 0BEGIN--PRINT @scriptDEXEC (@scriptD)FETCH NEXT FROM DropFKCursorINTO @scriptDENDCLOSE DropFKCursorDEALLOCATE DropFKCursor--------------------------------------------------------------------------------3.)
----------------------------------------------------------------------------------------------------------------------------------------------Truncate all tables in the database other than our staging tables --------------------------------------------------------------------------------------------------------------------------------------

EXEC sp_MSforeachtable 'IF OBJECT_ID(''?'') NOT IN(ISNULL(OBJECT_ID(''dbo.sync_createCHECK''),0),ISNULL(OBJECT_ID(''dbo.sync_createFK''),0),ISNULL(OBJECT_ID(''dbo.sync_dropFK''),0))BEGIN TRYTRUNCATE TABLE ?END TRYBEGIN CATCHPRINT ''Truncation failed on''+ ? +''END CATCH;'GO------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------execute Create FK Scripts and CHECK CONSTRAINT Scripts-------------------------------------------tack me at the end of the ETL in a SQL task--------------------------------------------------------------------------------------------------------------------------DECLARE @scriptC NVARCHAR(4000)
DECLARE CreateFKCursor CURSOR FORSELECT ScriptFROM sync_createFK WITH (NOLOCK)
OPEN CreateFKCursor
FETCH NEXT FROM CreateFKCursorINTO @scriptC
WHILE @@FETCH_STATUS = 0BEGIN--PRINT @scriptCEXEC (@scriptC)FETCH NEXT FROM CreateFKCursorINTO @scriptCENDCLOSE CreateFKCursorDEALLOCATE CreateFKCursor-------------------------------------------------------------------------------------------------DECLARE @scriptCh NVARCHAR(4000)
DECLARE CreateCHECKCursor CURSOR FORSELECT ScriptFROM sync_createCHECK WITH (NOLOCK)
OPEN CreateCHECKCursor
FETCH NEXT FROM CreateCHECKCursorINTO @scriptCh
WHILE @@FETCH_STATUS = 0BEGIN--PRINT @scriptChEXEC (@scriptCh)FETCH NEXT FROM CreateCHECKCursorINTO @scriptChENDCLOSE CreateCHECKCursorDEALLOCATE CreateCHECKCursor
小开

WITH CHECK CHECK几乎肯定是必需的！

在一些回答和评论中提出了这一点，但我认为再次提出这一点非常重要。

使用以下命令（否WITH CHECK）重新启用约束将具有一些严重缺点。

ALTER TABLE MyTable CHECK CONSTRAINT MyConstraint;

带支票|带NOCHECK

指定表中的数据是否经过验证新添加或重新启用的FOREIGN KEY或CHECK约束。如果不是指定，新约束假定为WHICHECK，而WHINOCHECK假设重新启用约束。

如果您不想验证新的CHECK或FOREIGN KEY约束针对存量数据，使用with NOCHECK。我们不建议这样做这，除非在极少数情况下。新的约束将在所有以后的数据更新。任何被抑制的约束违规当添加约束时，可能会导致将来的更新如果他们使用不符合限制。

查询优化器不考虑定义的约束使用NOCHECK。此类约束被忽略，直到它们被重新启用通过使用ALTER TABLE table with CHECK CHECK CONSTRAINT ALL。

备注：的NOCHECK是重新启用约束的默认值。我想知道为什么…

  1. 在执行此命令期间，不会评估表中的存量数据-成功完成不能保证表中的数据根据约束有效。
  2. 在无效记录的下一次更新期间，约束将被评估并失败-导致可能与实际更新无关的错误。
  3. 依赖约束来确保数据有效的应用程序逻辑可能会失败。
  4. 查询优化器不会使用以这种方式启用的任何约束。

sys.foreign_keys系统视图提供了对问题的一些可见性。请注意，它同时具有is_disabled和is_not_trusted列。is_disabled表示是否将针对约束验证未来的数据操作。is_not_trusted表示表中当前的所有数据是否都已针对约束验证。

ALTER TABLE MyTable WITH CHECK CHECK CONSTRAINT MyConstraint;

你的约束是可信的吗？找出…

SELECT * FROM sys.foreign_keys WHERE is_not_trusted = 1;
小开

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

/****** 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 fkinner join sys.objects oon fk.parent_object_id = o.object_idwhereexists (select * from @objects_for_managing chkwherechk.[schema] = SCHEMA_NAME(o.schema_id)andchk.[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 fkcinner join sys.foreign_keys fkon fkc.constraint_object_id = fk.object_idinner join sys.objects oon fk.parent_object_id = o.object_idinner join sys.columns con      fkc.parent_column_id = c.column_id ando.object_id = c.object_idinner join sys.objects refobon fkc.referenced_object_id = refob.object_idinner join sys.columns refcolon fkc.referenced_column_id = refcol.column_id andfkc.referenced_object_id = refcol.object_idwhereexists (select * from @objects_for_managing chkwherechk.[schema] = SCHEMA_NAME(o.schema_id)andchk.[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_recordsbegin
select @sql_exec = (select statement from @exec_table where ordinal =@looper)exec sp_executesql @sql_execprint @sql_execset @looper = @looper + 1end
小开

右键单击表设计并转到关系，然后在左侧窗格中选择外键，在右侧窗格中，将Enforce外键约束设置为“Yes”（启用外键约束）或“No”（禁用它）。输入图片描述

小开

一个脚本来管理它们：这将截断和删除命令与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=@schemaexec sp_MSforeachtable 'truncate table ?', @whereand=@truncateclauseexec sp_MSforeachtable 'delete from ?', @whereand=@deleteclauseexec sp_MSforeachtable 'alter table ? with check check constraint all', @whereand=@schema
小开

标有“905”的答案看起来不错，但不起作用。

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

--生成脚本以禁用

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

--生成脚本以启用

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

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

Alter table MyTable nocheck constraint FK_ForeignKeyConstraintName

只要确保您在约束名称中列出的第一个表上禁用约束。例如，如果我的外键约束FK_LocationsEmployeesLocationIdEmployeeId，我想使用以下内容：

Alter table Locations nocheck constraint FK_LocationsEmployeesLocationIdEmployeeId

即使违反此约束将产生不一定将该表声明为冲突源的错误。

小开

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

这里有一个简单的方法来做到这一点…

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

DECLARE @sql AS NVARCHAR(max)=''select @sql = @sql +'ALTER INDEX ALL ON [' + t.[name] + '] DISABLE;'+CHAR(13)fromsys.tables twhere type='u'
select @sql = @sql +'ALTER INDEX ' + i.[name] + ' ON [' + t.[name] + '] REBUILD;'+CHAR(13)fromsys.key_constraints ijoinsys.tables t on i.parent_object_id=t.object_idwherei.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)fromsys.tables twhere type='u'
exec dbo.sp_executesql @sql;go
小开

您可以使用以下命令轻松打开CONSTRAINT：ALTER TABLE TableName NOCHECK CONSTRAINT ALL

删除所有限制

完成交易后，不要忘记使用以下命令再次打开它们：ALTER TABLE TableName CHECK CONSTRAINT ALL

所有数据表的限制

提交答案