How to: Define Check Constraint Expressions

When you attach a check constraint to a table or column, you must include a Transact-SQL expression. For details about this operation, see How to: Attach 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
NoteNote

Some databases have different functionality for check constraints. Consult your database documentation for details about how your database works with check constraints.

NoteNote

Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Customizing Development Settings in Visual Studio.

To define a constraint expression

  1. Create a new check constraint. For details on how to do this, see How to: Attach a New Check Constraint to a Table or Column.

  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)}]
    
    NoteNote

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

    The Transact-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. For details about functions, see the SQL Server documentation.

    operator

    An arithmetic, bitwise, comparison, or string operator. For details about operators, see the SQL Server documentation.

    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

Show: