sp_bindrule System Stored Procedure

Binds a rule to a column or to a user-defined datatype.

Syntax

sp_bindrule rulename, objname [, futureonly]

where

rulename

Is the name of a rule created by the CREATE RULE statement.

objname

Specifies the table and column or the user-defined datatype to which the rule is to be bound. If objname is not of the form 'table.column', it is assumed to be a user-defined datatype. (Quotation marks are required around all system stored procedure parameters that have embedded blanks or punctuation.) By default, existing columns of the user-defined datatype inherit the rule rulename unless a rule has been bound directly to the column.

futureonly

Used only when binding a rule to a user-defined datatype, this option prevents existing columns of a user-defined datatype from inheriting the new rule. If futureonly is specified, any existing rule bound to that datatype will be explicitly bound to any existing columns of that datatype that do not already have a rule bound to them.

Remarks

First use the CREATE RULE statement to create a rule. Then execute the sp_bindrule system stored procedure to bind it to a column or to a user-defined datatype in the current database. You can bind a new rule to a column (although using a CHECK constraint is preferred) or to a user-defined datatype 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 - supplied datatype.

The rule is enforced when an INSERT is attempted, not at binding. You can bind a character rule to a column of numeric datatype, even though such an INSERT is illegal.

Existing columns of the user-defined datatype inherit the new rule unless they have a rule bound directly to them or the futureonly option is used. New columns of the user-defined datatype always inherit the rule.

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

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 startdate column of the employees table. When a row is added to employees, the data for the startdate column is checked against the today rule.

sp_bindrule today, 'employees.startdate'

B.    Bind a Rule to a User-defined Datatype

Assuming the existence of a rule named rule_ssn and a user-defined datatype 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 you specify the futureonly option (which prevents existing columns of type ssn from inheriting the rule) or ssn has a rule bound directly to it. Rules bound to columns always take precedence over those bound to datatypes.

sp_bindrule rule_ssn, ssn

C.    FUTUREONLY

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

sp_bindrule rule_ssn, ssn, FUTUREONLY

Permission

Execute permission defaults to the object owner.

Tables Used

syscolumns, sysobjects, systypes

See Also