DBA and SysAdmin World

December 20, 2008

Disable or Enable Constraint

Filed under: SQL Server — Tags: , — solihinho @ 11:33 PM
constraint Issue
As there are 3 database environments such as development, testing and production, sometimes there is a request to refresh master or transaction tables in testing from production with specific period.

1st issue is sometimes some data already exists in testing. So I need to delete the existing data. For deleting data we have to delete starting from detail table to master table.

2nd issue is when inserting data from production to testing, we need to insert data starting from master table to detail table. If we do not follow this rule, the action will be failed due to foreign key constraint. It is little bit take time for DBA to analysis which the master table or detail table. For data movement from one to another environment which have identical schema, the fastest way is disable the constraints during the data movement and enable it back after it is finish. Below script only valid for check and foreign key constraint.


Disable Constraints All Tables

sp_msforeachtable @command1 = "print 'Disable constraint on ? '"
                , @command2 = "ALTER TABLE ? NOCHECK CONSTRAINT ALL"

Enable Constraints All Tables

sp_msforeachtable @command1 = "print 'Enable constraint on ? '"
                , @command2 = "ALTER TABLE ? CHECK CONSTRAINT ALL"

Time outAfter you move the data and enable the constraints, this enabling constraint doesn’t check the data that already exists in the table. So you will not receive any violation message though there is a violated data. Use DBCC CHECKCONSTRAINTS to check whether there is a violated data or not and just clean it manually.
Time out Since the script enable/disable all the constraints and triggers, just make sure before you run the script, note the constraint already disabled before you want to enable all the constraints vice versa by running below script.

List All Constraints Status 

    CASE WHEN OBJECTPROPERTY(constid, 'cnstisdisabled') = 0 THEN 'Enabled'
       ELSE 'Disabled' END AS Status 
   ,O1.Name AS ConstraintName
   ,O2.Name AS TableName
   ,C1.name AS ColumnName
   ,O3.name AS ReferencedTable
   ,C2.name AS ReferencedColumn
FROM sysforeignkeys FK
INNER JOIN sysobjects O1 ON O1.id = FK.constid
INNER JOIN sysobjects O2 ON O2.id = FK.fkeyid
INNER JOIN syscolumns C1 ON C1.id = FK.fkeyid AND C1.colid = FK.fkey
INNER JOIN sysobjects O3 ON O3.id = FK.rkeyid
INNER JOIN syscolumns C2 ON C2.id = FK.rkeyid AND C2.colid = FK.rkey
ORDER BY TableName, ConstraintName, FK.KeyNo


  1. Great!, It works even with table name


    Comment by Chen Noam — February 18, 2009 @ 4:59 PM

  2. Hello webmaster
    I would like to share with you a link to your site
    write me here preonrelt@mail.ru

    Comment by Alexwebmaster — March 3, 2009 @ 6:02 PM

  3. Great. Thanks

    Comment by Edson Monte — November 21, 2009 @ 1:13 AM

  4. Great sample and posting….

    Comment by luispimi — May 1, 2010 @ 3:41 AM

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: