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!