How can foreign key constraints be temporarily dis

Question

Are disabling and enabling foreign key constraints supported in SQL Server? Or is my only option to drop and then re-create the constraints?

Answer 1

Your best option is to DROP and CREATE foreign key constraints.

I didn't find examples in this post that would work for me "as-is", one would not work if foreign keys reference different schemas, the other would not work if foreign key references multiple columns. This script considers both, multiple schemas and multiple columns per foreign key.

Here is the script that generates "ADD CONSTRAINT" statements, for multiple columns it will separate them by comma (be sure to save this output before executing DROP statements):

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

Here is the script that generates "DROP CONSTRAINT" statements:

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

Answer 2

First post :)

For the OP, kristof's solution will work, unless there are issues with massive data and transaction log balloon issues on big deletes. Also, even with tlog storage to spare, since deletes write to the tlog, the operation can take a VERY long time for tables with hundreds of millions of rows.

I use a series of cursors to truncate and reload large copies of one of our huge production databases frequently. The solution engineered accounts for multiple schemas, multiple foreign key columns, and best of all can be sproc'd out for use in SSIS.

It involves creation of three staging tables (real tables) to house the DROP, CREATE, and CHECK FK scripts, creation and insertion of those scripts into the tables, and then looping over the tables and executing them. The attached script is four parts: 1.) creation and storage of the scripts in the three staging (real) tables, 2.) execution of the drop FK scripts via a cursor one by one, 3.) Using sp_MSforeachtable to truncate all the tables in the database other than our three staging tables and 4.) execution of the create FK and check FK scripts at the end of your ETL SSIS package.

Run the script creation portion in an Execute SQL task in SSIS. Run the "execute Drop FK Scripts" portion in a second Execute SQL task. Put the truncation script in a third Execute SQL task, then perform whatever other ETL processes you need to do prior to attaching the CREATE and CHECK scripts in a final Execute SQL task (or two if desired) at the end of your control flow.

Storage of the scripts in real tables has proven invaluable when the re-application of the foreign keys fails as you can select * from sync_CreateFK, copy/paste into your query window, run them one at a time, and fix the data issues once you find ones that failed/are still failing to re-apply.

Do not re-run the script again if it fails without making sure that you re-apply all of the foreign keys/checks prior to doing so, or you will most likely lose some creation and check fk scripting as our staging tables are dropped and recreated prior to the creation of the scripts to execute.

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

Answer 3

Find the constraint

SELECT * 
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('TABLE_NAME')

Execute the SQL generated by this 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')

Safeway.

Note: Added solution for droping the constraint so that table can be dropped or modified without any constraint error.

Answer 4

I have a more useful version if you are interested. I lifted a bit of code from here http://www.dbaservices.com.au/news/drop-recreate-foreign-key-constraints/ modifying it to allow for an array of tables into the stored procedure and it populates the drop, truncate, add statements before executing all of them. This gives you control to decide which tables need truncating.

/****** 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

Answer 5

You can temporarily disable constraints on your tables, do work, then rebuild them.

Here is an easy way to do it...

Disable all indexes, including the primary keys, which will disable all foreign keys, then re-enable just the primary keys so you can work with them...

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 something, like loading data]

Then re-enable and rebuild the indexes...

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

Answer 6

http://www.sqljunkies.com/WebLog/roman/archive/2005/01/30/7037.aspx

-- Disable all table constraints

ALTER TABLE MyTable NOCHECK CONSTRAINT ALL

-- Enable all table constraints

ALTER TABLE MyTable WITH CHECK CHECK CONSTRAINT ALL

-- Disable single constraint

ALTER TABLE MyTable NOCHECK CONSTRAINT MyConstraint

-- Enable single constraint

ALTER TABLE MyTable WITH CHECK CHECK CONSTRAINT MyConstraint