Joe Webb Blog

Musing and observations about SQL Server, other technogies, and sometimes just life in general

How to: Disable constraints on a table

Sometimes it's useful to disable one or more constraints on a table, do something significant, and then re-enable the constaint(s) after you're done. This is most often done to improve performance during a bulk load operation.

According to SQL Server Books Online, we can disable constraints using the ALTER TABLE statement.Here's an excerpt from SQL Server Books Online that describes it.

{ CHECK | NOCHECK } CONSTRAINT

    Specifies that constraint_name is enabled or disabled. This option can only be used with FOREIGN KEY and CHECK constraints. When NOCHECK is specified, the constraint is disabled and future inserts or updates to the column are not validated against the constraint conditions. DEFAULT, PRIMARY KEY, and UNIQUE constraints cannot be disabled.

ALL

    Specifies that all constraints are either disabled with the NOCHECK option or enabled with the CHECK option.

For more information, see the "Alter Table (Transact-SQL)" topic in SQL Server Books Online (http://technet.microsoft.com/en-us/library/ms190273.aspx).

Let's consider an example.The following script disables a single constraint, does something, and then re-enables the constraint using the ALTER TABLE statement.

–disable the CK_Customer_CustomerType constraint 
ALTER
TABLE Sales.Customer NOCHECK CONSTRAINT CK_Customer_CustomerType

–do something

–enable the CK_Customer_CustomerType constraint
ALTER
TABLE Sales.Customer CHECK CONSTRAINT CK_Customer_CustomerType

 

The following example shows how to disable all constraints on a table.

–disable all constraints for the Sales.SalesOrderHeader table 
ALTER
TABLE Sales.SalesOrderHeader NOCHECK CONSTRAINT ALL

–do something –enable all constraints for the Sales.SalesOrderHeader table
ALTER TABLE Sales.SalesOrderHeader CHECK CONSTRAINT ALL

Cheers!

Joe

kick it on DotNetKicks.com

Legacy Comments


Hugo Kornelis
2008-10-02
re: How to: Disable constraints on a table
Joe,

When re-enabling the constraints, it's imperative that you tell SQL Server to check the existing population:
ALTER TABLE Sales.SalesOrderHeader 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

Best, Hugo

Joe Webb
2008-10-02
re: How to: Disable constraints on a table
Excellent point, Hugo. I would have done well to mention that in the post. Thanks for pointing it out in your comment.

Cheers!

Joe

Turkey
2008-10-16
re: How to: Disable constraints on a table
great an article, I'm very thank you, I think really usefull, I'm following comments, thanks

Eduardo R. Marcelino
2011-02-08
re: How to: Disable constraints on a table
Thanks a lot!

Hardik Gheewala
2011-07-02
re: How to: Disable constraints on a table
Good Article..Helps me a lot

Andrej Hribernik
2011-08-09
re: How to: Disable constraints on a table
cheers, helped me out today. also thanks to hugo for the extra comment

Bipin Sinha
2011-12-23
re: How to: Disable constraints on a table
This is best one article so far I have read online. I would like to appreciate you for making it very simple and easy. I have found another nice post related to this post over the internet which also explained very well. For more details you may check it by visiting this url.

mindstick.com/...

Thanks

Bipin Sinha
2011-12-27
re: How to: Disable constraints on a table
This is best one article so far I have read online. I would like to appreciate you for making it very simple and easy. I have found another nice post related to this post over the internet which also explained very well. For more details you may check it by visiting this url.
mindstick.com/...

Thanks

Parmod Bhardwaj
2012-01-02
re: How to: Disable constraints on a table
To disable a check constraint with INSERT and UPDATE statements
1.In Object Explorer, right-click the table with the constraint, and click Design.

The table opens in Table Designer.

2.From the Table Designer menu, click Check Constraints.

3.In the Check Constraints dialog box, select the constraint in the Selected Check Constraint list.

4.In the grid, click Enforce For INSERTS And UPDATES and choose No from the drop-down list.

You can set this option to Yes after you add or modify data to guarantee that the constraint applies to subsequent data modifications.


bb
2012-03-17
re: How to: Disable constraints on a table
bla bla

lokesh
2012-03-23
re: How to: Disable constraints on a table
Thank you sir. These are very useful example for me.