How to define check constraint expressions from GUI in SQL server

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.