Rule Objects

3 4

An alternative to using the CHECK constraint is to create a Rule object to restrict the values that can be inserted or updated in a column. The Rule object is similar to the Default object in that it is created apart from a table and is not deleted when the table is dropped. You must also bind a Rule object to a column or to a user-defined type—in this case, by using the sp_bindrule system stored procedure. And like the Default object, the Rule object is available with SQL Server 2000 for backward compatibility. Using the CHECK constraint is the preferred method of restricting column values, but Rule objects might be useful when the same rule is needed for many columns or user-defined types.

Creating a Rule Object by Using T-SQL

As an example, let's create a Rule object that performs the same function as the CHECK constraint we created earlier. Our rule uses the variable name @price to refer to the price column of the items table. The variable name must begin with an "at" symbol (@), but you can choose any name you want. First we'll create the rule, and then we'll bind it to a column, as shown here:

 USE MyDB GO CREATE RULE price_rule AS (@price >= .01 AND @price <= 500.00) GO sp_bindrule 'price_rule', 'items.price', 'futureonly' GO 

To unbind the rule and delete it, use the following statement:

 sp_unbindrule 'items.price' GO DROP RULE price_rule GO 

The parameters for sp_bindrule and sp_unbindrule are the same as for sp_bindefault and sp_unbindefault (described in the section "CREATE DEFAULT and sp_bindefault" earlier in this chapter). Each column or user-defined type can have only one rule, although you can assign both a rule and one or more CHECK constraints to the same column or user-defined type. If you do so, SQL Server will apply all of the restrictions to the inserted or updated data.

Creating a Rule Object by Using Enterprise Manager

To use Enterprise Manager to create and bind a Rule object, follow these steps:

1. Expand the server name and the database name in Enterprise Manager. Right-click Rules, and then choose New Rule from the shortcut menu to display the Rule Properties window. In this example, we will name the rule price_rule and will add text, as shown in Figure 16-24. Click OK to create the Rule object.
2. To bind the rule, click Rules in the left pane of Enterprise Manager, right-click the name of the new rule, and then choose Properties from the shortcut menu to display the Rule Properties window. As we did earlier when binding Default objects, click Bind UDTs to bind the rule to a user-defined data type, or click Bind Columns to bind the rule to a column or columns. For this example, we'll click Bind Columns and select the price column of the items table to bind the rule to, as shown in Figure 16-25.

   

   Figure 16-24. The Rule Properties window for creating a rule.

   

   Figure 16-25. Binding a rule to a column.

3. Click OK to apply your rule, and then click OK again to close the Rule Properties window.

To delete a rule, you must first unbind the rule from all columns or user-defined types. After you unbind the rule, right-click the rule name, choose Delete from the shortcut menu, and then click Drop All in the Drop Objects dialog box. If the rule is bound to anything when you try to delete it, SQL Server will display an error message and will not delete the rule.