Binding Rules

Use sp_bindrule to bind a rule to a column or user-defined datatype.

  • sp_bindrule rulename, objname [, futureonly]
  • A rule that is bound to a user-defined datatype is activated when you insert a value into, or update, a column of that type. Rules do not test values inserted into variables of that type.

  • The rule must be compatible with the datatype of the column. For example, you cannot use the following as a rule for an exact or approximate numeric column:
    @value like A%

    If the rule is not compatible with the column to which it is bound, SAP ASE generates an error message when it tries to insert a value, not when you bind it.

  • You can bind a rule to a column or datatype without unbinding an existing rule.

  • Rules bound to columns always take precedence over rules bound to user-defined datatypes, regardless of which rule was most recently bound. This table indicates the precedence when binding rules to columns and user-defined datatypes where rules already exist:

    New Rule Bound to

    Old Rule Bound to User-Defined Datatype

    Old Rule Bound to Column

    User-defined datatype

    New rule replaces old

    No change

    Column

    New rule replaces old

    New rule replaces old

  • Rules do not override column definitions. If a rule is bound to a column that allows null values, you can insert NULL into the column, implicitly or explicitly, even though NULL is not included in the text of the rule. For example, if you create a rule specifying “@val in (1,2,3)” or “@amount > 10000”, and bind this rule to a table column that allows null values, you can still insert NULL into that column. The column definition overrides the rule.

  • 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. The SAP ASE server generates an error message each time it attempts to insert the default.

  • You can define rules using check with the create table statement, which creates integrity constraints. However, these constraints are specific for that table; you cannot bind them to other tables. See create table and alter table for information about integrity constraints.

  • To get a report on a rule, use sp_help.

  • To display the text of a rule, which is stored in the syscomments system table, execute sp_helptext with the rule name as the parameter.

  • After a rule is bound to a particular column or user-defined datatype, its ID is stored in the syscolumns or systypes system tables.