Constraints
The integrity of a SQL Server database is ensured with the correct application of constraints, such as foreign keys, unique keys and check constraints. With these in place, any attempt to insert invalid data into a table is refused with an error report indicating which constraint prevented the action.
In some situations you may wish to insert data that would break the integrity of the database, knowing that further actions that you are about to take will remedy the problem. For example, you may be performing bulk operations on related tables that cause foreign key constraints to be breached. Perhaps you could avoid errors by processing rows individually and ensuring that changes in one table are reflected in related tables immediately. However, this may be a much slower process than performing bulk actions on one table at a time. In such situations you can temporarily disable individual constraints or all rules for a table.
NB: The commands described in this article should be used with care. With constraints disabled it is possible to create invalid data. These techniques should be used for maintenance of data only and not as part of a business process. You should consider using single user mode to ensure other users cannot damage data.
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
17 March 2011