Modify a CHECK Constraint in SQL Server using T-SQL

by

If you already have an existing CHECK constraint in SQL Server, but you need to modify it, you’ll need to drop it and recreate it. There’s no ALTER CONSTRAINT statement or anything similar.

So to “modify” an existing constraint:

  1. Drop the constraint using ALTER TABLE with DROP CONSTRAINT.
  2. Create the new constraint using ALTER TABLE with ADD CONSTRAINT.

Example

Here’s an example of dropping and recreating a CHECK constraint.

ALTER TABLE ConstraintTest 
  DROP CONSTRAINT chkTeamSize;

ALTER TABLE ConstraintTest
  ADD CONSTRAINT chkTeamSize 
  CHECK (TeamSize >= 5 AND TeamSize <= 20)
  ;

As mentioned, you can’t modify it – you need to drop it and create it with the new definition.

In this case, the constraint is called chkTeamSize and I simply drop it and create it with the new definition.

A Note on Ordering

Note that CHECK constraints are validated in the order that they’re created, so dropping/recreating a constraint may cause it to be validated in a different order than previously. This may result in other errors being caught before this constraint, when they were previously caught after.