Joe Webb

Musing and observations about SQL Server, other technogies, and sometimes just life in general
posts - 85, comments - 857, trackbacks - 0

My Links

News

This blog has moved!

Click here for the new location.

Follow me on Twitter
Add to Technorati Favorites

Search this Blog
 




Archives

Post Categories

About me

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

Print | posted on Wednesday, October 01, 2008 10:57 AM | Filed Under [ T-SQL ]

Feedback

Gravatar

# 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
10/2/2008 3:30 AM | Hugo Kornelis
Gravatar

# 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
10/2/2008 7:28 AM | Joe Webb
Gravatar

# 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
10/16/2008 7:49 PM | Turkey
Gravatar

# 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.
10/16/2010 3:59 PM | columbia jackets
Gravatar

# 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
10/19/2010 4:30 AM | furry boots
Gravatar

# 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
10/23/2010 4:01 AM | gabbanadolce.org
Gravatar

# 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
10/24/2010 3:03 PM | north face jackets on sale
Gravatar

# 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,
10/26/2010 1:33 AM | hanly
Gravatar

# Replicas watches

ehow give me cartier replica me closure. Absolutely
11/14/2010 7:26 PM | ssssssssss
Gravatar

# re: How to: Disable constraints on a table

Thanks a lot!
2/8/2011 12:50 PM | Eduardo R. Marcelino
Gravatar

# re: How to: Disable constraints on a table

Good Article..Helps me a lot
7/2/2011 1:45 AM | Hardik Gheewala
Gravatar

# re: How to: Disable constraints on a table

Thank you for posting and keep up the good work
8/2/2011 12:42 AM | registryspeeder
Gravatar

# re: How to: Disable constraints on a table

cheers, helped me out today. also thanks to hugo for the extra comment
8/9/2011 1:07 AM | Andrej Hribernik
Gravatar

# 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
12/23/2011 7:58 AM | Bipin Sinha
Gravatar

# 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
12/27/2011 10:07 AM | Bipin Sinha
Gravatar

# 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.

1/2/2012 2:01 AM | Parmod Bhardwaj
Gravatar

# re: How to: Disable constraints on a table

bla bla
3/17/2012 3:33 AM | bb
Gravatar

# re: How to: Disable constraints on a table

Thank you sir. These are very useful example for me.
3/23/2012 10:44 PM | lokesh
Gravatar

# re: How to: Disable constraints on a table

www.gravatar.com/avatar.php
Gravatar

# re: How to: Disable constraints on a table

Please enter a comment
9/12/2012 1:08 AM | Please enter your name
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET