Disabling All Constraints on a Table

When there are several constraints on a table you can disable them all with one statement. To do so, use the ALTER TABLE command, providing the name of the table and the clause, NOCHECK CONSTRAINT ALL. For example, the following disables all of the constraints for the CustomerAddresses table:

ALTER TABLE CustomerAddresses NOCHECK CONSTRAINT ALL

To re-enable the constraints, issue use the same command, substituting NOCHECK with CHECK, as follows:

ALTER TABLE CustomerAddresses CHECK CONSTRAINT ALL

Disabling Individual Constraints

Sometimes you will wish to disable one constraint only. You can use the same ALTER TABLE command but instead of specifying ALL, provide the name of the constraint that you wish to remove. The following disables the FK_CustomerAddresses_Customers constraint on the CustomerAddresses table.

ALTER TABLE CustomerAddresses NOCHECK CONSTRAINT FK_CustomerAddresses_Customers

To re-enable the constraint change NOCHECK to CHECK:

ALTER TABLE CustomerAddresses CHECK CONSTRAINT FK_CustomerAddresses_Customers