暂时禁用所有外键约束 [英] Temporarily disable all foreign key constraints

问题描述

我正在运行一个SSIS包，它将一些表格的数据从FlatFiles替换到数据库中的现有表格。

我的包将截断表，然后插入新数据。当我运行我的SSIS包，我得到一个异常，因为外键。

我可以禁用约束，运行我的导入，然后重新启用它们吗？ p>

解决方案

要禁用外键约束：

  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 +'NOCHECK CONSTRAINT ALL; 
'FROM x; 
 
 EXEC 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 +'WITH CHECK CHECK CONSTRAINT ALL; 
'FROM x; 
 
 EXEC sp_executesql @sql; 
  

但是，您将无法截断必须从正确的顺序删除他们。如果您需要截断，您需要完全删除这些约束，然后重新创建它们。这很简单，如果你的外键约束都很简单，单列约束，但如果涉及多个列，肯定更复杂。

这里是你可以尝试的东西。为了使它成为你的SSIS包的一部分，你需要一个地方来存储FK定义，而SSIS包运行（你不能够在一个脚本中执行这一切）。因此，在某些实用程序数据库中，创建一个表：

  CREATE TABLE dbo.PostCommand（cmd NVARCHAR（MAX））;然后在你的数据库中，你可以有一个存储过程：
 
 
 
 
 
 
 
 
 $ b 
  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（'。'，'nvarchar（max）'），1,1，''）
 +'）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（'。'，'nvarchar（max）'） ，1,1'）+'）; 
'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 sp_executesql @sql; 
 END 
  
 
 
现在当你的SSIS包完成后，它应该调用一个不同的存储过程，它执行：
  DECLARE @sql NVARCHAR（MAX）; 
 
 SELECT @sql = cmd FROM other_database.dbo.PostCommand; 
 
 EXEC sp_executesql @sql; 
  
如果你这么做只是为了能够截断而不是删除，我建议只是采取命中和运行删除。也许使用批量记录的恢复模式来最小化日志的影响。一般来说，我不知道这个解决方案将比只使用正确的顺序删除快得多。
 
 
 
在2014年我发表了一篇更详细的文章这里：
 
 
 
 
 
 删除并重新创建SQL Server中的所有外键约束 
 
 
 
I am running an SSIS package which will replace data for a few tables from FlatFiles to existing tables in a database.

My package will truncate the tables and then insert the new data. When I run my SSIS package, I get an exception because of the foreign keys.

Can I disable the constraints, run my import, then re-enable them?
解决方案
To disable foreign key constraints:
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 + ' NOCHECK CONSTRAINT ALL;
' FROM x;

EXEC sp_executesql @sql;
To re-enable:
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 + ' WITH CHECK CHECK CONSTRAINT ALL;
' FROM x;

EXEC sp_executesql @sql;
However, you will not be able to truncate the tables, you will have to delete from them in the right order. If you need to truncate them, you need to drop the constraints entirely, and re-create them. This is simple to do if your foreign key constraints are all simple, single-column constraints, but definitely more complex if there are multiple columns involved. 

Here is something you can try. In order to make this a part of your SSIS package you'll need a place to store the FK definitions while the SSIS package runs (you won't be able to do this all in one script). So in some utility database, create a table:
CREATE TABLE dbo.PostCommand(cmd NVARCHAR(MAX));
Then in your database, you can have a stored procedure that does this:
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('.', 'nvarchar(max)'), 1, 1, '')
+ ') 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('.', 'nvarchar(max)'), 1, 1, '') + ');
' 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 sp_executesql @sql;
END
Now when your SSIS package is finished, it should call a different stored procedure, which does:
DECLARE @sql NVARCHAR(MAX);

SELECT @sql = cmd FROM other_database.dbo.PostCommand;

EXEC sp_executesql @sql;
If you're doing all of this just for the sake of being able to truncate instead of delete, I suggest just taking the hit and running a delete. Maybe use bulk-logged recovery model to minimize the impact of the log. In general I don't see how this solution will be all that much faster than just using a delete in the right order.

In 2014 I published a more elaborate post about this here:


Drop and Re-Create All Foreign Key Constraints in SQL Server


                        
这篇关于暂时禁用所有外键约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！