sp_bindrule (T-SQL)

Binds a rule to a column or to a user-defined data type.

Syntax

sp_bindrule [@rulename =] 'rule',
    [@objname =] 'object_name'
    [, [@futureonly =] 'futureonly_flag']

Arguments

[@rulename =] 'rule'

Is the name of a rule created by the CREATE RULE statement. rule is nvarchar(776), with no default.

[@objname =] 'object_name'

Is the table and column, or the user-defined data type to which the rule is to be bound. object_name is nvarchar(517), with no default. If object_name is not of the form table.column, it is assumed to be a user-defined data type. By default, existing columns of the user-defined data type inherit rule unless a rule has been bound directly to the column.

---

Note object_name can contain the [ and ] characters as delimited identifier characters. For more information, see Delimited Identifiers.

---

[@futureonly =] 'futureonly_flag'

Is used only when binding a rule to a user-defined data type. future_only_flag is varchar(15), with a default of NULL. This parameter when set to futureonly prevents existing columns of a user-defined data type from inheriting the new rule. If futureonly_flag is NULL, the new rule is bound to any columns of the user-defined data type that currently have no rule or that are using the existing rule of the user-defined data type.

Return Code Values

0 (success) or 1 (failure)

Result Sets

None

Remarks

You can bind a new rule to a column (although using a CHECK constraint is preferred) or to a user-defined data type with sp_bindrule without unbinding an existing rule. The old rule is overridden. If a rule is bound to a column with an existing CHECK constraint, all restrictions are evaluated. You cannot bind a rule to a Microsoft® SQL Server™ data type.

The rule is enforced when an INSERT statement is attempted, not at binding. You can bind a character rule to a column of numeric data type, although such an INSERT is illegal.

Existing columns of the user-defined data type inherit the new rule unless futureonly_flag is specified as futureonly. New columns defined with the user-defined data type always inherit the rule. However, if the ALTER COLUMN clause of an ALTER TABLE statement changes the data type of a column to a user-defined data type bound to a rule, the rule bound to the data type is not inherited by the column. The rule must be specifically bound to the column using sp_bindrule.

When you bind a rule to a column, related information is added to the syscolumns table. When you bind a rule to a user-defined data type, related information is added to the systypes table.

Permissions

Execute permissions default to the object owner.

Examples

A. Bind a rule to a column

Assuming that a rule named today has been created in the current database by the CREATE RULE statement, this example binds the rule to the hire date column of the employees table. When a row is added to employees, the data for the hire date column is checked against the today rule.

USE master

EXEC sp_bindrule 'today', 'employees.[hire date]'

B. Bind a rule to a user-defined data type

Assuming the existence of a rule named rule_ssn and a user-defined data type named ssn, this example binds rule_ssn to ssn. In a CREATE TABLE statement, columns of type ssn inherit the rule_ssn rule. Existing columns of type ssn also inherit the rule_ssn rule unless futureonly is specified for futureonly_flag, or ssn has a rule bound directly to it. Rules bound to columns always take precedence over those bound to data types.

USE master

EXEC sp_bindrule 'rule_ssn', 'ssn'

C. Use the futureonly_flag

This example binds the rule_ssn rule to the user-defined data type ssn. Because futureonly is specified, no existing columns of type ssn are affected.

USE master

EXEC sp_bindrule 'rule_ssn', 'ssn', 'futureonly'

D. Use delimited identifiers

This example shows the use of delimited identifiers in object_name.

USE master

CREATE TABLE [t.2] (c1 int)

-- Notice the period as part of the table name.

EXEC sp_binderule rule1, '[t.2].c1'

-- The object contains two periods;

-- the first is part of the table name

-- and the second distinguishes the table name from the column name.

See Also