MS SQL Basics Tutorial: Database rules

Rule is the rules and restrictions in a database on the values stored in a table's columns or user-defined data types. A rule is a separate database object that is stored separately. The table or user-defined data types that the rule works with are independent of each other, i.e. the deletion and modification of a table or user-defined object does not affect the rules associated with it. Rules and constraints can be used at the same time, and table columns can have one rule and multiple check constraints. A rule is similar to a check constraint, whereas a check constraint in the altertable or create TABLE command is a more standard method of restricting column values, but a check constraint does not directly affect the user's custom data type.

9.2.1 Create rules

(1) Creating rules with the Create Rule command

The Create Rule command is used for creating rules in the current database with the following syntax:

CREATE Rule Rule_name as Condition_expression

Where the condition_expression clause is the definition of the rule. The condition_expression clause can be any expression that can be used in a WHERE condition clause, and it can contain arithmetic operators, relational operators, and predicates (such as in, like, BETWEEN, and so on).

Note: The expression in the condition_expression clause must begin with the character "@".

(2) Create rules with enterprise Manager

Selecting the Database Object "Rules" in Enterprise Manager and right-clicking the "newrule" option from the shortcut menu pops up the Create Rule Properties dialog box as shown in Figure 9-1. After you enter a rule name and an expression, click OK to complete the creation of the rule.

9.2.2 View Rules

(1) Viewing rules with Enterprise Manager