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

columbia jackets
2010-10-16
re: How to: Disable constraints on a table
Specifies that all constraints are either disabled with the NOCHECK option or enabled with the CHECK option.

furry boots
2010-10-19
re: How to: Disable constraints on a table
Specifies that all constraints are either disabled with the NOCHECK option or enabled with the CHECK option.

snow boots | snow boots for women | columbia sportswear | columbia sportswear outlet | cheap north face jackets | the north face outlet | mac makeup | cheap makeup

gabbanadolce.org
2010-10-23
re: How to: Disable constraints on a table
utely Tag Heuer Link Mens stops you cold," she said.
Jerry d&g ladies watches was at the peak of adidas mens shoes his career as a successful women's Nike casual shoes construction executive, but he was Tag Heuer Aquaracer Womens laid off when his mem

north face jackets on sale
2010-10-24
re: How to: Disable constraints on a table
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.

snow boots for women | columbia jackets | mac makeup | the north face outlet

hanly
2010-10-26
re: How to: Disable constraints on a table
This super video converter for mac is developed by Emicsoft Studio, it is currently the best video converter running under Mac os x, comparied by isqunite, Visualhub and other Video Converter for Mac Free vide under "format" option choose the output video formats by your needs, such as to mp4 for ipod, iphone,

ssssssssss
2010-11-14
Replicas watches
ehow give me cartier replica me closure. Absolutely

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

registryspeeder
2011-08-02
re: How to: Disable constraints on a table
Thank you for posting and keep up the good work

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.

http://www.gravatar.com/avatar.p
2012-07-05
re: How to: Disable constraints on a table
www.gravatar.com/avatar.php

Please enter your name
2012-09-12
re: How to: Disable constraints on a table
Please enter a comment