Share via


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

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

Note

The dialog boxes and menu commands you see might differ from those described in Help depending on your active settings or edition. To change your settings, choose Import and Export Settings on the Tools menu. For more information, see Visual Studio Settings.

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

    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]'
    

    Note

    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.

See Also

Reference

Indexes/Keys Dialog Box

Other Resources

Working with Constraints

Working with Keys