SQL CHECK Constraint

ALTER TABLE ADD CHECK Constraint Introduction

 We can use CHECK constraint when we need to limit value a user can insert into a table field. In my example I will show you how to set up CHECK Constraint on BirthDate field. In my example I will put a constraint so users cannot insert BirthDate that is in the future.
You can also use CHECK constraint with multiple fields in one table. Let's say you have StartDate and EndDate fields and you do not want to allow users to enter StartDate that is greater than EndDate.

ALTER TABLE ADD CHECK Syntax

See below syntax.
ALTER TABLE TableName 
ADD CONSTRAINT chk_ConstraintName CHECK (condition)
In the above example you can see that you need to specify table name, constraint name and condition that will be used to check if value is allowed or not.
Before we start see below table that contains BirthDate field that I will use to restrict values.

ALTER TABLE ADD CHECK Constraint Example

I create new query and type my code.
My constraint Name is chk_BirthDate and my condition is BirthDate <= GetDate()
NOTE: GetDate() uses local server date and you might have to use different function if you need precision in dates.
I run the query and get a messages box back.


In the Object Explorer, dbo.DimCustomer in a Constraints folder i can see that my constraint chk_BirthDate was added.


I hope that helps
Take care