-- Disable all table constraints
ALTER TABLE MyTable NOCHECK CONSTRAINT ALL
-- Enable all table constraints
ALTER TABLE MyTable WITH CHECK CHECK CONSTRAINT ALL
-- Disable single constraint
ALTER TABLE MyTable NOCHECK CONSTRAINT MyConstraint
-- Enable single constraint
ALTER TABLE MyTable WITH CHECK CHECK CONSTRAINT MyConstraint
这是生成"ADD CONSTRAINT"语句的脚本，对于多个列，它将用逗号分隔它们（ be sure to save this output before executing DROP statements ）：
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
ALTER TABLE [TABLE_NAME] WITH CHECK CHECK CONSTRAINT [ALL|CONSTRAINT_NAME]
启用时注意双 CHECK CHECK .
ALL表示表中的所有约束 .
完成后，如果需要检查状态，请使用此脚本列出约束状态 . 会非常有帮助：
SELECT (CASE
WHEN 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_NAME
FROM SYSFOREIGNKEYS
ORDER BY TABLE_NAME, CONSTRAINT_NAME,REFERENCED_TABLE_NAME, KEYNO
在SSIS中的“执行SQL”任务中运行脚本创建部分 . 在第二个执行SQL任务中运行“execute Drop FK Scripts”部分 . 将截断脚本放在第三个执行SQL任务中，然后在控制流结束时将CREATE和CHECK脚本附加到最终的执行SQL任务（或两个，如果需要）之前，执行您需要执行的任何其他ETL过程 .
----------------------------------------------------------------------------
1)
/*
Author: Denmach
DateCreated: 2014-04-23
Purpose: Generates SQL statements to DROP, ADD, and CHECK existing constraints for a
database. Stores scripts in tables on target database for execution. Executes
those stored scripts via independent cursors.
DateModified:
ModifiedBy
Comments: 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 NULL
DROP TABLE sync_dropFK
CREATE TABLE sync_dropFK
(
ID INT IDENTITY (1,1) NOT NULL
, Script NVARCHAR(4000)
)
DECLARE FKcursor CURSOR FOR
SELECT
OBJECT_SCHEMA_NAME(parent_object_id)
, OBJECT_NAME(parent_object_id)
, name
FROM
sys.foreign_keys WITH (NOLOCK)
ORDER BY
1,2;
OPEN FKcursor;
FETCH NEXT FROM FKcursor INTO
@schema_name
, @table_name
, @constraint_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @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 NULL
DROP 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 NULL
DROP TABLE sync_createCHECK
CREATE TABLE sync_createCHECK
(
ID INT IDENTITY (1,1) NOT NULL
, Script NVARCHAR(4000)
)
DECLARE FKcursor CURSOR FOR
SELECT
OBJECT_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)
FROM
sys.foreign_keys WITH (NOLOCK)
ORDER BY
1,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
BEGIN
SET @tsql = 'ALTER TABLE '
+ QUOTENAME(@schema_name)
+ '.'
+ QUOTENAME(@table_name)
+ CASE
@is_not_trusted
WHEN 0 THEN ' WITH CHECK '
ELSE ' WITH NOCHECK '
END
+ ' ADD CONSTRAINT '
+ QUOTENAME(@constraint_name)
+ ' FOREIGN KEY (';
SET @tsql2 = '';
DECLARE ColumnCursor CURSOR FOR
SELECT
COL_NAME(fk.parent_object_id
, fkc.parent_column_id)
, COL_NAME(fk.referenced_object_id
, fkc.referenced_column_id)
FROM
sys.foreign_keys fk WITH (NOLOCK)
INNER JOIN sys.foreign_key_columns fkc WITH (NOLOCK) ON fk.[object_id] = fkc.constraint_object_id
WHERE
fkc.constraint_object_id = @constraint_object_id
ORDER BY
fkc.constraint_column_id;
OPEN ColumnCursor;
SET @col1 = 1;
FETCH NEXT FROM ColumnCursor INTO @fkCol, @pkCol;
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@col1 = 1)
SET @col1 = 0;
ELSE
BEGIN
SET @tsql = @tsql + ',';
SET @tsql2 = @tsql2 + ',';
END;
SET @tsql = @tsql + QUOTENAME(@fkCol);
SET @tsql2 = @tsql2 + QUOTENAME(@pkCol);
--PRINT '@tsql = ' + @tsql
--PRINT '@tsql2 = ' + @tsql2
FETCH NEXT FROM ColumnCursor INTO @fkCol, @pkCol;
--PRINT 'FK Column ' + @fkCol
--PRINT 'PK Column ' + @pkCol
END;
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_action
WHEN 0 THEN 'NO ACTION '
WHEN 1 THEN 'CASCADE '
WHEN 2 THEN 'SET NULL '
ELSE 'SET DEFAULT '
END
+ ' ON DELETE '
+
CASE @delete_referential_action
WHEN 0 THEN 'NO ACTION '
WHEN 1 THEN 'CASCADE '
WHEN 2 THEN 'SET NULL '
ELSE 'SET DEFAULT '
END
+
CASE @is_not_for_replication
WHEN 1 THEN ' NOT FOR REPLICATION '
ELSE ''
END
+ ';';
END;
-- PRINT @tsql
INSERT 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_disabled
WHEN 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 FOR
SELECT Script
FROM sync_dropFK WITH (NOLOCK)
OPEN DropFKCursor
FETCH NEXT FROM DropFKCursor
INTO @scriptD
WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT @scriptD
EXEC (@scriptD)
FETCH NEXT FROM DropFKCursor
INTO @scriptD
END
CLOSE DropFKCursor
DEALLOCATE 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 TRY
TRUNCATE TABLE ?
END TRY
BEGIN CATCH
PRINT ''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 FOR
SELECT Script
FROM sync_createFK WITH (NOLOCK)
OPEN CreateFKCursor
FETCH NEXT FROM CreateFKCursor
INTO @scriptC
WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT @scriptC
EXEC (@scriptC)
FETCH NEXT FROM CreateFKCursor
INTO @scriptC
END
CLOSE CreateFKCursor
DEALLOCATE CreateFKCursor
-------------------------------------------------------------------------------------------------
DECLARE @scriptCh NVARCHAR(4000)
DECLARE CreateCHECKCursor CURSOR FOR
SELECT Script
FROM sync_createCHECK WITH (NOLOCK)
OPEN CreateCHECKCursor
FETCH NEXT FROM CreateCHECKCursor
INTO @scriptCh
WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT @scriptCh
EXEC (@scriptCh)
FETCH NEXT FROM CreateCHECKCursor
INTO @scriptCh
END
CLOSE CreateCHECKCursor
DEALLOCATE CreateCHECKCursor
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
16 回答
如果要禁用数据库中的所有约束，只需运行以下代码：
要重新打开它们，请运行:(打印是可选的，当然只是列出表格）
我发现在将数据从一个数据库填充到另一个数据库时很有用 . 这比删除约束要好得多 . 正如您所提到的，在将所有数据放入数据库并重新填充数据库时（例如在测试环境中），它非常方便 .
如果您要删除所有数据，您可能会发现this solution有帮助 .
有时也可以方便地禁用所有触发器，您可以看到完整的解决方案here .
http://www.sqljunkies.com/WebLog/roman/archive/2005/01/30/7037.aspx
您最好的选择是DROP和CREATE外键约束 .
我没有在这篇文章中找到可以“按原样”为我工作的例子，如果外键引用不同的模式，则无法工作，如果外键引用多个列，则另一个不起作用 . 此脚本同时考虑每个外键的多个模式和多个列 .
这是生成"ADD CONSTRAINT"语句的脚本，对于多个列，它将用逗号分隔它们（ be sure to save this output before executing DROP statements ）：
以下是生成“DROP CONSTRAINT”语句的脚本：
要禁用约束，您可以使用NOCHECK更改表格
要使你必须使用双CHECK
启用时注意双 CHECK CHECK .
ALL表示表中的所有约束 .
完成后，如果需要检查状态，请使用此脚本列出约束状态 . 会非常有帮助：
SQL-92标准允许将constaint声明为DEFERRABLE，以便可以在事务范围内延迟（隐式或显式） . 遗憾的是，SQL Server仍然缺少这种SQL-92功能 .
对我来说，将约束更改为NOCHECK类似于在飞行中更改数据库结构 - 确实是 - 并且需要避免的事情（例如用户需要增加的权限） .
哈林，我同意你的看法 . 当您使用SSIS传输数据或想要复制数据时，似乎必须暂时禁用或删除外键约束，然后重新启用或重新创建它们 . 在这些情况下，引用完整性不是问题，因为它已在源数据库中维护 . 因此，您可以放心这件事 .
第一篇文章:)
对于OP，kristof的解决方案将起作用，除非在大删除上存在大量数据和事务日志气球问题 . 此外，即使使用tlog存储，由于删除写入tlog，对于具有数亿行的表，操作可能需要很长时间 .
我使用一系列游标来频繁截断和重新加载我们庞大的 生产环境 数据库的大型副本 . 该解决方案设计了多个模式，多个外键列，最重要的是可以在SSIS中使用 .
它涉及创建三个临时表（实际表）以容纳DROP，CREATE和CHECK FK脚本，创建这些脚本并将其插入表中，然后循环遍历表并执行它们 . 附加的脚本分为四个部分：1 . ）在三个临时（真实）表中创建和存储脚本，2 . 通过游标逐个执行drop FK脚本，3 . ）使用sp_MSforeachtable截断所有除了我们的三个临时表之外的数据库中的表和4.）执行create FK并检查ETL SSIS包末尾的FK脚本 .
在SSIS中的“执行SQL”任务中运行脚本创建部分 . 在第二个执行SQL任务中运行“execute Drop FK Scripts”部分 . 将截断脚本放在第三个执行SQL任务中，然后在控制流结束时将CREATE和CHECK脚本附加到最终的执行SQL任务（或两个，如果需要）之前，执行您需要执行的任何其他ETL过程 .
当重新应用外键失败时，实际表中脚本的存储已被证明是非常宝贵的，因为您可以从sync_CreateFK中选择*，复制/粘贴到查询窗口，一次运行一个，并在您修复数据问题后找到失败/仍然无法重新申请的人 .
如果失败而没有确保在执行此操作之前重新应用所有外键/检查，请不要再次重新运行脚本，否则您很可能会丢失一些创建并检查fk脚本，因为我们的登台表已被删除在创建要执行的脚本之前重新创建 .
WITH CHECK CHECK is almost certainly required!
在一些答案和评论中提出了这一点，但我觉得打电话很重要它再次出来 .
使用以下命令重新启用约束（无
WITH CHECK）将具有一些serious drawbacks .Note: WITH NOCHECK是重新启用约束的默认值 . 我不知道为什么......
在执行此命令期间，不会评估表中的现有数据 - 成功完成并不能保证表中的数据根据约束有效 .
在下次更新无效记录期间，将评估约束并将失败 - 导致可能与实际更新无关的错误 .
依赖约束来确保数据有效的应用程序逻辑可能会失败 .
查询优化器不会使用以这种方式启用的任何约束 .
sys.foreign_keys系统视图提供了对该问题的一些可见性 . 请注意，它同时具有
is_disabled和is_not_trusted列 .is_disabled表示是否将根据约束验证未来的数据操作操作 .is_not_trusted指示是否已根据约束验证表中当前的所有数据 .您的约束是否值得信任？找出...
找到约束
执行此SQL生成的SQL
Safeway公司 .
Note: Added solution for droping the constraint so that table can be dropped or modified without any constraint error.
右键单击表设计并转到“关系”并在左侧窗格和右侧窗格中选择外键，将“强制外键约束”设置为'Yes'（以启用外键约束）或'No'（禁用它） .
您实际上应该能够以暂时禁用其他约束的方式禁用外键约束：
只需确保禁用约束名称中列出的第一个表上的约束 . 例如，如果我的外键约束是FK_LocationsEmployeesLocationIdEmployeeId，我想使用以下内容：
即使违反此约束也会产生错误，该错误不一定表明该表是冲突的来源 .
如果你有兴趣，我有一个更有用的版本 . 我从这里提取了一些代码http://www.dbaservices.com.au/news/drop-recreate-foreign-key-constraints/修改它以允许一个表数组进入存储过程，并在执行所有这些之前填充drop，truncate，add语句 . 这使您可以控制决定哪些表需要截断 .
标有'905'的答案看起来不错，但不起作用 .
以下为我工作 . 禁用任何主键，唯一键或默认约束 CAN NOT . 事实上，如果'sp_helpconstraint ' ' shows ' n / a'在status_enabled中 - 意味着它可以 NOT 被启用/禁用 .
一个脚本来统治它们：这将truncate和delete命令与sp_MSforeachtable相结合，这样你就可以避免删除和重新创建约束 - 只需指定需要删除而不是截断的表，为了我的目的，我已经包含了一个额外的模式过滤器措施（在2008r2测试）
您可以暂时禁用表的约束，执行工作，然后重建它们 .
这是一个简单的方法...
禁用所有索引，包括主键，这将禁用所有外键，然后重新启用主键，以便您可以使用它们...
[做一些事情，比如加载数据]
然后重新启用并重建索引......