Temporarily disable all foreign key constraints

Question

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

User · Answer

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

User · Answer

There is a easy way to this  -- 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   Reference SQL SERVER     Disable All the Foreign Key Constraint in Database     Enable All the Foreign Key Constraint in Database

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Disable all table constraints  ALTER TABLE TableName NOCHECK CONSTRAINT ConstraintName   -- Enable all table constraints  ALTER TABLE TableName CHECK CONSTRAINT ConstraintName

User · Answer

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

User · Answer

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

User · Answer

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

[sql-server] Temporarily disable all foreign key constraints

Examples related to sql-server

Examples related to sql-server-2008

Examples related to ssis

Examples related to constraints