CHECK constraint is to specify a predicate to a column or multiple columns that allows the data values that are acceptable. If the value is not satisfying the condition, the record violates the constraint and the operation will be ignored.

Let us quickly look at few of its usage with some examples as below.

create table testcheck
(
	ID int NOT NULL,
	Name varchar(50),
	Gender varchar(50) check (Gender in ('Male','Female'))
)

Insert into testcheck values(1,'Latheesh','Male')--Success row
Insert into testcheck values(1,'SQL Server','') --Failed row

Is it possible to drop a constraint?

Yes, we can very well drop a check constraint using alter table statement as below.

--Drop the constraint
ALTER TABLE testcheck DROP CONSTRAINT CK__testcheck__Gende__29572725;
--retry with no value for gender
Insert into testcheck values(1,'SQL Server','')

Does the constraint check the existing data?

Yes, when we create a constraint, it check the existing value by default.

--Add the constraint back
ALTER TABLE testcheck ADD CONSTRAINT CK__testcheck__Gende__29572725 CHECK (Gender in ('Male','Female') );

Is it possible to enable and disable a constraint?

Yes, very well.

Select * From testcheck
--Disable the constraint
ALTER TABLE testcheck NOCHECK CONSTRAINT CK__testcheck__Gende__29572725;
--Insert a value that does not satisfy
Insert into testcheck values(1,'SQL Server','')
Select * From testcheck

Now, let us enable the constraint back and see the interesting behavior.

--Enable the constraint back
ALTER TABLE testcheck CHECK CONSTRAINT CK__testcheck__Gende__29572725; --This would not check the existing data
Select * From testcheck
Insert into testcheck values(1,'dummy','')
Select * From testcheck

We can see that the CHECK CONSTRAINT operation is enabling the constraint and it violates the next insert statement as expected. However, if you notice the existing data, there is a violated data, which is not evaluated. Quite interesting, right? So, we need a different way to enable the constraint that also verifies the existing data. Here we go with the usage of WITH CHECK.

-- Enable the constraint properly
ALTER TABLE testcheck WITH CHECK CHECK CONSTRAINT CK__testcheck__Gende__29572725 --This would also check the existing data

The above shows that WITH CHECK actually evaluates the existing data as well. Now, let us clean up the data and re-enable successfully.

--Existing data clean up
Delete from testcheck where gender not in ('male','Female')
-- Enable the constraint properly
ALTER TABLE testcheck WITH CHECK CHECK CONSTRAINT CK__testcheck__Gende__29572725 --This time, this would succeed

When you disable a constraint using WITH NOCHECK, SQL Server is making a change to constraint to no longer trusts that constraint. It flags it as “not trusted” in metadata. Eventually, the only way to set it to trusted is by specifying the constraint WITH CHECK while enabling the constraint.

If you enjoyed this blog post, feel free to share it with your friends!