You can declare a check constraint to limit the values users insert into a column in a table. Check constraints are useful for applications that check a limited, specific range of values. A check constraint specifies a search_condition that any value must pass before it is inserted into the table. A search_condition can include:
A list of constant expressions introduced with in
A range of constant expressions introduced with between
A set of conditions introduced with like, which may contain wildcard characters
An expression can include arithmetic operations and Transact-SQL built-in functions. The search_condition cannot contain subqueries, a set function specification, or a target specification.
For example, this statement ensures that only certain values can be entered for the pub_id column:
create table my_new_publishers
(pub_id char(4)
check (pub_id in ("1389", "0736", "0877",
"1622", "1756")
or pub_id like "99[0-9][0-9]"),
pub_name varchar(40),
city varchar(20),
state char(2))
Column-level check constraints can reference only the column on which the constraint is defined; they cannot reference other columns in the table. Table-level check constraints can reference any columns in the table. create table allows multiple check constraints in a column definition.
Because check constraints do not override column definitions, you cannot use a check constraint to prohibit null values if the column definition permits them. If you declare a check constraint on a column that allows null values, you can insert NULL into the column, implicitly or explicitly, even though NULL is not included in the search_condition. For example, suppose you define the following check constraint on a table column that allows null values:
check (pub_id in ("1389", "0736", "0877", "1622", "1756"))
You can still insert NULL into that column. The column definition overrides the check constraint because the following expression always evaluates to true:
col_name != null
