SQL SERVER – Disable CHECK Constraint – Enable CHECK Constraint

Foreign Key and Check Constraints are two types of constraints that can be disabled or enabled when required. This type of operation is needed when bulk loading operations are required or when there is no need to validate the constraint. The T-SQL Script that does the same is very simple.

USE AdventureWorks
GO
-- Disable the constraint
ALTER TABLE HumanResources.Employee
NOCHECK CONSTRAINT CK_Employee_BirthDate
GO
-- Enable the constraint
ALTER TABLE HumanResources.Employee
WITH CHECK CHECK CONSTRAINT CK_Employee_BirthDate
GO

It is very interesting that when the constraint is enabled, the world CHECK is used twice – WITH CHECK CHECK CONSTRAINT. I often ask those to find the mistake in this script when they claim to know the syntax very well.

Have you ever disabled and enabled constraints in your production environment? I would like to know why you did so.

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

About these ads

23 thoughts on “SQL SERVER – Disable CHECK Constraint – Enable CHECK Constraint

  1. I have disabled FKs in the past for purposes of data loading, where data coming into constrained column was going to have nulls.

    After loading and cleaning the constraints were re-enabled.

  2. I remember, We had a huge database that collects statistics around 45 GB and we had very less space on server, it created many problems.

    Finally one fine day, they asked me if I could help, they said, they want to clean historical data. Vendor suggested them to use front End to Purge (Delete) data. It was like tons of records… and would take for ever more than a day, to purge data, because you could purge very few records at one time.

    I studied their database and came to conclusion that there are only 3 tables that had huge amount of data… and other tables were referring to them.

    I gave them a solution to Truncate those 3 tables. For that I had to drop foreign key constraint. I dropped Forign Key Constraint, Truncated tables and built Foreign Key constraint again.

    Boom… 3 seconds… Database size came to less than 0.5 GB.

    Result: I was awarded DBA of the week :)

    Note: I did enough research on the database, and I ensured that truncating data from 3 tables wont effect application.

    ~ IM.

  3. @Dave so you disabled the NOT NULL CONSTRAINT?

    @Imran So you disable the FK.

    I think FK is the most likely CONSTRAINT to be disabled, and NOT NULL being a close second. A CHECK CONSTRAINT (not the one for NOT NULL which although is a CHECK CONSTRAINT, is specified differently) is not really disabled too often.

    It would be interesting to see what types a CHECK CONSTRAINT are actually used, and which types are disabled.

  4. You don’t need to use With Check when re-enabling a check constraint, it accomplishes something entirely different than re-enabling the check constraint. By default for Alter Table, With Nocheck is used for re-enabling check constraints. Specifying With Check looks at all existing data to make sure it satisfies the check constraint. If you can safely assume that it already does, then you don’t need to specify that.

    I disable check constraints to get replicating dbs that have gotten out of sync back in sync.

  5. Hi,
    Suppose,I disable the index.
    Perform some task
    Again I enable it.
    when I enable the index again whether the data will be reindexed or it will remain same.
    If it gets reindexed how long will it take to reindex for 13 crore / 130 million record.

    Thanks.

  6. I am updating the tables in 13 databases on my one application. Many of them reffering to each other. I have disabled the constraints, updated tables and enabled.

    Just I want to know if any impact on database health by doing this changes in constraints.

    Response required.

    Thanks
    AKHILESH

  7. Pingback: SQL SERVER – Various Ways to Create Constraints – Quiz – Puzzle – 17 of 31 « SQL Server Journey with SQL Authority

  8. There is no mistake in your ‘check’ but if you check your text, there is some mistake.

    Let us have a look…
    “…. the world CHECK is used twice – WITH CHECK CHECK CONSTRAINT”

    Just go before the first check, world… it should be word but not world… ;)

    I know, its not a technical discussion but still a suggestion to correct it.. :)

  9. Hi,

    I need to alter a column datatype. so i have disabled all the constraints which are depending on the column. But still i could not run the alter statement. It gives
    Msg 5074, Level 16, State 1, Line 1
    The object ‘FK_something’ is dependent on column ‘ALIAS’.

    But if i run this script

    SELECT name, tbl = object_name(parent_obj)
    FROM sysobjects
    WHERE objectproperty(id, ‘CnstIsDisabled’) = 1

    it shows all the constrains which i was disabled.

    any one help me!

  10. When re-enabling the constraints, it’s imperative that you tell SQL Server to check the existing population:
    ALTER TABLE HumanResources.Employee WITH CHECK CHECK CONSTRAINT ALL;
    (Note that the double CHECK is *not* a typo!)

    Failing to do so, as in your code, makes the constraints non-trusted. The optimizer will now no longer use these constraints to optimize execution plans.

    More info at http://sqlblog.com/blogs/hugo_kornelis/archive/2007/03/29/can-you-trust-your-constraints.aspx

  11. hi pinal
    i was using this query to disable my check constraint and it is done successfully but while i wanted to enable this one it is showing an error saying that
    The ALTER TABLE statement conflicted with the CHECK constraint “ck_address_emp”. The conflict occurred in database
    please solve it…

  12. Pingback: SQL SERVER – Weekly Series – Memory Lane – #003 « SQL Server Journey with SQL Authority

  13. I did it when I had to import several related tables and didn’t want to bother with order of the importing. It was from one server, file transfer, flat file import…and since the files were different sizes, they arrived in different order so I disabled every check and simply imported the files as they arrived.

    @iDesi, it’s not typo, it works exactly as written

  14. Hi,
    I am currently a student at a University of Technology. I would like to know if I don’t want to delete, is it possible to “freeze/disable” a record in sql server 2008 R2?

  15. use swathy2
    create table paydept( emp_id int check(emp_id>0),firstnam varchar(50),lastnam varchar(50))
    insert into paydept values(’1′, ‘jam’,’varatha’)
    select * from paydept
    insert into paydept values(’2′, ‘dhana’, ‘lakshmi’)
    Alter table paydept
    drop check emp_id

    in this table for dropping the table error is coming

    like Incorrect syntax near the keyword ‘check’.

    please give me the reply how do i drop the check in this table

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s