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'
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