Professional SQL Server™ 2005 Programming

5.7. Rules and Defaults: Cousins of Constraints

Rules and defaults have been around much longer than CHECK and DEFAULT constraints have been. They are something of an old SQL Server standby and are definitely not without their advantages.

That being said, I'm going to digress from explaining them long enough to recommend that you look them over for backward compatibility and legacy code familiarity only. Rules and defaults aren't ANSI compliant, which creates portability issues, and they don't perform as well as constraints do.

The primary thing that sets rules and defaults apart from constraints is in their very nature; constraints are features of a table. Rules and defaults are actual objects. Whereas a constraint is defined in the table definition, rules and defaults are defined independently and are then "bound" to the table after the fact.

5.7.1. Rules

A rule is incredibly similar to a CHECK constraint. The only difference beyond those I've already described is that rules are limited to working with just one column at a time. You can bind the same rule separately to multiple columns in a table, but the rule will work independently with each column, and won't be aware of the other columns at all. A constraint defined as (QtyShipped <= QtyOrdered) would not work for a rule. It refers to more than one column. In contrast, LIKE ([0-9][0-9][0-9]) applies only to the column to which the rule is bound.

Try defining a rule so that you can see the differences first hand:

CREATE RULE ...