SQL Server : Rules and Defaults

Rules :

A rule provides a mechanism for enforcing domain constraints for columns or user defined data types. The rule is applied before any modification is to be done on the table. In other words, a rule specifies the restriction on the values for a column or a user defined data type.
The Syntax of the CREATE RULE statement :

  CREATE RULE rule_name AS conditional_expression
  example:
  1.  CREATE RULE type_rule AS @typerule IN('business', 'mod_cook', 'trad_cook', 'popular_comp', 'psychology')
  2.  CREATE RULE dept_name_rule AS @deptname NOT IN ('accounts', 'stores')
  3.  CREATE RULE max_price_rule AS @maxprice >=5000

Iformation on a rule can be obtained using the sp_help system stored procedure. The text of a rule can be displayed using the sp_helptext system stored procedure with the name of the rule as its parameter.

Binding Rules :

A rule can be bound using the sp_bindrule system stored procedure.

Syntax :

 Sp_bindrule rule_name, object_name.ColName

For Example :
 Sp_bindrule type_rule, 'titles.type'

Binds the rule, type_rule, to the type column of the titles table

The restrictions on the use of the rules are as follows:

• Only one rule can be bound to a column or a user defined datatype.
• A rule cannot be bound to system datatypes.
• If a new rule is bound to a column or datatype that is already been inserted in the table, the existing values in the tables do not have to meet the criteria specified by the rule.
• A rule cannot be defined for a system-defined datatype

Unbinding Rules :

A rule can be unbound from a column or user-defined datatype using the sp_unbindrule system stored procedure.
The Syntax of the sp_unbindrule is :

 Sp_unbindrule object_name

 For Example :
  Sp_unbindingrule 'titles.type'

Continue Articles - SQL Server : Default and Rules : Default