You can use the code below to enable all CHECK and foreign key constraints for the current database in SQL Server.

When you enable a CHECK or foreign key constraint, you have the option of checking existing data in the table before the constraint is enabled. Doing this allows you to verify whether or not any existing violates the constraint. To perform this check, use WITH CHECK within the code, otherwise use WITH NOCHECK.

Sample Code

Here’s how to enable all CHECK and foreign key constraints within a database. The first example checks existing data, the second doesn’t.

With Check (recommended):

EXEC sp_MSforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL"

Without Check:

EXEC sp_MSforeachtable "ALTER TABLE ? WITH NOCHECK CHECK CONSTRAINT ALL"

You can also explicitly provide the argument name (@command1) if you prefer (you’ll get the same result either way).

With Check:

EXEC sp_MSforeachtable @command1="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL"

Without Check:

EXEC sp_MSforeachtable @command1="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL"

These examples use the (undocumented) sp_MSforeachtable stored procedure. This procedure allows you to perform tasks against each table in a database. So it’s perfect for our task here – to enable all CHECK and foreign key constraints within the current database.

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        | 1             | 1                |
| ConstraintTest | chkValidEndDate | 1             | 1                |
| ConstraintTest | chkTeamSize     | 1             | 1                |
| Occupation     | chkJobTitle     | 1             | 1                |
+----------------+-----------------+---------------+------------------+

So there are currently four CHECK constraints constraints in the database, for two different tables.

We can see that all constraints are disabled, because is_disabled is set to 1.

Also, they’re all untrusted, because is_not_trusted is also set to 1.

Example 2 – Enable the Constraints using WITH CHECK

Now I’ll enable all constraints using the WITH CHECK argument:

EXEC sp_MSforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL"

It’s always a good idea to make sure you’re using the correct database when doing this type of thing. So we could modify the code by first switching to the correct database:

USE Test;
EXEC sp_MSforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL"

In this case I switch to a database called Test before executing the stored procedure.

Example 3 – Check the Result

Having run the above code, now I’ll 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        | 0             | 0                |
| ConstraintTest | chkValidEndDate | 0             | 0                |
| ConstraintTest | chkTeamSize     | 0             | 0                |
| Occupation     | chkJobTitle     | 0             | 0                |
+----------------+-----------------+---------------+------------------+

So all constraints in the database have now been enabled (because the is_disabled column is set to 0 for all constraints).

We can also see that the is_not_trusted column is also set to 0. This means the constraint is trusted. It’s trusted, because we got it to check all existing data before being enabled.

If I’d used WITH NOCHECK, the constraints would remain untrusted (i.e. their is_not_trusted flag would be set to 1). This is because the database could potentially contain data that violates one (or more) of the constraints (invalid data could have entered the database while the constraints were disabled).

On rare occasions, you may need to keep invalid data in the database. In such cases, the constraint will need to remain untrusted, because the existing data wouldn’t pass the initial check and therefore the constraint would not be able to be enabled unless it uses WITH NOCHECK.

See What You Should Know about WITH NOCHECK when Enabling a CHECK Constraint in SQL Server for a detailed example of switching between trusted and untrusted when disabling and re-enabling a constraint.

Enable the Constraints Individually

If you only want to enable the constraints one-by-one, see How to Enable a CHECK Constraint in SQL Server and How to Enable a Foreign Key in SQL Server.