我正在运行一个SSIS包，将FlatFiles中的数据替换为数据库中现有表的数据。
我的包将截断表，然后插入新数据。当我运行我的SSIS包时，由于外键问题，我遇到了异常。
我能否禁用约束，运行导入，然后重新启用它们？
9个回答
114
禁用外键约束：
DECLARE @sql nvarchar(max) = N'';
;WITH x AS
(
SELECT DISTINCT obj =
QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.'
+ QUOTENAME(OBJECT_NAME(parent_object_id))
FROM sys.foreign_keys
)
SELECT @sql += N'ALTER TABLE ' + obj + N' NOCHECK CONSTRAINT ALL;
' FROM x;
EXEC sys.sp_executesql @sql;
重新启用：
DECLARE @sql nvarchar(max) = N'';
;WITH x AS
(
SELECT DISTINCT obj =
QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.'
+ QUOTENAME(OBJECT_NAME(parent_object_id))
FROM sys.foreign_keys
)
SELECT @sql += N'ALTER TABLE ' + obj + N' WITH CHECK CHECK CONSTRAINT ALL;
' FROM x;
EXEC sys.sp_executesql @sql;
然而，您将无法截断表格，您必须按正确的顺序从中删除。如果需要截断它们，则需要完全删除约束并重新创建它们。如果您的外键约束都是简单的单列约束，则这很容易实现，但如果涉及多个列，则肯定更加复杂。
以下是您可以尝试的内容。为了使其成为SSIS软件包的一部分，您需要一个地方来存储FK定义，同时运行SSIS软件包（您无法在一个脚本中完成所有操作）。因此，在某个实用数据库中，创建一个表：
CREATE TABLE dbo.PostCommand(cmd nvarchar(max));
那么在您的数据库中，您可以有一个存储过程来执行此操作：
DELETE other_database.dbo.PostCommand;
DECLARE @sql nvarchar(max) = N'';
SELECT @sql += N'ALTER TABLE '
+ QUOTENAME(OBJECT_SCHEMA_NAME(fk.parent_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(fk.parent_object_id))
+ ' ADD CONSTRAINT ' + fk.name + ' FOREIGN KEY ('
+ STUFF((SELECT ',' + c.name
FROM sys.columns AS c
INNER JOIN sys.foreign_key_columns AS fkc
ON fkc.parent_column_id = c.column_id
AND fkc.parent_object_id = c.[object_id]
WHERE fkc.constraint_object_id = fk.[object_id]
ORDER BY fkc.constraint_column_id
FOR XML PATH(''),
TYPE).value(N'./text()[1]', 'nvarchar(max)'), 1, 1, N'')
+ ') REFERENCES ' +
QUOTENAME(OBJECT_SCHEMA_NAME(fk.referenced_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(fk.referenced_object_id))
+ '(' +
STUFF((SELECT ',' + c.name
FROM sys.columns AS c
INNER JOIN sys.foreign_key_columns AS fkc
ON fkc.referenced_column_id = c.column_id
AND fkc.referenced_object_id = c.[object_id]
WHERE fkc.constraint_object_id = fk.[object_id]
ORDER BY fkc.constraint_column_id
FOR XML PATH(''),
TYPE).value(N'./text()[1]', N'nvarchar(max)'), 1, 1, N'') + ');
' FROM sys.foreign_keys AS fk
WHERE OBJECTPROPERTY(parent_object_id, 'IsMsShipped') = 0;
INSERT other_database.dbo.PostCommand(cmd) SELECT @sql;
IF @@ROWCOUNT = 1
BEGIN
SET @sql = N'';
SELECT @sql += N'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(fk.parent_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(fk.parent_object_id))
+ ' DROP CONSTRAINT ' + fk.name + ';
' FROM sys.foreign_keys AS fk;
EXEC sys.sp_executesql @sql;
END
现在，当您的SSIS包完成时，它应该调用一个不同的存储过程，执行以下操作：
DECLARE @sql nvarchar(max);
SELECT @sql = cmd FROM other_database.dbo.PostCommand;
EXEC sys.sp_executesql @sql;
如果你只是为了能够截断而不是删除而做所有这些工作，我建议你忍受一次删除。也许可以使用批量日志记录恢复模型来减少日志的影响。总的来说，我不认为这种解决方案比按正确顺序使用删除要快多少。
2014年，我在这里发表了一篇更详细的文章：
- 在SQL Server中删除并重新创建所有外键约束
- Aaron Bertrand
9
请查看一下是否有可用的代码，因为如果没有，我认为我的 Identity 插入可能会出问题，我需要截断表。 - HaBo
1顺便说一下，我正在尝试运行您的禁用和启用脚本。禁用没有问题，但是当我尝试启用时，出现了“Msg 547，Level 16，State 0，Line 1 The ALTER TABLE statement conflicted with the FOREIGN KEY constraint ..........."的冲突。 - HaBo
@HaBo 在此期间，您是否插入了违反约束条件的数据？如果表中包含不符合约束条件的数据，则无法启用约束条件。 - Aaron Bertrand
7请尝试运行以下代码：
SELECT name, is_disabled FROM sys.foreign_keys; 提醒大家，在测试系统上先运行来自网站上陌生人的代码示例，这就是为什么你要运行不理解的代码示例的原因。 - Aaron BertrandFYI：删除操作完美执行。我在运行删除过程后测试了SELECT * FROM sys.foreign_keys，没有返回任何键。这证实了删除操作是好的。无论如何，谢谢你。我完成了我的任务。 - HaBo
显示剩余4条评论
58
使用内置的sp_msforeachtable存储过程。
禁用所有约束：
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT ALL";
启用所有的约束条件：
EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL";
删除所有表：
EXEC sp_msforeachtable "DROP TABLE ?";
- Ed Randall
5
本周这个回答有两个踩，不知道问题出在哪里？ - Ed Randall
12也许有人尝试在生产数据库上执行这三个查询，特别是最后一个查询 :-) - carlo.borreo
2遗憾的是，在Azure SQL中不存在
sp_msforeachtable。 - Matt Jacobi1禁用和删除约束是两个完全不同的事情。如果您禁用它们，您将无法截断表格，这就是 SQL Server 的工作方式。您必须先删除它们，然后截断表格，最后再添加约束。 - HamsterWithPitchfork
关于sp_msforeachtable的更多信息，包括代码，以便您可以将其安装到Azure SQL中：https://www.sqlshack.com/an-introduction-to-sp_msforeachtable-run-commands-iteratively-through-all-tables-in-a-database/ - Ed Randall
8
有一种简单的方法可以做到这一点。
-- Disable all the constraint in database
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'
-- Enable all the constraint in database
EXEC sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all'
- Trần Thanh Phong
5
一个很好的参考资料在这里: http://msdn.microsoft.com/en-us/magazine/cc163442.aspx，其中有一节是“禁用所有外键”。
受此启发，可以通过创建一个临时表，在该表中插入约束，然后删除这些约束，再从该临时表重新应用它们来实现。言归正传，下面是我所说的内容。
受此启发，可以通过创建一个临时表，在该表中插入约束，然后删除这些约束，再从该临时表重新应用它们来实现。言归正传，下面是我所说的内容。
SET NOCOUNT ON
DECLARE @temptable TABLE(
Id INT PRIMARY KEY IDENTITY(1, 1),
FKConstraintName VARCHAR(255),
FKConstraintTableSchema VARCHAR(255),
FKConstraintTableName VARCHAR(255),
FKConstraintColumnName VARCHAR(255),
PKConstraintName VARCHAR(255),
PKConstraintTableSchema VARCHAR(255),
PKConstraintTableName VARCHAR(255),
PKConstraintColumnName VARCHAR(255)
)
INSERT INTO @temptable(FKConstraintName, FKConstraintTableSchema, FKConstraintTableName, FKConstraintColumnName)
SELECT
KeyColumnUsage.CONSTRAINT_NAME,
KeyColumnUsage.TABLE_SCHEMA,
KeyColumnUsage.TABLE_NAME,
KeyColumnUsage.COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE KeyColumnUsage
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TableConstraints
ON KeyColumnUsage.CONSTRAINT_NAME = TableConstraints.CONSTRAINT_NAME
WHERE
TableConstraints.CONSTRAINT_TYPE = 'FOREIGN KEY'
UPDATE @temptable SET
PKConstraintName = UNIQUE_CONSTRAINT_NAME
FROM
@temptable tt
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS ReferentialConstraint
ON tt.FKConstraintName = ReferentialConstraint.CONSTRAINT_NAME
UPDATE @temptable SET
PKConstraintTableSchema = TABLE_SCHEMA,
PKConstraintTableName = TABLE_NAME
FROM @temptable tt
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TableConstraints
ON tt.PKConstraintName = TableConstraints.CONSTRAINT_NAME
UPDATE @temptable SET
PKConstraintColumnName = COLUMN_NAME
FROM @temptable tt
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KeyColumnUsage
ON tt.PKConstraintName = KeyColumnUsage.CONSTRAINT_NAME
--Now to drop constraint:
SELECT
'
ALTER TABLE [' + FKConstraintTableSchema + '].[' + FKConstraintTableName + ']
DROP CONSTRAINT ' + FKConstraintName + '
GO'
FROM
@temptable
--Finally to add constraint:
SELECT
'
ALTER TABLE [' + FKConstraintTableSchema + '].[' + FKConstraintTableName + ']
ADD CONSTRAINT ' + FKConstraintName + ' FOREIGN KEY(' + FKConstraintColumnName + ') REFERENCES [' + PKConstraintTableSchema + '].[' + PKConstraintTableName + '](' + PKConstraintColumnName + ')
GO'
FROM
@temptable
GO
- NG.
5
为了禁用所有约束，可以尝试执行以下操作：
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all" - NG.
并启用它们返回
执行sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all" - NG.
但是这些将禁用所有约束，所以不确定在您的ssis情况下是否有帮助，当我们截断表数据时可能会有帮助。 - NG.
我不确定表变量如何帮助...如果这是由SSIS包执行的，最后一步将不再有对表变量的访问。这就是为什么我的解决方案使用永久表...即使包失败、被取消、服务器崩溃等，那些约束定义仍然可用。此外，您的脚本对外键约束有一个非常简单的视图——它无法处理多列外键（例如
FOREIGN KEY (a,b) REFERENCES dbo.foo(c,d)）。 - Aaron BertrandAaron Bertand：感谢您的回答。 - NG.
1
禁用所有表约束。
-- 启用所有表约束
ALTER TABLE TableName NOCHECK CONSTRAINT ConstraintName
-- 启用所有表约束
ALTER TABLE TableName CHECK CONSTRAINT ConstraintName
- Alam Usmani
1
2禁用是不足以允许截断的。 - Aaron Bertrand
1
如果您使用的数据库模式不同于“.dbo”，或者您的数据库包含由多个字段组成的主键，请不要使用Carter Medlin的解决方案，否则将损坏您的数据库！！！
当您使用不同的模式时，请尝试以下方法（不要忘记在操作之前备份数据库）：
DECLARE @sql AS NVARCHAR(max)=''
select @sql = @sql +
'ALTER INDEX ALL ON ' + SCHEMA_NAME( t.schema_id) +'.'+ '['+ t.[name] + '] DISABLE;'+CHAR(13)
from
sys.tables t
where type='u'
select @sql = @sql +
'ALTER INDEX ' + i.[name] + ' ON ' + SCHEMA_NAME( t.schema_id) +'.'+'[' + 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
在执行一些不需要Fk的操作之后，您可以使用以下方法切换回来：
DECLARE @sql AS NVARCHAR(max)=''
select @sql = @sql +
'ALTER INDEX ALL ON ' + SCHEMA_NAME( t.schema_id) +'.'+'[' + t.[name] + '] REBUILD;'+CHAR(13)
from
sys.tables t
where type='u'
print @sql
exec dbo.sp_executesql @sql;
exec sp_msforeachtable "ALTER TABLE ? WITH NOCHECK CHECK CONSTRAINT ALL";
- olaf870
1
当然，一切都被搞糊涂了。通常不会尝试这些事情，但有备份。不幸的是，在完全删除约束条件后再添加回去之前，无法截断表格。在 MSSQL 中禁用约束条件无法用于截断（使用 SQL2016 和更早版本-不确定最新版本）。 - MC9000
0
在 SQL 中，無需運行查詢即可禁用 FK。如果您在表 A 到 B 之間建立了一個 FK，則應：
- 從表 A 中刪除數據
- 從表 B 中刪除數據
- 在 B 上插入數據
- 在 A 上插入數據
您還可以告訴目的地不要檢查約束條件。
- Diego
3
2即使表为空，甚至约束已禁用，也无法截断被外键约束引用的表。你试过了吗？ - Aaron Bertrand
你是对的，我忘记了那个细节，但你可以运行delete * from....并重置标识。 - Diego
所以请更新您的答案并提供该信息。用户无法使用truncate，除非他们删除外键。 - Aaron Bertrand
0
即使你禁用了外键，也无法截断表。因此，您可以使用delete命令从表中删除所有记录，但是请注意，如果您将delete命令用于包含数百万条记录的表，则您的程序将变慢，您的事务日志大小将增加，并且可能填满有价值的磁盘空间。
如果您删除了约束条件，则可能会在表中填充不干净的数据，当您尝试重新创建约束条件时，它可能不会让您这样做，因为它会产生错误。因此，请确保如果您删除约束条件，则正在加载彼此正确相关并满足您要重新创建的约束关系的数据。
因此，请仔细考虑每种方法的利弊，并根据您的要求使用它。
- vimal vasudevan
-2
禁用所有索引（包括主键，这将禁用所有外键），然后重新启用主键。
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
- Carter Medlin
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接
SELECT * FROM sys.all_objects WHERE name like 'sp_MSforeach%';，因此尝试使用它，例如EXEC sp_MSforeachtable SELECT 1 AS FOO将会出现错误信息Msg 2812, Level 16, State 62, Line 15 Could not find stored procedure 'sp_MSforeachtable'.- TPPZ