• Article

How to: Attach a New Check Constraint to a Table or Column (Visual Database Tools)

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

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 agent performs an insert or update on this table, check Enforce For Replication. For more information, see Controlling Constraints, Identities, and Triggers with NOT FOR REPLICATION.

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