In this Tutorial I will show you how create CHECK Constraint . For the purpose of this tutorial I will use SQL Server 2012.
Other Versions of SQL Server:
- SQL Server 2012
- This blog post has been written using SQL Server 2012
- SQL Server 2008 / R2
- The examples we used should work in SQL Server 2008 and R2
- SQL Server 2005
- We suspect that this will work in SQL Server 2005 as well
Who is it for?
- People who would like to learn basics of using CHECK Constraint.
- Those who are preparing for Microsoft certifications and need to cover this topic
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.
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