Check Constraint In MSSQL Server:

Check Constraint In MSSQL Server:

First we will Create a simple table as follows

CREATE TABLE Course
(
CourseId int Primary key identity(1,1),
CourseName Varchar(200) not null,
StartDate datetime,
EndDate datetime,
Deleted char(1) Constraint Check_Deleted CHECK ( Deleted in ('Y','N'))
)
Try To Check If Constraint On Deleted column is enforced or not as follows
Try Running Following Query:
insert into Course(CourseName,StartDate,EndDate,Deleted) values ('C',getdate(),DATEADD(month,6,getdate()),'Q')

The Message Window Bellow Will Flash Error Message:
The INSERT statement conflicted with the CHECK constraint "Check_Deleted". The conflict occurred in database "YourDataBaseName", table "dbo.Course", column 'Deleted'.
The statement has been terminated.

Another Way is You Just Create a table without Check Constraint and then alter your table to add the check constraint. If You Already Created Table with name ‘Course’ just drop it to find out another way else try changing table name of the new table.
Here Is Sample Code:
CREATE TABLE Course
(
CourseId int Primary key identity(1,1),
CourseName Varchar(200) not null,
StartDate datetime,
EndDate datetime,
Deleted char(1)
)

We Created a table without Check constraint Now following Line will add check constraint on Deleted column.

ALTER Table Course add constraint Check_Deleted CHECK(Deleted in ('Y','N'))
The End Result is Same Again Try Running Query bellow
insert into Course(CourseName,StartDate,EndDate,Deleted) values ('C',getdate(),DATEADD(month,6,getdate()),'Q')

How Do I get rid of the Check constraint I Just Added?
If we just wish to keep the check constraint quite for the time you clear the mess we can do it as follows
ALTER TABLE Course NOCHECK CONSTRAINT Check_Deleted
This will temporary disable the enforcing of check constraint.So we can now get this time to insert the following record which other wise never passed the hurdle.
insert into Course(CourseName,StartDate,EndDate,Deleted) values ('C',getdate(),DATEADD(month,6,getdate()),'Q')
This Time Query Runs Successfully.
How Do I Enable the Check Constraint if it is Disabled Like Above:
One can re-enable the disabled check constraint as follows
ALTER TABLE Course CHECK CONSTRAINT Check_Deleted
After consraint is enabled following query will not execute successfully as it violate our check constraint
insert into Course(CourseName,StartDate,EndDate,Deleted) values ('C',getdate(),DATEADD(month,6,getdate()),'Q')
Well now Time to Remove the Constraint All Together, How Do I Go At It?
Removing a Check constraint from your column is simple we have run a simple table alter statement as follows
ALTER TABLE Course DROP CONSTRAINT Check_Deleted