Chapter 13 Using Batches and Control-of-Flow
Language

Transact-SQL User's Guide

Chapter 12 Defining Defaults and Rules for Data

Chapter 12

Defining Defaults and Rules for Data

A default is a value that Adaptive Server inserts into a column if the user does not explicitly enter a value for that column. In database management, a rule specifies what you are or are not allowed to enter in a particular column or in any column with a given user-defined datatype. You can use defaults and rules to help maintain the integrity of data across the database.

How defaults and rules work

You can define a value for a table column or user-defined datatype that will be automatically inserted if a user does not explicitly enter a value. For example, you can create a default that has the value "???" or the value "fill in later." You can also define rules for that table column or datatype to restrict the types of values users can enter for it.

In a relational database management system, every data element must contain some value, even if that value is null. As discussed in Chapter 7, "Creating Databases and Tables," some columns do not accept the null value. For those columns, some other value must be entered, either a value explicitly entered by the user or a default entered by Adaptive Server.

Rules enforce the integrity of data in ways not covered by a column's datatype. A rule can be connected to a specific column, to several specific columns or to a specified, user-defined datatype.

Every time a user enters a value, Adaptive Server checks it against the most recent rule that has been bound to the specified column. Data entered prior to the creation and binding of a rule is not checked.

As an alternative to using defaults and rules, you can use the default clause and the check integrity constraint of the create table statement to accomplish some of the same tasks. However, they are specific for that table and cannot be bound to columns of other tables or to user-defined datatypes. For more information about integrity constraints, see Chapter 7, "Creating Databases and Tables."

Creating defaults

You can create or drop defaults at any time, before or after data has been entered in a table. In general, to create defaults you:

Define the default, using create default.
Bind the default to the appropriate table column or user-defined datatype using sp_bindefault.
Test the bound default by inserting data.

You can drop defaults using drop default and remove their association using sp_unbinddefault.

When you create and bind defaults:

Make sure the column is large enough for the default. For example, a char (2) column will not hold a 17-byte string like "Nobody knows yet."
Be careful when you put a default on a user-defined datatype and a different default on an individual column of that type. If you bind the datatype default first and then the column default, the column default replaces the user-defined datatype default for the named column only. The user-defined datatype default is bound to all the other columns having that datatype.
However, once you bind another default to a column that has a default because of its type, that column ceases to be influenced by defaults bound to its datatype. This issue is discussed in more detail under "Binding defaults".
Watch for conflicts between defaults and rules. Be sure the default value is allowed by the rule; otherwise, the default may be eliminated by the rule.
For example, if a rule allows entries between 1 and 100, and the default is set to 0, the rule rejects the default entry. Either change the default or change the rule.

create default syntax

The syntax of create default is:

create default [owner.]default_name 
     as constant_expression

Default names must follow the rules for identifiers. You can create a default in the current database only.

Within a database, default names must be unique for each user. For example, you cannot create two defaults called phonedflt. However, as "guest," you can create a phonedflt even if dbo.phonedflt already exists because the owner names make each one distinct.

Another example: suppose you want to create a default value of "Oakland" that can be used with the city column of friends_etc and possibly with other columns or user datatypes. To create the default, enter:

create default citydflt 
as "Oakland"

As you continue to follow this example, you can use any city name that works for the people you are going to enter in your personal table.

Enclose character and date constants in quotes; money, integer, and floating point constants do not require them. Binary data must be preceded by "0x", and money data should be preceded by a dollar sign ($). The default value must be compatible with the datatype of the column. You cannot use "none," for example, as a default for a numeric column, but 0 is appropriate.

If you specify NOT NULL when you create a column and do not associate a default with it, Adaptive Server produces an error message whenever anyone fails to make an entry in that column.

Usually, you will enter default values when you create a table. However, during a session in which you want to enter many rows having the same values in one or more columns, it may be convenient to create a default tailored to that session before you begin.

You cannot issue create table with a declarative default and then insert data into the table in the same batch or procedure. Either separate the create and insert statements into two different batches or procedures, or use execute to perform the actions separately.

Binding defaults

After you have created a default, use sp_bindefault to bind the default to a column or user-defined datatype. For example, suppose you create the following default:

create default advancedflt as "UNKNOWN"

Now, bind the default to the appropriate column or user-defined datatype with sp_bindefault.

sp_bindefault advancedflt, "titles.advance"

The default takes effect only if the user does not add an entry to the advance column of the titles table. Not making an entry is different than entering a null value. A default can connect to a particular column, to a number of columns, or to all columns in the database that have a given user-defined datatype.

To get the default, you must issue an insert or update command with a column list that does not include the column that has the default.

The following restrictions apply to defaults:

The default applies to new rows only. It does not retroactively change existing rows. Defaults take effect only when no entry is made. If you supply any value for the column, including NULL, the default has no effect.
You cannot bind a default to a system datatype.
You cannot bind a default to a timestamp column, because Adaptive Server generates values for timestamp columns.
You cannot bind defaults to system tables.
You can bind a default to an IDENTITY column or to a user-defined datatype with the IDENTITY property, but Adaptive Server ignores such defaults. When you insert a row into a table without specifying a value for the IDENTITY column, Adaptive Server assigns a value that is 1 greater than the last value assigned.
If a default already exists on a column, you must remove it before binding a new default. Use sp_unbindefault to remove defaults created with sp_bindefault. Use alter table to remove defaults created with create table.

To bind citydflt to the city column in friends_etc, type:

sp_bindefault citydflt, "friends_etc.city"

Notice that the table and column name are enclosed in quotes, because of the embedded punctuation (the period).

If you create a special datatype for all city columns in every table in your database, and bind citydflt to that datatype, "Oakland" appears only where city names are appropriate. For example, if the user datatype is called citytype, here is how to bind citydflt to it:

sp_bindefault citydflt, citytype

To prevent existing columns or a specific user datatype from inheriting the new default, use the futureonly parameter when binding a default to a user datatype. However, do not use futureonly when binding a default to a column. Here is how you create and bind the new default "Berkeley" to the datatype citytype for use by new table columns only:

create default newcitydflt as "Berkeley"

sp_bindefault newcitydflt, citytype, futureonly

"Oakland" continues to appear as the default for any existing table columns using citytype.

If most of the people in your table live in the same zip code area, you can create a default to save data entry time. Here is one, along with its binding, that is appropriate for a section of Oakland:

create default zipdflt as "94609"

sp_bindefault zipdflt, "friends_etc.postalcode"

Here is the complete syntax for sp_bindefault:

sp_bindefault defname, objname [, futureonly]

defname is the name of the default created with create default. objname is the name of the table and column, or of the user-defined datatype, to which the default is to be bound. If the parameter is not of the form table.column, it is assumed to be a user-defined datatype.

All columns of a specified user-defined datatype become associated with the specified default unless you use the optional futureonly parameter, which prevents existing columns of that user datatype from inheriting the default.

Defaults cannot be bound to columns and used during the same batch. sp_bindefault cannot be in the same batch as insert statements that invoke the default.

Unbinding defaults

Unbinding a default means disconnecting it from a particular column or user-defined datatype. An unbound default is still stored in the database and is available for future use. Use sp_unbindefault to remove the binding between a default and a column or datatype.

Here is how you unbind the current default from the city column of the friends_etc table:

execute sp_unbindefault "friends_etc.city"

To unbind a default from the user-defined datatype citytype, use:

sp_unbindefault citytype

The complete syntax of sp_unbindefault is:

sp_unbindefault objname [, futureonly]

If the objname parameter you give is not of the form table.column, Adaptive Server assumes it is a user-defined datatype. When you unbind a default from a user-defined datatype, the default is unbound from all columns of that type unless you give the optional futureonly parameter, which prevents existing columns of that datatype from losing their binding with the default.

How defaults affect NULL values

If you specify NOT NULL when you create a column and do not create a default for it, Adaptive Server produces an error message whenever anyone inserts a row and fails to make an entry in that column.

When you drop a default for a NULL column, Adaptive Server inserts NULL in that position each time you add rows without entering any value for that column. When you drop a default for a NOT NULL column, you get an error message when rows are added, without a value entered for that column.

Table 12-1 illustrates the relationship between the existence of a default and the definition of a column as NULL or NOT NULL.

Column definition and null defaults
Column definition	User entry	Result
Null and default defined	No value NULL value	Default used NULL used
Null defined, no default defined	No value NULL value	NULL used NULL used
Not null, default defined	No value NULL value	Default used NULL used
Not null, no default defined	No value NULL value	Error Error

After creating a default

After you create a default, the source text describing the default is stored in the text column of the syscomments system table. Do not remove this information; doing so may cause problems for future versions of Adaptive Server. Instead, encrypt the text in syscomments by using sp_hidetext, described in the Reference Manual. For more information, see "Compiled objects".

Dropping defaults

To remove a default from the database entirely, use the drop default command. Unbind the default from all columns and user datatypes before you drop it. (See "Unbinding defaults".) If you try to drop a default that is still bound, Adaptive Server displays an error message and the drop default command fails.

Here is how to remove citydflt. First, you unbind it:

sp_unbindefault citydft

Then you can drop citydft:

drop default citydflt

The complete syntax of drop default is:

drop default [owner.]default_name 
  [, [owner.]default_name] ...

A default can be dropped only by its owner. For more information about unbinding a default see sp_unbindefault and alter table in the Reference Manual.

Creating rules

A rule lets you specify what users can or cannot enter into a particular column or any column with a user-defined datatype. In general, to create a rule you:

Create the rule using create rule.
Bind the rule to a column or user-defined datatype using sp_bindrule.
Test the bound rule by inserting data. Many errors in creating and binding rules can be caught only by testing with an insert or update command.

You can unbind a rule from the column or datatype either by using sp_unbindrule or by binding a new rule to the column or datatype.

create rule syntax

The syntax of create rule is:

create rule [owner.]rule_name 
     as condition_expression

Rule names must follow the rules for identifiers. You can create a rule in the current database only.

Within a database, rule names must be unique for each user. For example, a user cannot create two rules called socsecrule. However, two different users can create a rule named socsecrule, because the owner names make each one distinct.

Here is how the rule permitting five different pub_id numbers and one dummy value (99 followed by any two digits) was created:

create rule pub_idrule 
as @pub_id in ("1389", "0736", "0877", "1622", "1756")
or @pub_id like "99[0-9][0-9]"

The as clause contains the name of the rule's argument, prefixed with "@", and the definition of the rule itself. The argument refers to the column value that is affected by the update or insert statement.

In the preceding example, the argument is @pub_id, a convenient name, since this rule is to be bound to the pub_id column. You can use any name for the argument, but the first character must be "@." Using the name of the column or datatype to which the rule will be bound can help you remember what it is for.

The rule definition can contain any expression that is valid in a where clause, and can include arithmetic operators, comparison operators, like, in, between, and so on. However, the rule definition cannot reference any column or other database object directly. Built-in functions that do not reference database objects can be included.

The following example creates a rule that forces the values you enter to comply with a particular "picture." In this case, each value entered in the column must begin with "415" and be followed by 7 more characters:

create rule phonerule 
as @phone like "415_ _ _ _ _ _ _"

To make sure that the ages you enter for your friends are between 1 and 120, but never 17, try this:

create rule agerule 
as @age between 1 and 120 and @age ! = 17

Binding rules

After you have created a rule, use sp_bindrule to link the rule to a column or user-defined datatype.

Here is the complete syntax for sp_bindrule:

sp_bindrule rulename, objname [, futureonly]

The rulename is the name of the rule created with create rule. The objname is the name of the table and column, or of the user-defined datatype to which the rule is to be bound. If the parameter is not of the form table.column, it is assumed to be a user datatype.

Use the optional futureonly parameter only when binding a rule to a user-defined datatype. All columns of a specified user-defined datatype become associated with the specified rule unless you specify futureonly, which prevents existing columns of that user datatype from inheriting the rule. If the rule associated with a given user-defined datatype has previously been changed, Adaptive Server maintains the changed rule for existing columns of that user-defined datatype.

The following restrictions apply to rules:

You cannot bind a rule to a text, image, or timestamp datatype column.
Adaptive Server does not allow rules on system tables.

Rules bound to columns

You bind a rule to a column by using sp_bindrule with the rule name and the quoted table name and column name. This is how pub_idrule was bound to publishers.pub_id:

sp_bindrule pub_idrule, "publishers.pub_id"

Here is a rule to ensure that all the postal codes entered will have 946 as the first 3 digits:

create rule postalcoderule946 
as @postalcode like "946[0-9][0-9]"

Bind it to the postalcode column in friends_etc like this:

sp_bindrule postalcoderule946, "friends_etc.postalcode"

Rules cannot be bound to columns and used during the same batch. sp_bindrule cannot be in the same batch as insert statements that invoke the rule.

Rules bound to user-defined datatypes

You cannot bind a rule to a system datatype, but you can bind one to a user-defined datatype. To bind phonerule to a user-defined datatype called p#, enter:

sp_bindrule phonerule, "p#"

Precedence of rules

Rules bound to columns always take precedence over rules bound to user datatypes. Binding a rule to a column replaces a rule bound to the user datatype of that column, but binding a rule to a datatype does not replace a rule bound to a column of that user datatype.

A rule bound to a user-defined datatype is activated only when you attempt to insert a value into, or update, a database column of the user-defined datatype. Because rules do not test variables, do not assign a value to a user-defined datatype variable that would be rejected by a rule bound to a column of the same datatype.

Table 12-2 indicates the precedence when binding rules to columns and user datatypes where rules already exist:

Precedence of rules
New rule bound to	Old rule bound to
	User Datatype	Column
User Datatype	Replaces old rule	No change
Column	Replaces old rule	Replaces old rule

When you are entering data that requires special temporary constraints on some columns, you can create a new rule to help check the data. For example, suppose that you are adding data to the debt column of the friends_etc table. You know that all the debts you want to record today are between $5 and $200. To avoid accidentally typing an amount outside those limits, create a rule like this one:

create rule debtrule 
as @debt = $0.00 or @debt between $5.00 and $200.00

The @debt rule definition allows for an entry of $0.00 to maintain the default previously defined for this column.

Bind debtrule to the debt column like this:

sp_bindrule debtrule, "friends_etc.debt"

Rules and NULL values

You cannot define a column to allow nulls, and then override this definition with a rule that prohibits null values. For example, if a column definition specifies NULL and the rule specifies the following, an implicit or explicit NULL does not violate the rule:

@val in (1,2,3)

The column definition overrides the rule, even a rule that specifies:

@val is not null

After defining a rule

After you define a rule, the source text describing the rule is stored in the text column of the syscomments system table. Do not remove this information; doing so may cause problems for future versions of Adaptive Server. Instead, encrypt the text in syscomments by using sp_hidetext, described in the Reference Manual. For more information, see "Compiled objects".

Unbinding rules

Unbinding a rule disconnects it from a particular column or user-defined datatype. An unbound rule's definition is still stored in the database and is available for future use.

There are two ways to unbind a rule:

Use sp_unbindrule to remove the binding between a rule and a column or user-defined datatype.
Use sp_bindrule to bind a new rule to that column or datatype. The old one is automatically unbound.

Here is how to disassociate debtrule (or any other currently bound rule) from friends_etc.debt:

sp_unbindrule "friends_etc.debt"

The rule is still in the database, but it has no connection to friends_etc.debt.

To unbind a rule from the user-defined datatype p#, use:

sp_unbindrule "p#"

The complete syntax of sp_unbindrule is:

sp_unbindrule objname [, futureonly]

If the objname parameter you use is not of the form "table.column", Adaptive Server assumes it is a user-defined datatype. When you unbind a rule from a user-defined datatype, the rule is unbound from all columns of that type unless:

You give the optional futureonly parameter, which prevents existing columns of that datatype from losing their binding with the rule, or
The rule on a column of that user-defined datatype has been changed so that its current value is different from the rule being unbound.

Dropping rules

To remove a rule from the database entirely, use the drop rule command. Unbind the rule from all columns and user datatypes before you drop it. If you try to drop a rule that is still bound, Adaptive Server displays an error message, and drop rule fails. However, you need not unbind and then drop a rule to bind a new one. Simply bind a new one in its place.

To remove phonerule after unbinding it:

drop rule phonerule

The complete syntax for drop rule is:

drop rule [owner.]rule_name 
     [, [owner.]rule_name] ...

After you drop a rule, new data entered into the columns that previously were governed by it goes in without these constraints. Existing data is not affected in any way.

A rule can be dropped only by its owner.

Getting information about defaults and rules

The system procedure sp_help, when used with a table name, displays the rules and defaults that are bound to columns. This example displays information about the authors table in the pubs2 database, including the rules and defaults:

sp_help authors

sp_help also reports on a rule bound to a user-defined datatype. To check whether a rule is bound to the user-defined datatype p#, use:

sp_help "p#"

sp_helptext reports the definition (the create statement) of a rule or default.

If the source text of a default or rule was encrypted using sp_hidetext, Adaptive Server displays a message advising you that the text is hidden. For information about hiding source text, see sp_hidetext in the Reference Manual.

If the System Security Officer has reset the allow select on syscomments.text column parameter with sp_configure (as required to run Adaptive Server in the evaluated configuration), you must be the creator of the default or rule or a System Administrator to view the text of a default or rule through sp_helptext. For more information, see evaluated configuration in the Adaptive Server Glossary.


	Expand Search