Check Constraints

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

When you create a check constraint, source text, which describes the check constraint, is stored in the text column of the syscomments system table.

Warning! Do not remove this information from syscomments; doing so can cause problems for future upgrades of SAP ASE.

If you have security concerns, encrypt the text in syscomments by using sp_hidetext, described in the Reference Manual: Procedures.