How can foreign key constraints be temporarily disabled using T-SQL?

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?

Solution 1

If you want to disable all constraints in the database just run this code:

-- disable all constraints
EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

To switch them back on, run: (the print is optional of course and it is just listing the tables)

-- enable all constraints
exec sp_MSforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

I find it useful when populating data from one database to another. It is much better approach than dropping constraints. As you mentioned it comes handy when dropping all the data in the database and repopulating it (say in test environment).

If you are deleting all the data you may find this solution to be helpful.

Also sometimes it is handy to disable all triggers as well, you can see the complete solution here.

Solution 2

(Copied from from http://www.sqljunkies.com/WebLog/roman/archive/2005/01/30/7037.aspx, which is now archived in the Wayback Machine)

Foreign key constraints and check constraint are very useful for enforcing data integrity and business rules. There are certain scenarios though where it is useful to temporarily turn them off because their behavior is either not needed or could do more harm than good. I sometimes disable constraint checking on tables during data loads from external sources or when I need to script a table drop/recreate with reloading the data back into the table. I usually do it in scenarios where I don't want a time consuming process to fail because one or a few of many million rows have bad data in it. But I always turn the constraints back on once the process is finished and also in some cases I run data integrity checks on the imported data.

If you disable a foreign key constraint, you will be able to insert a value that does not exist in the parent table. If you disable a check constraint, you will be able to put a value in a column as if the check constraint was not there. Here are a few examples of disabling and enabling table constraints:

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

Solution 3

To disable the constraint you have ALTER the table using NOCHECK

ALTER TABLE [TABLE_NAME] NOCHECK CONSTRAINT [ALL|CONSTRAINT_NAME]

To enable you to have to use double CHECK:

ALTER TABLE [TABLE_NAME] WITH CHECK CHECK CONSTRAINT [ALL|CONSTRAINT_NAME]

• Pay attention to the double CHECK CHECK when enabling.
• ALL means for all constraints in the table.

Once completed, if you need to check the status, use this script to list the constraint status. Will be very helpfull:

    SELECT (CASE 
        WHEN OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0 THEN 'ENABLED'
        ELSE 'DISABLED'
        END) AS STATUS,
        OBJECT_NAME(CONSTID) AS CONSTRAINT_NAME,
        OBJECT_NAME(FKEYID) AS TABLE_NAME,
        COL_NAME(FKEYID, FKEY) AS COLUMN_NAME,
        OBJECT_NAME(RKEYID) AS REFERENCED_TABLE_NAME,
        COL_NAME(RKEYID, RKEY) AS REFERENCED_COLUMN_NAME
   FROM SYSFOREIGNKEYS
ORDER BY TABLE_NAME, CONSTRAINT_NAME,REFERENCED_TABLE_NAME, KEYNO 

Solution 4

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

Solution 5

The SQL-92 standard allows for a constaint to be declared as DEFERRABLE so that it can be deferred (implicitly or explicitly) within the scope of a transaction. Sadly, SQL Server is still missing this SQL-92 functionality.

For me, changing a constraint to NOCHECK is akin to changing the database structure on the fly -- dropping constraints certainly is -- and something to be avoided (e.g. users require increased privileges).

View more solutions

Comments

[Steven A. Lowe]

a good find, but note that you still cannot truncate the table without removing the foreign key constraints

[Jimoc]

and you will also need to be aware that when you turn the constraints back on and do a data integrity check, your data may fail and fixng an issue like that can be a nightmare if the failing data is at the end of a long string of linked constraints.

[Tony Peterson]

I had to make a mass migration because I created a new Sql Server database to restructure, and this code was very helpful to make my migration code work

[Omu]

my sql server 2008 asks for check check instead of check and check nocheck

[CrazyPyro]

"ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all" Should there be only one "CHECK" there?

[kristof]

@CrazyPyro - no you need both

[Valentino Vranken]

@CrazyPyro: both are indeed needed, reason for that is because the first CHECK belongs with the WITH and the second CHECK with the CONSTRAINT (it's the type of constraint). The first CHECK ensures your data gets checked for consistency when activating the constraint. If you don't want that, you could write WITH NOCHECK. Can be useful in certain test situations when you don't care about the actual data, as long as there is some so that your queries have something to play with.

[BlueChippy]

This script is great for generating my "ALTER" commands, but how can I get these to execute/run in an SP?

[Zar Shardan]

I think this won't work if any of the foreign keys is multi-column

[THE JOATMON]

Is it bad that I get this result on the second command? "The ALTER TABLE statement conflicted with the FOREIGN KEY constraint ..."

[James McCormack]

It's worth noting that even with constraints disabled, TRUNCATE TABLE won't work. For that, you will need to drop the constraints. Otherwise, use DELETE FROM, but take into consideration the difference: mssqltips.com/sqlservertip/1080/…

[kristof]

@ James that's a good point - you can check this related question which I also reference here

[PseudoNinja]

Just a note for everyone that sp_msforeachtable is not available on Azure SQL

[Scratz]

This also did not produce all characters for exceedingly long table/key names.

[ps2goat]

You also need a second check when turning the constraints back on. Otherwise, as-is, your code will only check the constraint once, not turn it on.

[Mike Christian]

Also note NOCHECK CONSTRAINT is not available in any SQL Server Compact Editions (CE), as of posting this comment.

[Kaloyan Roussev]

Can I apply this to all tables because I dont want to run this command 20 times

[ekkis]

the CHECK CHECK fails for me on SQL Server 2008R2, with Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'check'.

[crokusek]

yes, "with check check" needed for 2012. Edit was rejected? MS Link

[user2173353]

This doesn't seem to work on my SQL server 2008 R2. The contraints are still there... :(

[Brent]

Just a note that TRUNCATE TABLE myTable still checks constraints, have to do DELETE FROM myTable. Also re-enabling constraints doesn't work if a required row to satisfy constraint doesn't exist anymore.

[Mahmood Dehghan]

Can u explain why it's better than disabling and re-enabling constraints?

[leighghunt]

If you get Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'CHECK'., as per comment by @ekkis, make sure you don't miss out the WITH keyword like I did.

[lathomas64]

Thank you james for the note about this not working with TRUNCATE TABLE it was buried under the more comments and I was frustrated about this until I saw I could just DELETE FROM the table instead.

[angularsen]

I'm not able to get this to work inside an Entity Framework code first migration, using the Sql() statement to temporarily disable FKs before dropping a PK in order to convert it to a non-clustered index. I had to drop and recreate the FKs. Any ideas?

[Rachael]

@ScottBeeson this means when you've readded the constraint, you've got mismatched foreign/primary keys inserted into your table, or you are not allowing nulls and there are nulls where you should have key values. It sounds like you've inserted data (with relationship that needs preserving) into your table without maintaining the relationship.

[Seth Flowers]

@StevenA.Lowe - That is correct, but you can still delete from MyTable

[Mitul]

Getting error #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOCHECK CONSTRAINT all' at line 1

[Scott Munro]

The statements used here to re-enable the constraints are missing the WITH CHECK clauses. This is a pretty major flaw. See my answer for details - stackoverflow.com/a/35427150/81595.

[Magnus]

@Valentino Vranken, actually the second CHECK is not used here to indicate the type of constraint. The meaning of it here is that It gives the instruction to enable the constraint. Compare when you disable, in which case you write NOCHECK.

[adolf garlic]

this doesn't work, I get an error when trying to change column datatype after running the first part: "The object 'DF__table_blahblah' is dependent on column 'mycolname'."

[Matt Browne]

Nice script. For a similar but alternative approach, see: mssqltips.com/sqlservertip/3347/…

[Kiquenet]

Not view for Primary keys ? For foreign keys SYSFOREIGNKEYS System View sys.sysforeignkeys msdn.microsoft.com/en-us/library/ms177604.aspx

[Diego Mendes]

if you are trying to disable primary key for insert, I would recomend using (SET IDENTITY_INSERT) if you want just check for primary key, you can try sys.key_constraints with sys.indexes.is_primary_key

[TPPZ]

Sometimes this undocumented stored procedure is not there in the database. This gives 0 records: SELECT * FROM sys.all_objects WHERE name like 'sp_MSforeach%';, so then an attempt at using it e.g. EXEC sp_MSforeachtable SELECT 1 AS FOO gives error message Msg 2812, Level 16, State 62, Line 15 Could not find stored procedure 'sp_MSforeachtable'.

[Andre Figueiredo]

This is very dangerous! This will enable constraints that had been previously disabled. select * from sys.foreign_keys where is_disabled = 1 tells you if you have this scenario.

[Corey]

@MahmoodDehghan Old comment, but there are situations where disabling a constraint isn't enough. The target table '???' of the INSERT statement cannot be on either side of a (primary key, foreign key) relationship when the FROM clause contains a nested INSERT, UPDATE, DELETE or MERGE statement. Hit this today.

[Ercument Eskar]

@MahmoodDehghan In my situation; Still I can't drop PK because of FK even I disabled them. I have to drop & create