You can use the code below to disable all CHECK and foreign key constraints for a specific table in SQL Server.

Just replace TableName with the name of the applicable table.

ALTER TABLE TableName NOCHECK CONSTRAINT ALL

Below is an example where I do this and then check the result.

Example 1 – Review the Constraints

First, I’ll take a quick look at the current CHECK and foreign key constraints in the database, to see whether or not they are enabled or disabled.

SELECT
  OBJECT_NAME(parent_object_id) AS 'Table',
  name AS 'Constraint',
  is_disabled, 
  is_not_trusted
FROM sys.foreign_keys
UNION
SELECT 
  OBJECT_NAME(parent_object_id),
  name,
  is_disabled, 
  is_not_trusted
FROM sys.check_constraints;

Result:

+----------------+-----------------+---------------+------------------+
| Table          | Constraint      | is_disabled   | is_not_trusted   |
|----------------+-----------------+---------------+------------------|
| ConstraintTest | chkPrice        | 0             | 0                |
| ConstraintTest | chkValidEndDate | 0             | 0                |
| ConstraintTest | chkTeamSize     | 0             | 0                |
| Occupation     | chkJobTitle     | 0             | 0                |
+----------------+-----------------+---------------+------------------+

So there are currently four CHECK constraints in the database, three of which are for the ConstraintTest table.

We can see that all constraints are enabled because is_disabled is set to 0.

Example 2 – Disable the Constraints

Now I’ll disable all constraints for the ConstraintTest table:

ALTER TABLE ConstraintTest 
NOCHECK CONSTRAINT ALL;

Example 3 – Check the Result

Now I run the same query from the first example to see the result.

SELECT
  OBJECT_NAME(parent_object_id) AS 'Table',
  name AS 'Constraint',
  is_disabled, 
  is_not_trusted
FROM sys.foreign_keys
UNION
SELECT 
  OBJECT_NAME(parent_object_id),
  name,
  is_disabled, 
  is_not_trusted
FROM sys.check_constraints;

Result:

+----------------+-----------------+---------------+------------------+
| Table          | Constraint      | is_disabled   | is_not_trusted   |
|----------------+-----------------+---------------+------------------|
| ConstraintTest | chkPrice        | 1             | 1                |
| ConstraintTest | chkValidEndDate | 1             | 1                |
| ConstraintTest | chkTeamSize     | 1             | 1                |
| Occupation     | chkJobTitle     | 0             | 0                |
+----------------+-----------------+---------------+------------------+

As expected, all three constraints for the ConstraintTest table have been disabled (because the is_disabled column is set to 1 for those constraints).

Notice that the is_not_trusted column is also set to 1. This is an important consideration, especially if you intend to re-enable any of your disabled constraints.

See What You Should Know about WITH NOCHECK when Enabling a CHECK Constraint in SQL Server for information about how to restore trust when re-enabling your constraints. The information in that article also applies to foreign keys.

Disable the Constraints Individually

If you don’t want to disable all constraints in the table, you can disable them individually. See How to Disable a CHECK Constraint in SQL Server and How to Disable a Foreign Key in SQL Server.

Re-enable the Constraints

If you need to re-enable all constraints for a table, see How to Enable ALL CHECK and Foreign Key Constraints for a Table.

If you need to re-enable them individually, see How to Enable a CHECK Constraint in SQL Server and and How to Enable a Foreign Key in SQL Server.