Temporarily disable all foreign key constraints

问题:

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?

回答1:

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

回答2:

Use the built-in sp_msforeachtable stored procedure.

To disable all constraints:

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT ALL";

To enable all constraints:

EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL";

To drop all the tables:

EXEC sp_msforeachtable "DROP TABLE ?";

回答3:

A good reference is given at : http://msdn.microsoft.com/en-us/magazine/cc163442.aspx under the section "Disabling All Foreign Keys"

Inspired from it, an approach can be made by creating a temporary table and inserting the constraints in that table, and then dropping the constraints and then reapplying them from that temporary table. Enough said here is what i am talking about

 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

回答4:

Disable all table constraints

ALTER TABLE TableName NOCHECK CONSTRAINT ConstraintName

-- Enable all table constraints

ALTER TABLE TableName CHECK CONSTRAINT ConstraintName

回答5:

not need to run queries to sidable FKs on sql. If you have a FK from table A to B, you should:

• delete data from table A
• delete data from table B
• insert data on B
• insert data on A

You can also tell the destination not to check constraints

回答6:

Truncating the table wont be possible even if you disable the foreign keys.so you can use delete command to remove all the records from the table,but be aware if you are using delete command for a table which consists of millions of records then your package will be slow and your transaction log size will increase and it may fill up your valuable disk space.

If you drop the constraints it may happen that you will fill up your table with unclean data and when you try to recreate the constraints it may not allow you to as it will give errors. so make sure that if you drop the constraints,you are loading data which are correctly related to each other and satisfy the constraint relations which you are going to recreate.

so please carefully think the pros and cons of each method and use it according to your requirements

回答7:

In case you use a different database schemas than ".dbo" or your db is containing Pk´s, which are composed by several fields, please don´t use the the solution of Carter Medlin, otherwise you will damage your db!!!

When you are working with different schemas try this (don´t forget to make a backup of your database before!):

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

After doing some Fk-free actions, you can switch back with

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";

回答8:

Disable all indexes (including the pk, which will disable all fks), then reenable the pks.

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

[Do your data load]

Then bring everything back to life...

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