Binds a rule to a column or to an alias data type.
This feature will be removed in a future version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. Use Unique Constraints and Check Constraints instead. CHECK constraints are created by using the CHECK keyword of the CREATE TABLE or ALTER TABLE statements.
Applies to: SQL Server (SQL Server 2008 through current version).
You can bind a new rule to a column (although using a CHECK constraint is preferred) or to an alias 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 SQL Server data type.
The rule is enforced when an INSERT statement is tried, not at binding. You can bind a character rule to a column of numeric data type, although such an INSERT operation is not valid.
Existing columns of the alias data type inherit the new rule unless futureonly_flag is specified as futureonly. New columns defined with the alias 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 an alias 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 by using sp_bindrule.
When you bind a rule to a column, related information is added to the sys.columns table. When you bind a rule to an alias data type, related information is added to the sys.types table.
A. Binding a rule to a column
Assuming that a rule named today has been created in the current database by using the CREATE RULE statement, the following example binds the rule to the HireDate column of the Employee table. When a row is added to Employee, the data for the HireDate column is checked against the today rule.
USE master; GO EXEC sp_bindrule 'today', 'HumanResources.Employee.HireDate';
B. Binding a rule to an alias data type
Assuming the existence of a rule named rule_ssn and an alias data type named ssn, the following 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; GO EXEC sp_bindrule 'rule_ssn', 'ssn';
C. Using the futureonly_flag
The following example binds the rule_ssn rule to the alias data type ssn. Because futureonly is specified, no existing columns of type ssn are affected.
USE master; GO EXEC sp_bindrule rule_ssn, 'ssn', 'futureonly';
D. Using delimited identifiers
The following example shows the use of delimited identifiers in object_name parameter.
USE master; GO CREATE TABLE [t.2] (c1 int) ; -- Notice the period as part of the table name. EXEC sp_bindrule 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.