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. See “Managing User Permissions” in the System Administration Guide.

rule_name

is the name of the new rule, which 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, condition_expression 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, which must be 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. 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


Restrictions


Binding rules

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

The permission checks for create rule differ based on your granular permissions settings.

Granular permissions enabled

With granular permissions enabled, you must have the create rule privilege. You must have create any rule privilege to use create rule for other users.

Granular permissions disabled

With granular permissions disabled, you must have the create rule privilege, be the database owner, or a user with sa_role.

You must be a user with sa_role to use create rule for 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 is in effect

See also

Commands alter table, create default, create table, drop rule, drop table

System procedures sp_bindrule, sp_help, sp_helptext, sp_hidetext, sp_rename, sp_unbindrule