About check constraint expressions (ADP)

When you attach a check constraint to a table or column, you must include an SQL 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 page, 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: