Binds a rule to a column or to a user-defined data type.
sp_bindrule [ @rulename = ] 'rule' ,
[ @objname = ] 'object_name'
[ , [ @futureonly = ] 'futureonly_flag' ]
[@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)
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.
Only members of the sysadmin fixed server role, the db_owner and db_ddladmin fixed database roles, and the table owner can execute sp_bindrule.
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.