Developers attach a check constraint to a table when they want to specify the data values that are acceptable in one or more columns.When Developers working with SQL Server “Denali”, MS SQL Server 2008 R2 and MS SQL Server 2005 want to attach a new check constraint, they should follow the next steps:
1. In their 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. Developers give the constraint a different name, by typing 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 clients table to California, type:
state = ‘CA’
Or, to require entries in the country code column to be 3 characters digits, type:
ccode LIKE ‘[A-Z][A-Z][A-Z]’
4. Expand the Table Designer category to set when the constraint is enforced:
– To test the constraint on data that existed before they 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.
– To enforce the constraint whenever a row of this table is inserted or updated, check Enforce for INSERTs and UPDATEs.