What You Should Know about WITH NOCHECK when Enabling a CHECK Constraint in SQL Server

Posted on by Ian

If you ever find yourself in the situation where you need to re-enable a CHECK constraint that has previously been disabled, you should definitely make sure that you know what you’re doing.

In particular, you should understand the difference between WITH NOCHECK and WITH CHECK arguments.

These arguments can be used at the time you enable the constraint. They specify whether or not existing data is validated against your re-enabled (or newly added) CHECK constraint. Basically you have the option of checking all existing data for any violations against the constraint. If you don’t specify anything, existing data won’t be checked. That’s why it’s important to understand how it works.

By the way, these arguments also apply to foreign key constraints.

As you might expect, WITH CHECK specifies that existing data is validated and WITH NOCHECK specifies that it isn’t. The default is WITH NOCHECK.

If you use WITH NOCHECK, the constraint will be flagged as untrusted. Actually, it’s flagged as untrusted when you disable the constraint. But when you re-enable it, it will remain untrusted unless you use WITH CHECK. In other words, if you want to restate its “trustworthiness”, you must explicitly specify this.

In other words:

  • When you use WITH NOCHECK, the constraint will remain untrusted.
  • When you use WITH CHECK it will become trusted, but only if all existing data conforms to the constraint. If any existing data violates the constraint, then the constraint will not be enabled and you’ll receive an error message.

Of course, when I say “all existing data” I’m only referring to data that the constraint applies to.

There may be scenarios where you intentionally disabled a constraint because you had to enter data that violates the constraint. In such cases, if the invalid data must remain in the database, you’ll need to use WITH NOCHECK if you want to re-enable the constraint. This will allow you to enable the constraint without any existing data getting in the way.

Below are examples that demonstrate this.

Example 1 – Review CHECK Constraints

First, let’s use the sys.check_constraints to take a look at all CHECK constraints in the current database.

SELECT 
  name,
  is_disabled,
  is_not_trusted,
  definition
FROM sys.check_constraints;

Result:

+-----------------+---------------+------------------+----------------------------------------+
| name            | is_disabled   | is_not_trusted   | definition                             |
|-----------------+---------------+------------------+----------------------------------------|
| chkPrice        | 0             | 0                | ([Price]>(0))                          |
| chkValidEndDate | 0             | 0                | ([EndDate]>=[StartDate])               |
| chkTeamSize     | 0             | 0                | ([TeamSize]>=(5) AND [TeamSize]<=(20)) |
| chkJobTitle     | 0             | 0                | ([JobTitle]<>'Digital Nomad')          |
+-----------------+---------------+------------------+----------------------------------------+

We can see that they are all enabled and trusted (because they all have zeros in the is_disabled and is_not_trusted columns).

For this article, I’ll be disabling and re-enabling the chkJobTitle constraint.

Example 2 – Disable the Constraint

Here, I disable the chkJobTitle constraint:

ALTER TABLE Occupation  
NOCHECK CONSTRAINT chkJobTitle;

Done.

Now let’s review all constraints again:

SELECT 
  name,
  is_disabled,
  is_not_trusted,
  definition
FROM sys.check_constraints;

Result:

+-----------------+---------------+------------------+----------------------------------------+
| name            | is_disabled   | is_not_trusted   | definition                             |
|-----------------+---------------+------------------+----------------------------------------|
| chkPrice        | 0             | 0                | ([Price]>(0))                          |
| chkValidEndDate | 0             | 0                | ([EndDate]>=[StartDate])               |
| chkTeamSize     | 0             | 0                | ([TeamSize]>=(5) AND [TeamSize]<=(20)) |
| chkJobTitle     | 1             | 1                | ([JobTitle]<>'Digital Nomad')          |
+-----------------+---------------+------------------+----------------------------------------+

We can see that it’s been disabled (because its is_disabled column is set to 1).

You might notice that the is_not_trusted column is also set to 1. This indicates that the CHECK constraint has not been verified by the system for all rows.

As mentioned, a CHECK constraint can only be trusted if all data has successfully passed the constraint’s conditions. When we disable a constraint, this opens up the potential for invalid data to enter the database. Therefore we can’t be 100% certain that all data is valid, hence the constraint being flagged as not trusted.

The way to ensure the constraint is trusted again is to re-enable it using the WITH CHECK argument. This will cause the constraint to check all data before it’s re-enabled. If any data is invalid, it won’t be able to be re-enabled. You’ll either need to update the data so that it’s valid, or re-enable the constraint using the WITH NOCHECK argument instead (which will cause the constraint to remain untrusted).

Example 3 – Enable the Constraint using the Default Settings (WITH NOCHECK)

Let’s re-enable the constraint and run the query again.

To enable the constraint, I’ll be lazy and use the default settings:

ALTER TABLE Occupation  
CHECK CONSTRAINT chkJobTitle;

Now verify the change:

SELECT 
  name,
  is_disabled,
  is_not_trusted,
  definition
FROM sys.check_constraints;

Result:

+-----------------+---------------+------------------+----------------------------------------+
| name            | is_disabled   | is_not_trusted   | definition                             |
|-----------------+---------------+------------------+----------------------------------------|
| chkPrice        | 0             | 0                | ([Price]>(0))                          |
| chkValidEndDate | 0             | 0                | ([EndDate]>=[StartDate])               |
| chkTeamSize     | 0             | 0                | ([TeamSize]>=(5) AND [TeamSize]<=(20)) |
| chkJobTitle     | 0             | 1                | ([JobTitle]<>'Digital Nomad')          |
+-----------------+---------------+------------------+----------------------------------------+

Did you see what just happened? Even though I enabled the constraint again, it’s still not trusted.

This is because I was lazy (or maybe just forgetful) when I enabled the constraint. When I enabled the constraint, I forgot to specify WITH CHECK. The default is WITH NOCHECK which means that existing data is not checked when re-enabling the constraint.

This is why you should definitely know what you’re doing when enabling CHECK (and FOREIGN KEY) constraints. By being lazy and not explicitly specifying a potentially important setting, we give SQL Server permission to turn a blind eye to any issues with existing data.

However, if the whole reason you needed to disable the constraint is to insert data that violates the constraint, then the default WITH NOCHECK is probably what you want.

By the way, for new constraints, the default is WITH CHECK.

But in my case, I didn’t insert or update any data after disabling the constraint, so if it was trustworthy before, it should still be trustworthy now.

So how can I get my constraint trusted again?

Example 4 – Enable the Constraint using WITH CHECK

If I want my constraint to be trusted again, I need to explicitly specify WITH CHECK when re-enabling it.

Let’s disable the constraint again:

ALTER TABLE Occupation  
NOCHECK CONSTRAINT chkJobTitle;

So now I’m back to where I was before I re-enabled it.

What I should have done when I re-enabled it was this:

ALTER TABLE Occupation  
WITH CHECK CHECK CONSTRAINT chkJobTitle;

Now take another look at the constraint:

SELECT 
  name,
  is_disabled,
  is_not_trusted,
  definition
FROM sys.check_constraints;

Result:

+-----------------+---------------+------------------+----------------------------------------+
| name            | is_disabled   | is_not_trusted   | definition                             |
|-----------------+---------------+------------------+----------------------------------------|
| chkPrice        | 0             | 0                | ([Price]>(0))                          |
| chkValidEndDate | 0             | 0                | ([EndDate]>=[StartDate])               |
| chkTeamSize     | 0             | 0                | ([TeamSize]>=(5) AND [TeamSize]<=(20)) |
| chkJobTitle     | 0             | 0                | ([JobTitle]<>'Digital Nomad')          |
+-----------------+---------------+------------------+----------------------------------------+

Phew! My constraint is trustworthy once again.

Example 5 – Enable the CHECK Constraint with Invalid Data

Of course, my constraint is only trusted again because I didn’t insert invalid data while it was disabled. If I had done this, I wouldn’t be able to enable it using WITH CHECK, as demonstrated below.

If I disable it again:

ALTER TABLE Occupation  
NOCHECK CONSTRAINT chkJobTitle;

Now insert invalid data (and return the results):

INSERT INTO Occupation
VALUES ( 7, 'Digital Nomad' );

SELECT 
  OccupationId,
  JobTitle
FROM Occupation;

Result:

+----------------+-----------------+
| OccupationId   | JobTitle        |
|----------------+-----------------|
| 1              | Engineer        |
| 2              | Accountant      |
| 3              | Cleaner         |
| 4              | Attorney        |
| 5              | Sales Executive |
| 6              | Uber Driver     |
| 7              | Digital Nomad   |
+----------------+-----------------+

So we successfully inserted invalid data (last row).

This is invalid because the constraint definition goes as follows: ([JobTitle]<>'Digital Nomad')

This means that the JobTitle column must not contain the text Digital Nomad.

Now let’s try to re-enable the CHECK constraint using WITH CHECK and see what happens.

ALTER TABLE Occupation  
WITH CHECK CHECK CONSTRAINT chkJobTitle;

Result:

Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the CHECK constraint "chkJobTitle". The conflict occurred in database "Test", table "dbo.Occupation", column 'JobTitle'.

So we can’t re-enable the constraint using WITH CHECK while we’ve got data in the table that violates the CHECK constraint. Either we need to update the data or we need to use WITH NOCHECK (or simply omit it altogether).

Let’s try it again using WITH NOCHECK.

ALTER TABLE Occupation  
WITH NOCHECK CHECK CONSTRAINT chkJobTitle;

Result:

Commands completed successfully.
Total execution time: 00:00:00.015

So we can successfully enable the constraint if we don’t check the existing data.

Of course, in this case the CHECK constraint is still not trusted. If we want the constraint to be trusted, we’ll need to update the data so that it doesn’t violate the constraint.

Example:

UPDATE Occupation
SET JobTitle = 'Unemployed'
WHERE OccupationId = 7;

SELECT 
  OccupationId,
  JobTitle
FROM Occupation;

Result:

+----------------+-----------------+
| OccupationId   | JobTitle        |
|----------------+-----------------|
| 1              | Engineer        |
| 2              | Accountant      |
| 3              | Cleaner         |
| 4              | Attorney        |
| 5              | Sales Executive |
| 6              | Uber Driver     |
| 7              | Unemployed      |
+----------------+-----------------+

Now we can alter the CHECK constraint to become trusted again.

Let’s do all three together:

ALTER TABLE Occupation  
NOCHECK CONSTRAINT chkJobTitle; 

ALTER TABLE Occupation  
WITH CHECK CHECK CONSTRAINT chkJobTitle; 

SELECT 
  name,
  is_disabled,
  is_not_trusted,
  definition
FROM sys.check_constraints;

Result:

+-----------------+---------------+------------------+----------------------------------------+
| name            | is_disabled   | is_not_trusted   | definition                             |
|-----------------+---------------+------------------+----------------------------------------|
| chkPrice        | 0             | 0                | ([Price]>(0))                          |
| chkValidEndDate | 0             | 0                | ([EndDate]>=[StartDate])               |
| chkTeamSize     | 0             | 0                | ([TeamSize]>=(5) AND [TeamSize]<=(20)) |
| chkJobTitle     | 0             | 0                | ([JobTitle]<>'Digital Nomad')          |
+-----------------+---------------+------------------+----------------------------------------+

So now our constraint is enabled and trustworthy once again, and our database is free from digital nomads!