Rules

Rules are used to limit the range of values that can be stored in a column. They are similar to CHECK constraints in this regard, but they are more limited in their functionality. A rule is considered a bound object; that is, the rule is first created, and then is bound to a column or User-Defined Datatype.

Rule Usage

A rule, once created, can be bound to multiple columns or User-Defined Datatypes (UDTs). A rule can be useful to maintain consistency not just in a column, but in all columns and UDTs across the database to which it is bound. For example, if you require a standard format for telephone numbers , you could create the following rule:

 CREATE RULE phone_rule AS  @phone LIKE '([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]' 

By binding this rule to phone number columns in your database, you can ensure a consistent phone number format. This could be taken a step further by creating a UDT called Phone and binding the rule to the UDT. When creating a column for storing telephone numbers, use the Phone datatype, and the rule automatically applies.

Creating and Managing Rules

Rules are created using the CREATE RULE command. Rule definitions can contain anything that is valid in a WHERE clause. Managing rules involves binding them to columns and UDTs using sp_bindrule and unbinding them using sp_unbindrule . The following are some examples of creating and managing rules.

First, the rule must be created:

 --Create a rule to limit product color codes  CREATE RULE ccode_rule AS @ccode IN ('r01', 'r02', 'r03') 

After the rule is created, it can be bound to a column or UDT:

 --Bind a rule to a column  sp_bindrule ccode_rule, 'product.ccode' --Bind a rule to a UDT named color sp_bindrule ccode_rule, color 

To unbind a rule, use sp_unbindrule :

 --Unbind a rule from a UDT  sp_unbindrule color 

Creating Rules in Enterprise Manager

If you prefer to use Enterprise Manager to create a bound rule, you simply right-click Rules in the appropriate database, then select New Rule from the pop-up menu. This opens the Rule Properties dialog box, as shown in Figure 14.5. Enter a name for the rule, as well as the rule definition in the text box, and click OK. After a rule is created, this same dialog box can be used to bind the rule to UDTs and columns.

Figure 14.5. The Rule Properties dialog box.

Rule Limitations

Although rules have an advantage over CHECK constraints in that they can be bound to many columns or to UDTs to provide centralized rule handling, they also have limitations.

• A column or UDT can have only one rule bound to it.

• A rule cannot validate against other columns in the row.

• Rules are not ANSI-92 compliant.

The status of rules for future versions of SQL Server is also a concern. Microsoft refers to rules in the documentation as a "backward compatibility" feature. I read this to mean that rules won't be supported in the future, and could be dropped from future releases. The inference is that you should now use CHECK constraints where you might have used rules in the past.