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)
pinaldave
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.