Log Shipping – NORECOVERY vs. STANDBY mode

Sometimes it's useful to disable one or more constraints on a table, do something significant like a BULK INSERT / DELETE, and then re-enable the constraint(s) once you're done.

You can disable / enable the constraint using the ALTER TABLE … NOCHECK/CHECK CONSTRAINT  …

Here is an example that disables all the constraints in a table

 

--disable all the constraints for Customer table 
ALTER TABLE Customer NOCHECK CONSTRAINT ALL
--do something 
--disable all the constraints for Customer table 
ALTER TABLE Customer CHECK CONSTRAINT ALL

Here is the one which disable /enable  a particular constraint

--disable the foreign key constraint FK_customer_countryId constraint 
ALTER TABLE Customer NOCHECK CONSTRAINT FK_customer_countryId
--do something
--enable the FK_customer_countryId constraint 
ALTER TABLE Customer CHECK CONSTRAINT FK_customer_countryId

 

Once you disable/enable a constraint, make sure to enable it after you are done with your BULK operation