MS SQL Column Check Constraints

0
22

A SQL2 check constraint is a search condition, like the search condition in a WHERE clause, that produces a true/false value. When a check constraint is specified for a column, the DBMS automatically checks the value of that column each time a new row is inserted or a row is updated to insure that the search condition is true. If not, the INSERT or UPDATE statement fails. A column check constraint is specified as part of the column definition within the CREATE TABLE statement. Consider this excerpt from a CREATE TABLE statement, modified from the definition of the demo database to include three check constraints:

CREATE TABLE SALESREPS (

EMPL_NUM INTEGER NOT NULL CHECK (EMPL_NUM BETWEEN 101 AND 199),

AGE INTEGER CHECK (AGE >= 21),

QUOTA MONEY CHECK (MONEY >= 0.0)

)

The first constraint (on the EMPL_NUM column) requires that valid employee numbers be three-digit numbers between 101 and 199. The second constraint (on the AGE column) similarly prevents hiring of minors. The third constraint (on the QUOTA column) prevents a salesperson from having a quota target less than $0.00. All three of these column check constraints are very simple examples of the capability specified by the SQL2 standard. In general, the parentheses following the keyword CHECK can contain any valid search condition that makes sense in the context of a column definition. With this flexibility, a check constraint can compare values from two different columns of the table, or even compare a proposed data value against other values from the database.

LEAVE A REPLY

Please enter your comment!
Please enter your name here