Check constraints

Last modified: October 1, 2024

Check constraints enforce domain integrity by limiting the values that are accepted by one or more columns. The search condition must evaluate to a Boolean expression and cannot contain subqueries. A column-level check constraint can reference only the constrained column, and a table-level check constraint can reference only columns of the constrained table.

Multiple check constraints can be defined for a table. SQL Server can define only one column-level check constraint per column per the CREATE TABLE statement (although each column constraint can have multiple conditions).

To create a check constraint

Open the table you need, switch to Constraints tab, and select New Check Constraint in the shortcut menu.

To edit a check constraint

Navigate to the constraint in Database Explorer and double-click it or open the table that owns the constraint, switch to the Constraints tab and choose Edit Constraint from shortcut menu.

To drop a constraint

Navigate to the constraint in Database Explorer and choose Delete from shortcut menu or open the table that owns the constraint, switch to the Constraints tab and choose Delete Constraint from its shortcut menu.

Example

Consider you need to ensure that all employees in the Emp table have and will have salary that is more than 500. This can be implemented best with check constraint. Open the table and create the constraint as described above. In field Condition type ‘Sal>500’ and save the document. At this moment all data in the table is checked for validity and, if it satisfies the condition, new constraint is applied to the table.

This example assumes you do not change some of the constraint options which are located at the bottom of constraint editor. These options add some flexibility to constraints both in the moments of creation and altering. For example, you can:

• Clear the Enabled option to prevent constraint from activating. You can enable it later.
• Clear the Validate option to avoid verification of data that table already contains. Newly added or modified data still will be checked for validity.
• Check Deferrable option to preserve opportunity of deferring check operation until after a transaction is committed.