For example, imagine that you have an inventory database that contains information about products available in a retail store. You might have a products table that contains item descriptions, product codes, and pricing information. Generally speaking, you would use the smallmoney data type for your pricing information. However, that data type allows values in the range -$214,748.3648 to $214,748.3647. If your store has a policy limiting prices to a range of $0.00 to $50.00, you can implement that business requirement using a CHECK constraint.
Creating a CHECK ConstraintBefore you can implement a CHECK constraint, you must be able to express it in a SQL statement. You can do this using any standard Transact-SQL conditions, including pattern matching wildcards. For example, you may write the pricing CHECK constraint described above as:
cost >= 0 and cost <=50
Applying a CHECK Constraint in SQL ServerOnce you've written the Transact-SQL statement that describes your CHECK constraint, you may implement it in SQL Server 2008 as follows:
- Open SQL Server Management Studio
- Navigate to the server containing the database you wish to modify and click the "+" icon to its left
- Expand the Databases folder by clicking the "+" icon to its left
- Expand the folder for the database containing the table where you wish to implement the constraint
- Expand the Tables folder
- Right-click on the table where you would like to implement the constraint and select Design from the menu
- Click the Table Designer menu at the top of the screen
- Choose Check Constraints from the drop-down list
- Click Add to build a new CHECK constraint
- Type the constraint definition you created (for example, "cost >= 0 and cost <=50") in the Expression textbox.
- Click the Close button
- Choose File -> Save to save the constraint to the database