SQL SERVER – How to Disable and Enable All Constraint for Table and Database

One of the most popular questions I find still coming to via email is how to enable or disable all the constraint for single table or database.

Well, in this blog post we will not discuss the reasons why do you need them or what are the advantages or disadvantages of the same. Instead, we will go over scripts to do the same. As lots of users are seeking this script there should be good reason for the same.

-- Disable all table constraints
ALTER TABLE YourTableName NOCHECK CONSTRAINT ALL
-- Enable all table constraints
ALTER TABLE YourTableName CHECK CONSTRAINT ALL
-- ----------
-- Disable single constraint
ALTER TABLE YourTableName NOCHECK CONSTRAINT YourConstraint
-- Enable single constraint
ALTER TABLE YourTableName CHECK CONSTRAINT YourConstraint
-- ----------
-- Disable all constraints for database
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
-- Enable all constraints for database
EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

Let me know if there is any better way to do the same.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – How to Learn SQL Server 2014 – Video Tutorial
Next Post
SQL SERVER – Beginning with SQL Server Security Aspects

Related Posts

15 Comments. Leave new

  • Hi,
    good one.
    There is a slide typo error on a statement for enabling all constraints for a database.
    Regards,
    Hitesh Shah

    Reply
  • ribeirofloripa
    April 17, 2015 4:52 pm

    The foreachproc is a handy tool, but it doesnt work on SQL AZURE, so I came up with a script to generate the ENABLE/DISABLE constraints for all the database.

    SELECT ‘ALTER TABLE [‘ + s.name + ‘].[‘ + o.name + ‘] WITH CHECK CHECK CONSTRAINT [‘ + i.name + ‘]’
    –SELECT ‘ALTER TABLE [‘ + s.name + ‘].[‘ + o.name + ‘] NOCHECK CONSTRAINT all’
    from sys.foreign_keys i
    INNER JOIN sys.objects o ON i.parent_object_id = o.object_id
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id

    Reply
  • Useful info, Many thanks for the share…

    Reply
  • thank you
    but the problem after execute istructions
    ALTER TABLE YourTableName NOCHECK CONSTRAINT ALL
    and
    ALTER TABLE YourTableName CHECK CONSTRAINT ALL
    for all DB tables i loss all relations SCHEMA(s) :-(
    DB constraints still valid but i can’t find anymore schemas with SQL Server Management Studio!!!

    Reply
  • betweene the two istructions i delete the content of all tables

    Reply
  • Sebastian Gfeller
    January 27, 2016 3:26 pm

    Thank you, that snippet was exactly what I needed today :-)

    Reply
  • i do this in situations where i have to copy data from one table to another having a foreign key.
    to bypass the foreign key constraint

    Reply
  • “Add related tables” in database diagram is grayed out. I have enabled all constraints but no result.

    Reply
  • Ruairi McGowan
    March 22, 2017 9:17 pm

    This should be used with caution – I ran this on a database on which someone had disabled constraints in order to insert test data. Needless to say the recovery was not pleasant. :)

    Reply
  • Dave, thanks for the script. Question: Why the inconsistency on the WITH CHECK in the re-enable for your three sets of scripts? Don’t the first two cause me to lose my WITH CHECK?

    Reply
  • You must double check in order to actually tell sql server to trust the constraint, otherwise the constraint is ignored for query plans. Simply enabling is not enough.

    Alter table SalesOrderdetail WITH CHECK check constraint FTCustomerID; — notice the double check

    Reply
  • @pinal firstly, thanks for your scripts, very handy for my use case!

    for this line
    — Enable all table constraints
    ALTER TABLE YourTableName CHECK CONSTRAINT ALL

    I modified to
    ALTER TABLE YourTableName WITH CHECK CHECK CONSTRAINT ALL

    I realised that the NOCHECK was still applied in the table definition (but FK definition was fine) and found this note:

    “The query optimizer doesn’t consider constraints that are defined WITH NOCHECK. Such constraints are ignored until they are re-enabled by using ALTER TABLE table WITH CHECK CHECK CONSTRAINT ALL.”

    from https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-ver15

    Reply

Leave a Reply

Menu