MSDN Library

How to: Attach a New Check Constraint to a Table or Column

Attach a check constraint to a table to specify the data values that are acceptable in one or more columns.

Note Note

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 attach a new check constraint

  1. In your database diagram, right-click the table that will contain the constraint, then select Check Constraints from the shortcut menu.

    -or-

    Open the Table Designer for the table that will contain the constraint, right-click in the Table Designer, and choose Check Constraints from the shortcut menu.

  2. Click Add.

    Note Note

    If you want to give the constraint a different name, type the name in the Constraint name box.

  3. In the grid, in the Expression field, type the SQL expressions for the check constraint. For example, to limit the entries in the state column of the authors table to New York, type:

    state = 'NY'
    

    Or, to require entries in the zip column to be 5 digits, type:

    zip LIKE '[0-9][0-9][0-9][0-9][0-9]'
    
    NoteNote

    Make sure to enclose any non-numeric constraint values in single quotation marks (').

  4. Expand the Table Designer category to set when the constraint is enforced:

    • To test the constraint on data that existed before you created the constraint, check Check Existing Data on Creation or Enabling.

    • To enforce the constraint whenever a replication operation occurs on this table, check Enforce For Replication.

    • To enforce the constraint whenever a row of this table is inserted or updated, check Enforce for INSERTs and UPDATEs.

Community Additions

Show:
© 2016 Microsoft