Chapter 1: Commands

create rule

Description

Specifies the domain of acceptable values for a particular column or for any column of a user-defined datatype, and creates access rules.

Syntax

create [ [ and | or ] access]] rule
	[owner.]rule_name 
	as condition_expression

Parameters

access: specifies that you are creating an access rule. For information on access rules, see Chapter 11, “Managing User Permissions” in the System Administration Guide.

rule_name: is the name of the new rule. It must conform to the rules for identifiers and cannot be a variable. Specify the owner’s name to create another rule of the same name owned by a different user in the current database. The default value for owner is the current user.

condition_expression

specifies the conditions that define the rule. It can be any expression that is valid in a where clause, and can include arithmetic operators, relational operators, in, like, between, and so on. However, it cannot reference a column or any other database object. Built-in functions that do not reference database objects can be included.

A condition_expression takes one argument. The argument is prefixed by the @ sign and refers to the value that is entered via the update or insert command. You can use any name or symbol to represent the value when you write the rule, but the first character must be the @ sign. Enclose character and date constants in quotes, and precede binary constants with “0x”.

Examples

Example 1

Creates a rule named limit, which limits the value of advance to less than $1000:

create rule limit 
as @advance < $1000

Example 2

Creates a rule named pubid_rule, which restricts the values of pub_id to 1389, 0736, or 0877:

create rule pubid_rule 
as @pub_id in ('1389', '0736', '0877')

Example 3

Creates a rule named picture, which restricts the value of value to always begin with the indicated characters:

create rule picture 
as @value like '_-%[0-9]'

Usage

To hide the text of a rule, use sp_hidetext.
To rename a rule, use sp_rename.

Restrictions

You can create a rule only in the current database.
Rules do not apply to the data that already exists in the database at the time the rules are created.
create rule statements cannot be combined with other statements in a single batch.
You cannot bind a rule to an Adaptive-Server-supplied datatype or to a column of type text, unitext, image, or timestamp.
You must drop a rule before you create a new one of the same name, and you must unbind a rule before you drop it. Use:
```
sp_unbindrule objname [, futureonly]
```

Binding rules

Use sp_bindrule to bind a rule to a column or user-defined datatype. Its syntax is:
```
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, Adaptive Server 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. Table 1-11 indicates the precedence when binding rules to columns and user-defined datatypes where rules already exist.

**Table 1-11: Rule binding precedence**
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 and NULLs

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.

Defaults and rules

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. Adaptive Server generates an error message each time it attempts to insert the default.

Using integrity constraints in place of rules

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.

Getting information about rules

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.

Standards

ANSI SQL – Compliance level: Entry-level compliant.

To create rules using ANSI SQL-compliant syntax, use the check clause of the create table statement.

Permissions

create rule permission defaults to the Database Owner, who can transfer it to other users.

Auditing

Values in event and extrainfo columns of sysaudits are:

Event	Audit option	Command or access audited	Information in extrainfo
13	create	create rule	Roles – Current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – NULL Proxy information – Original login name, if set proxy in effect