Defining a Check Constraint Expression

When you attach a check constraint to a table or column, you must include an SQL expression. For details about this operation, see Attaching a New Check Constraint to a Table or Column.

You can create a simple constraint expression to check data for a simple condition; or you can create a complex expression, using Boolean operators, to check data for several conditions. For example, suppose the authors table has a zip column where a 5-digit character string is required. This sample constraint expression guarantees that only 5-digit numbers are allowed:

zip LIKE '[0-9][0-9][0-9][0-9][0-9]'

Or suppose the sales table has a column called qty which requires a value greater than 0. This sample constraint guarantees that only positive values are allowed:

qty > 0

Or suppose the orders table limits the type of credit cards accepted for all credit card orders. This sample constraint guarantees that if the order is placed on a credit card, then only Visa, MasterCard, or American Express is accepted:

NOT (payment_method = ‘credit card’) OR
   (card_type IN (‘VISA’, ‘MASTERCARD’, ‘AMERICAN EXPRESS’))

Note   Some databases have different functionality for check constraints. For more information about the differences between databases, see Database Server Considerations. Consult your database documentation for details about how your database works with check constraints.

To define a constraint expression

• In the Tables tab of the property pages, type the expression in the Constraint expression box using the following syntax:

  {constant | column_name | function | (subquery)}
  [{operator | AND | OR | NOT}
  {constant | column_name | function | (subquery)}…]

  Note   This syntax is Microsoft® SQL Server specific. Consult your database documentation for details.

The SQL syntax is made up of the following parameters: