Attaching 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.

To attach a new check constraint

1. In your database diagram, right-click the table that will contain the constraint, then select 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 Constraints from the shortcut menu.

2. 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.
   
   
3. 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 ('). For additional details, see Defining a Check Constraint Expression.

4. If you want to give the constraint a different name, type the name in the Constraint name box.
   
   
5. 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. 
     
     

See Also

Check Constraints | Constraints | Deleting a Check Constraint | Disabling a Check Constraint for Replication