Export (0) Print
Expand All
Expand Minimize

Check Constraint Expression Dialog Box (Visual Database Tools)

New: 5 December 2005

When you attach a check constraint to a table or column, you must include an SQL expression. Type the check constraint expression in the box provided.

Expression

Enter the expression

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'))

In the Check Constraints tab of the property pages, type an expression in the Constraint expression box using the following syntax:

{constant | column_name | function | (subquery)}

[{operator | AND | OR | NOT}

{constant | column_name | function | (subquery)}...]

The SQL syntax is made up of the following parameters:

Parameter Description

constant

A literal value, such as numeric or character data. Character data must be enclosed within single quotation marks (').

column_name

Specifies a column.

function

A built-in function.

operator

Arithmetic, bitwise, comparison, or string operators.

AND

Use in Boolean expressions to connect two expressions. Results are returned when both expressions are true.

When AND and OR are both used in a statement, AND is processed first. You can change the order of execution by using parentheses.

OR

Use in Boolean expressions to connect two or more conditions. Results are returned when either condition is true.

When AND and OR are both used in a statement, OR is evaluated after AND. You can change the order of execution by using parentheses.

NOT

Negates any Boolean expression (which can include keywords, such as LIKE, NULL, BETWEEN, IN, and EXISTS).

When more than one logical operator is used in a statement, NOT is processed first. You can change the order of execution by using parentheses.

Community Additions

ADD
Show:
© 2014 Microsoft