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.

1. In the Database window, click Database Diagrams   under Objects, click the database diagram you want to open, and then click Design on the Database window toolbar.
2. In your database diagram, right-click the table that will contain the constraint, then select Constraints from the shortcut menu.
3. 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.
4. 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]'

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

5. If you want to give the constraint a different name, type the name in the Constraint name box.
6. Use the check boxes to control when the constraint is enforced:
   • To test the constraint on existing data before creating the constraint, check Check existing data on creation.
   • To enforce the constraint whenever a replication operation occurs on this table, check Enforce constraint for replication.
   • To enforce the constraint whenever a row of this table is inserted or updated, check Enforce constraint for INSERTs and UPDATEs.