Home

	Expand Search

Transact-SQL User's Guide

Chapter 12 Defining Defaults and Rules for Data

Creating defaults

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:

1. Define the default, using create default.

2. Bind the default to the appropriate table column or user-defined datatype using sp_bindefault.

3. 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.

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".