如何使用 T-SQL 临时禁用外键约束？
SQL Server 是否支持禁用和启用外键约束？还是我唯一的选择是drop 然后re-create 约束？
如果要禁用数据库中的所有约束，只需运行以下代码：
-- 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"
在将数据从一个数据库填充到另一个数据库时，我发现它很有用。这是比放弃约束更好的方法。正如您所提到的，在删除数据库中的所有数据并重新填充它（例如在测试环境中）时，它会派上用场。
如果您要删除所有数据，您可能会发现 this solution 很有帮助。
此外，有时禁用所有触发器也很方便，您可以查看完整的解决方案 here。
（从 http://www.sqljunkies.com/WebLog/roman/archive/2005/01/30/7037.aspx、which is now archived in the Wayback Machine 复制）
外键约束和检查约束对于执行数据完整性和业务规则非常有用。在某些情况下，暂时关闭它们很有用，因为它们的行为要么是不需要的，要么弊大于利。我有时会在从外部源加载数据期间禁用对表的约束检查，或者当我需要编写一个表删除/重新创建脚本并将数据重新加载回表中时。我通常在不希望耗时过程失败的情况下这样做，因为数百万行中的一或几行包含错误数据。但是一旦过程完成，我总是会重新打开约束，并且在某些情况下，我会对导入的数据运行数据完整性检查。
如果禁用外键约束，您将能够插入父表中不存在的值。如果禁用检查约束，您将能够将值放入列中，就好像检查约束不存在一样。以下是一些禁用和启用表约束的示例：
-- 禁用所有表约束 ALTER TABLE MyTable NOCHECK CONSTRAINT ALL -- 启用所有表约束 ALTER TABLE MyTable WITH CHECK CHECK CONSTRAINT ALL -- 禁用单个约束 ALTER TABLE MyTable NOCHECK CONSTRAINT MyConstraint -- 启用单个约束 ALTER TABLE MyTable WITH CHECK CHECK CONSTRAINT MyConstraint
要禁用约束，您有 ALTER 使用 NOCHECK 的表
ALTER TABLE [TABLE_NAME] NOCHECK CONSTRAINT [ALL|CONSTRAINT_NAME]
要使您必须使用双 CHECK：
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
sys.sysforeignkeys msdn.microsoft.com/en-us/library/ms177604.aspx
您最好的选择是 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
The target table '???' of the INSERT statement cannot be on either side of a (primary key, foreign key) relationship when the FROM clause contains a nested INSERT, UPDATE, DELETE or MERGE statement. 今天点击这个。
SQL-92 标准允许将一个约束声明为 DEFERRABLE，以便它可以在事务范围内（隐式或显式）延迟。遗憾的是，SQL Server 仍然缺少这个 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)
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 传输数据或想要复制数据时，似乎很有必要暂时禁用或删除外键约束，然后重新启用或重新创建它们。在这些情况下，参照完整性不是问题，因为它已经在源数据库中维护。因此，您可以放心。
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)
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
T.PrimaryKeyConstraintName = R.UNIQUE_CONSTRAINT_NAME,
T.UpdateRule = R.UPDATE_RULE,
T.DeleteRule = R.DELETE_RULE
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
SELECT '
BEGIN TRANSACTION
BEGIN TRY'
--DROP CONSTRAINT:
SELECT
'
ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + ']
DROP CONSTRAINT ' + ForeignKeyConstraintName + '
'
FROM
@table
SELECT '
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
RAISERROR(''Operation failed.'', 16, 1)
END CATCH
IF(@@TRANCOUNT != 0)
BEGIN
COMMIT TRANSACTION
RAISERROR(''Operation completed successfully.'', 10, 1)
END
'
--ADD CONSTRAINT:
SELECT '
BEGIN TRANSACTION
BEGIN 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 CATCH
ROLLBACK TRANSACTION
RAISERROR(''Operation failed.'', 16, 1)
END CATCH
IF(@@TRANCOUNT != 0)
BEGIN
COMMIT TRANSACTION
RAISERROR(''Operation completed successfully.'', 10, 1)
END'
GO
WITH CHECK CHECK 几乎肯定是必需的！
在一些答案和评论中提出了这一点，但我觉得再次提出这一点很重要。
使用以下命令（没有 WITH CHECK）重新启用约束将有一些 serious drawbacks。
ALTER TABLE MyTable CHECK CONSTRAINT MyConstraint;
带支票 | WITH NOCHECK 指定表中的数据是否针对新添加或重新启用的 FOREIGN KEY 或 CHECK 约束进行验证。如果未指定，则假定 WITH CHECK 用于新约束，而 WITH NOCHECK 假定用于重新启用的约束。如果您不想针对现有数据验证新的 CHECK 或 FOREIGN KEY 约束，请使用 WITH NOCHECK。我们不建议这样做，除非在极少数情况下。新约束将在以后的所有数据更新中进行评估。在添加约束时由 WITH NOCHECK 抑制的任何约束违规都可能导致将来的更新失败，如果它们使用不符合约束的数据更新行。查询优化器不考虑用 NOCHECK 定义的约束。在使用 ALTER TABLE table WITH CHECK CHECK CONSTRAINT ALL 重新启用这些约束之前，它们将被忽略。
注意： WITH NOCHECK 是重新启用约束的默认设置。我不得不想为什么...
在执行此命令期间不会评估表中的现有数据 - 成功完成并不能保证表中的数据根据约束是有效的。在无效记录的下一次更新期间，将评估约束并将失败 - 导致可能与所做的实际更新无关的错误。依赖约束来确保数据有效的应用程序逻辑可能会失败。查询优化器不会使用以这种方式启用的任何约束。
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;
第一篇文章:)
对于 OP，kristof 的解决方案将起作用，除非在大删除时存在大量数据和事务日志气球问题。此外，即使有备用的 tlog 存储，由于删除写入到 tlog，对于具有数亿行的表，该操作可能需要很长时间。
我经常使用一系列游标来截断和重新加载我们一个庞大的生产数据库的大型副本。设计的解决方案考虑了多个模式、多个外键列，最重要的是可以存储在 SSIS 中使用。
它涉及创建三个临时表（真实表）以容纳 DROP、CREATE 和 CHECK FK 脚本，创建这些脚本并将其插入表中，然后循环遍历表并执行它们。附带的脚本分为四个部分：1.）在三个临时（真实）表中创建和存储脚本，2.）通过游标逐个执行 drop FK 脚本，3.）使用 sp_MSforeachtable 截断所有除了我们的三个临时表和 4.) 执行创建 FK 并在 ETL SSIS 包末尾检查 FK 脚本。
在 SSIS 的执行 SQL 任务中运行脚本创建部分。在第二个执行 SQL 任务中运行“执行删除 FK 脚本”部分。将截断脚本放在第三个 Execute SQL 任务中，然后在控制流结束时将 CREATE 和 CHECK 脚本附加到最终的 Execute SQL 任务（或两个如果需要）中之前执行您需要执行的任何其他 ETL 过程。
事实证明，当重新应用外键失败时，将脚本存储在真实表中是非常宝贵的，因为您可以从 sync_CreateFK 中选择 *，复制/粘贴到您的查询窗口中，一次运行一个，并在您完成后修复数据问题找到那些失败/仍然无法重新申请的。
如果脚本失败，请不要再次重新运行脚本，而不要确保在执行此操作之前重新应用所有外键/检查，否则您很可能会丢失一些创建并检查 fk 脚本，因为我们的登台表被删除并且在创建要执行的脚本之前重新创建。
----------------------------------------------------------------------------
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
找到约束
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')
西夫韦。
注意：添加了删除约束的解决方案，以便可以删除或修改表而不会出现任何约束错误。
标记为“905”的答案看起来不错，但不起作用。
以下为我工作。不能禁用任何主键、唯一键或默认约束。事实上，如果 'sp_helpconstraint'' 在 status_enabled 中显示 'n/a' - 意味着它不能被启用/禁用。
-- 生成脚本以禁用
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)
您实际上应该能够像暂时禁用其他约束一样禁用外键约束：
Alter table MyTable nocheck constraint FK_ForeignKeyConstraintName
只需确保您在约束名称中列出的第一个表上禁用约束。例如，如果我的外键约束是 FK_LocationsEmployeesLocationIdEmployeeId，我想使用以下内容：
Alter table Locations nocheck constraint FK_LocationsEmployeesLocationIdEmployeeId
即使违反此约束会产生一个错误，不一定将该表声明为冲突的来源。
https://i.stack.imgur.com/QEg4B.png
一个脚本来统治它们：这将 truncate 和 delete 命令与 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
您可以暂时禁用表上的约束，进行工作，然后重新构建它们。
这是一个简单的方法来做到这一点......
禁用所有索引，包括主键，这将禁用所有外键，然后仅重新启用主键，以便您可以使用它们...
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
@sql 总是被截断。 :(
如果你有兴趣，我有一个更有用的版本。我从这里提取了一些代码，该网站的链接不再有效。我对其进行了修改，以允许将一组表放入存储过程中，并在执行所有这些语句之前填充 drop、truncate、add 语句。这使您可以控制决定哪些表需要截断。
/****** 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
您可以使用以下命令轻松关闭 CONSTRAINT：ALTER TABLE TableName NOCHECK CONSTRAINT ALL
完成事务后，不要忘记使用以下命令再次打开它们：ALTER TABLE TableName CHECK CONSTRAINT ALL
"ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"那里应该只有一个“CHECK”吗？select * from sys.foreign_keys where is_disabled = 1告诉您是否有这种情况。