When you attempt to enter data into a table that has a fully enabled CHECK constraint, you will only be successful if the data doesn’t violate that constraint. If you attempt to enter invalid data, the operation will fail with an error.

But what if you find yourself in the situation where you really must insert data that will violate the CHECK constraint? Perhaps the constraint no longer applies, or maybe you have an exception where one row is allowed to bypass the constraint. Either way, you won’t be able to enter anything outside the rules of the constraint.

If you find yourself in this situation, you can always disable the constraint. Here’s how to do that using Transact-SQL.

Example 1 – Disable the CHECK Constraint

To disable a CHECK constraint, use the NOCHECK argument within an ALTER TABLE statement.

Like this:

ALTER TABLE Occupation  
NOCHECK CONSTRAINT chkJobTitle; 

This code disables a constraint called chkJobTitle.

Example 2 – Review the CHECK Constraint

We can query the sys.check_constraints system view to verify that our constraint has been disabled:

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')          |
+-----------------+---------------+------------------+----------------------------------------+

In this case I selected all CHECK constraints from the current database.

We can see that this is the only one that’s 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.

In other words, we can no longer assume that the constraint has checked all the data. The fact that the constraint is disabled means that data is now able to get into the database without being checked by the constraint. Therefore, the potential exists for invalid data to be present in the database.

If you ever need to re-enable the CHECK constraint, you will have the opportunity to restore the constraint’s trust (by using the WITH CHECK option). This will check all existing rows before enabling the constraint.

You will also have the option of not checking the existing data, but this should only be done in rare cases.

See What You Should Know about WITH NOCHECK when Enabling a CHECK Constraint in SQL Server for a demonstration of how trust is affected, depending on how you re-enable the constraint.