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 (https://blog.sqlauthority.com)
23 Comments. Leave new
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.
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.
@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.
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.
correct!
how to use Check constraint in If conditions
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.
How to maintain check constraint with two databases say db1 and db2?
How to check status of column constraints.(Enable or disabled).
How to check status of column constraint ( enabled or disabled)
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
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.. :)
Thanks pinal sir..
Its very helpful…
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!
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
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…
There’s a typo in your blog post. “World CHECK is used twice” should be “word”. Very helpful blog though :)
There’s a typo in your blog entry. “World CHECK is used twice” should be “word”. A very helpful blog though. :)
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
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?
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