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