If you have a CHECK constraint in SQL Server that is currently disabled, you can use the code below to re-enable it.

When you enable a CHECK constraint (or a foreign key constraint for that matter), you have the option to specify whether or not to check any existing data in the table.

Below are code examples of enabling a CHECK constraint, while specifying each of these different options.

Example 1 – Enable a Constraint using WITH CHECK

This is the recommended method of enabling your CHECK constraints (unless you have a specific reason not to use it).

Here’s an example of enabling a constraint called chkJobTitle:

ALTER TABLE Occupation  
WITH CHECK CHECK CONSTRAINT chkJobTitle;

Here I explicitly state WITH CHECK, which tells SQL Server to check the existing data before enabling the constraint. If any data violates the constraint, the constraint won’t be enabled and you’ll get an error.

This is good, because it enforces data integrity.

When you create a new CHECK constraint, this is the default setting. However, when you enable an existing constraint (as we’re doing here), it’s not the default setting.

Example 2 – Enable a Constraint using WITH NOCHECK

In this example the constraint is enabled without checking the existing data:

ALTER TABLE Occupation  
WITH NOCHECK CHECK CONSTRAINT chkJobTitle;

Here I explicitly state WITH NOCHECK, which tells SQL Server not to check the existing data. This means that the constraint will be enabled even if the table already contains data that violates the constraint.

This is the default setting when enabling a constraint (but not when creating one).

One of the few reasons (probably the only reason) you would use this is if you want to keep invalid data in the database. Perhaps you have a one-off exception where you must enter a row or more of invalid data, but you require all future data to conform to the constraint.

However, there are still risks associated with doing this. Here’s what Microsoft has to say about this:

We don’t recommend doing this, except in rare cases. The new constraint is evaluated in all later data updates. Any constraint violations that are suppressed by WITH NOCHECK when the constraint is added may cause future updates to fail if they update rows with data that doesn’t follow the constraint.

So using WITH NOCHECK could potentially cause issues later on.

Example 3 – Enable a Constraint using the Default Option

Here’s an example using the default option:

ALTER TABLE Occupation  
CHECK CONSTRAINT chkJobTitle;

This example is the equivalent of the previous example. Because I didn’t specify whether or not to check, SQL Server assumes I want WITH NOCHECK.

Using WITH NOCHECK Removes Trust

When you enable a constraint using WITH NOCHECK, one consequence you should be aware of is that SQL Server no longer trusts that constraint. It flags it as not trusted.

Yes you read that right. There’s actually a is_not_trusted flag that SQL Server sets to 1 when you disable a CHECK constraint (which means it’s not trusted), and the only way to set it to 0 (trusted) is to specify WITH CHECK when re-enabling the constraint. Using WITH NOCHECK just doesn’t cut it.

This makes perfect sense. After all, would you trust a constraint that hasn’t checked all the data?

By using WITH CHECK, you ensure that the constraint checks all existing data before its enabled. The only way it can be enabled is if all existing data conforms to the constraint. Once it has checked all existing data, it can then be trusted.

For more about this, see What You Should Know about WITH NOCHECK when Enabling a CHECK Constraint in SQL Server, where you can see the actual is_not_trusted flag being toggled back and forth each time I disable and re-enable a CHECK constraint.