check Constraints

A check constraint limits the values a user can insert into a column in a table.

  • A check constraint specifies a search_condition that any non-null 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 can contain wildcard characters

    An expression can include arithmetic operators and Transact-SQL built-in functions. The search_condition cannot contain subqueries, aggregate functions, or a host variable or parameter. The SAP ASE server does not enforce check constraints for temporary tables.

  • A column-level check constraint can reference only the column in which it is defined; it cannot reference other columns in the table. Table-level check constraints can reference any column in the table.

  • create table allows multiple check constraints in a column definition.

  • check integrity constraints offer an alternative to using rules and triggers. They are specific to the table in which they are created, and cannot be bound to columns in other tables or to user-defined datatypes.

  • check constraints do not override column definitions. 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, if you create a check constraint specifying “pub_id in (“1389”, “0736”, “0877”, “1622”, “1756”)” or “@amount > 10000” in a table column that allows null values, you can still insert NULL into that column. The column definition overrides the check constraint.