Developers can create a simple constraint expression to check data for a simple condition; or they can create a complex expression, using Boolean operators, to check data for several conditions. For example, suppose the client table has a county code (ccode) column where a 3-alphabetic character string is required. This sample constraint expression guarantees that only 3-alphabetic characters are allowed:
ccode LIKE ‘[A-Z][A-Z][A-Z]’
Or suppose the cars table has a column called power which requires a value greater than 0. This sample constraint guarantees that only positive values are allowed:
power > 0
Developers can define a constraint expression, by following the next steps:
1. Create a new check constraint by following steps from the article “How to attach a new check constraint from GUI”.
2. In the Check Constraints dialog box, type an expression in the Check Constraint Expression dialog box using the following syntax:
{constant
column_name
function
(subquery)}
[{operator
AND
OR
NOT}
{constant
column_name
function
(subquery)}]
Where they can use 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 |
An arithmetic, bitwise, comparison, or string operator |
|
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. Developers 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. Developers 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. Developers can change the order of execution by using parentheses. |