CREATE RULE Statement

Creates an object called a rule, which, when bound to a column or a user-defined datatype, specifies the acceptable values that can be inserted into that column. An alternative method to creating rules is to create table- and column-level CHECK constraints as described with the CREATE TABLE. Using constraints is the preferred method of restricting column data because multiple constraints can be defined on a column or multiple columns. A column or user-defined datatype can have only one rule bound to it. However, a column can have a rule and one or more CHECK constraints associated with it. When this is true, all restrictions are evaluated.

Syntax

CREATE RULE [owner.]rule_name
AS condition_expression

where

rule_name

Is the name of the new rule. Rule names must conform to the rules for identifiers.

AS condition_expression

Specifies the conditions that define the rule. A rule can be any expression that is valid in a WHERE clause, and it can include such elements as arithmetic operators, relational operators, IN, LIKE, BETWEEN, and so on. It cannot reference columns or other database objects. Built-in functions that do not reference database objects can be included.

A condition_expression includes one variable. The at symbol (@) precedes each local variable. The expression refers to the value that is entered with the UPDATE or INSERT statement. You can use any name or symbol to represent the value when you write the rule, but the first character must be @.

Remarks

CREATE RULE statements cannot be combined with other SQL statements in a single batch. Rules do not apply to data already existing in the database at the time the rules are created, and rules cannot be bound to system datatypes. You can create a rule only in the current database. After creating a rule, execute the sp_bindrule System Stored Procedure to bind the rule to a column or to a user-defined datatype.

The rule must be compatible with the datatype of the column. You cannot bind a rule to a text, image, or timestamp column. Be sure to enclose character and date constants with single quotation marks (') and to precede binary constants with 0x. For example, you cannot use "@value like A%" as a rule for a numeric column. If the rule is not compatible with the column to which you've bound it, SQL Server returns an error message when you try to insert a value (not when you bind the rule).

A rule bound to a user-defined datatype is activated only when you attempt to insert a value into or to update a database column of the user-defined datatype. Because rules do not test variables, do not assign a value to a user-defined datatype variable that would be rejected by a rule bound to a column of the same datatype.

To get a report on a rule, use the sp_help System Stored Procedure. To display the text of a rule, execute the sp_helptext System Stored Procedure with the rule name as the parameter. To rename a rule, use the sp_rename System Stored Procedure.

You must drop a rule (using the DROP RULE) before you can create a new one with the same name, and you must unbind a rule (using the sp_unbindrule System Stored Procedure) before you drop it. Use the sp_unbindrule System Stored Procedure to unbind a rule from a column.

You can bind a new rule to a column or datatype without unbinding the previous one; the new rule overrides the previous one. Rules bound to columns always take precedence over rules bound to user-defined datatypes. Binding a rule to a column replaces a rule already bound to the user-defined datatype of that column. But binding a rule to a datatype does not replace a rule bound to a column of that user-defined datatype. The following table shows the precedence in effect when binding rules to columns and to user-defined datatypes where rules already exist:

If a column has both a default and a rule associated with it, the default must fall within the domain defined by the rule. A default that conflicts with a rule is never inserted. SQL Server generates an error message each time it attempts to insert such a default.

Permission

CREATE RULE permission defaults to the database owner, who can transfer it to other users.

Examples

A.    Rule with a Range

This example creates a rule that restricts the range of integers inserted into the column(s) to which this rule is bound.

CREATE RULE range_rule
AS 
@range >= $1000 AND @range < $20000

B.    Rule with a List

This example creates a rule that restricts the actual values entered into the column(s) to which this rule is bound to only those listed in the rule.

CREATE RULE list_rule
AS 
@list IN ('1389', '0736', '0877')

C.    Rule with a Pattern

This example creates a rule to follow a pattern of any two characters followed by a hyphen, any number of characters (or no characters), and ending with an integer between 0 and 9.

CREATE RULE pattern_rule 
@value LIKE '_ _-%[0-9]'

See Also