CREATE RULE (T-SQL)

Creates an object called a rule. When bound to a column or a user-defined data type, a rule specifies the acceptable values that can be inserted into that column. Rules, a backward compatibility feature, perform some of the same functions as check constraints. CHECK constraints, created using the CHECK keyword of ALTER or CREATE TABLE, are the preferred, standard way to restrict the values in a column (multiple constraints can be defined on a column or multiple columns). A column or user-defined data type can have only one rule bound to it. However, a column can have both a rule and one or more check constraints associated with it. When this is true, all restrictions are evaluated.

Syntax

CREATE RULE rule
    AS condition_expression

Arguments

rule

Is the name of the new rule. Rule names must conform to the rules for identifiers. Specifying the rule owner name is optional.

condition_expression

Is the condition(s) defining the rule. A rule can be any expression that is valid in a WHERE clause and can include such elements as arithmetic operators, relational operators, and predicates (for example, IN, LIKE, BETWEEN). A rule cannot reference columns or other database objects. Built-in functions that do not reference database objects can be included.

condition_expression includes one variable. The at sign (@) precedes each local variable. The expression refers to the value that is entered with the UPDATE or INSERT statement. Any name or symbol can be used to represent the value when creating the rule, but the first character must be @.

Remarks

The CREATE RULE statement cannot be combined with other Transact-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 data types. A rule can be created only in the current database. After creating a rule, execute sp_bindrule to bind the rule to a column or to a user-defined data type.

The rule must be compatible with the data type of the column. A rule cannot be bound 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, “@value LIKE A%” cannot be used as a rule for a numeric column. If the rule is not compatible with the column to which it has been bound, Microsoft® SQL Server™ returns an error message when inserting a value (not when the rule is bound).

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

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

A rule must be dropped (using DROP RULE) before a new one with the same name is created, and the rule must be unbound (using sp_unbindrule) before it is dropped. Use sp_unbindrule to unbind a rule from a column.

You can bind a new rule to a column or data type 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 data types. Binding a rule to a column replaces a rule already bound to the user-defined data type of that column. But binding a rule to a data type does not replace a rule bound to a column of that user-defined data type. The table shows the precedence in effect when binding rules to columns and to user-defined data types 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.

Note Whether SQL Server interprets an empty string as a single space or as a true empty string is controlled by the setting of sp_dbcmptlevel. If the compatibility level is less than or equal to 65, SQL Server interprets empty strings as single spaces. If the compatibility level is equal to 70, SQL Server interprets empty strings as empty strings. For more information, see sp_dbcmptlevel.

Permissions

CREATE RULE permissions default to the members of the db_owner fixed database role, who can transfer permissions 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

AS

@value LIKE '_ _-%[0-9]'

See Also