How To Add a Check Constraint in SQL Server

I am going to explain how to add a check constraint to your table in SQL Server.

First of all, I have this table called tblPerson below and I want to add a CHECK constraint.

What is a CHECK constraint?

A check constraint is used to limit the range of the values that can be entered into a column.

For example, you don't want to allow negative values for integers like for the Age column. You don't want to enter a value of 120 because it is impractical for a person to live beyond the age of 100.

The General formula for entering a CHECK constraint in SQL Server:

ALTER {table_name}

ADD CONSTRAINT{CONSTRAINT_NAME} CHECK{BOOLEAN EXPRESSION}

I have included an example below to help you put in a constraint.

--How to view a table--

select * from tblPerson

ID     Name   Email      GenderID  Age    City

1      John   j@j.com       1      21     Sydney

2      Mary   m@m.com       NULL   23     Los Angeles

3      Ron    ron@r.com     1      41     Miami

4      Sara   s@s.com       2      33     Buffalo

5      Linda  l@l.com       2      35     Lincoln

6      Kathy  k@k.com       2      42     Seattle

7      Rich   rich@r.com    NULL   52     Portland

8      Mike   mike@m.com    3      56     Austin

9      Sarah  s@r.com       1      60     San Francisco

10     Johnny john@r.com    NULL   38     Kansas City

11     Val    v@v.com       2      100    Portland

12     Glen   g@g.com       1      39     Honolulu

13     Donna  d@d.com       2      50     Tempe

14     David  david@d.com   1      49     Trenton

15     Tony   tony@t.com    1      22     Singapore

Insert into tblPerson Values (11, 'Val', 'v@v.com', 2, 100)

11     Val    v@v.com       2      100    Portland

--How to drop Check constraint--

Alter table tblPerson

Drop Constraint CK_tblPerson_Age

--How to ADD Constraint--

--How to ADD CHECK CONSTRAINT--

Alter table tblPerson

Add Constraint CK_tblPerson_Age CHECK (AGE > 0 AND AGE < 150)