Attaching a New Check Constraint to a Table or Column

See Also

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. For more information about the differences between databases, see Database Server Considerations. Consult your database documentation for details about how your database works with check constraints.

To attach a new check constraint

  1. In your database diagram, select the table where you want to add a check constraint.

  2. Right-click the table and select Properties.

  3. Choose the Tables tab.

  4. Under CHECK constraints for tables and columns, choose New. The Selected constraint box displays the system-assigned name of the new constraint. System-assigned names begin with "CK_" followed by the table name.

  5. In the Constraint expression box, 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]'
    

    Important   Make sure to enclose any non-numeric constraint values in single quotation marks ('). For additional details, see Defining a Check Constraint Expression.

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

When you create a check constraint, it is automatically bound to the specified columns. It takes effect when you save the table or diagram. When you delete a table, all the check constraints attached to that table are automatically deleted.