Home

	Expand Search

Reference Manual: Commands

Chapter 1 Commands

create default

create default

Description

Specifies a value to insert in a column (or in all columns of a user-defined datatype) if no value is explicitly supplied at insert time.

Syntax

create default [owner.]default_name 
    as constant_expression

Parameters

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

is an expression that does not include the names of any columns or other database objects. It cannot include global variables, but can include built-in functions that do not reference database objects. Enclose character and date constants in quotes and use a "0x" prefix for binary constants.

Examples

Example 1

Defines a default value. Now, you need to bind it to the appropriate column or user-defined datatype with sp_bindefault:

create default phonedflt as "UNKNOWN"

Example 2

sp_bindefault phonedflt, "authors.phone"

The default takes effect only if there is no entry in the phone column of the authors table. No entry is different from a null value entry. To get the default, issue an insert command with a column list that does not include the column that has the default.

Example 3

Creates a default value, todays_date, that inserts the current date into the columns to which it is bound:

create default todays_date as getdate()

Usage

• Bind a default to a column or user-defined datatype--but not a Adaptive Server-supplied datatype--with sp_bindefault.

• You can bind a new default to a datatype without unbinding the old one. The new default overrides and unbinds the old one.

• To hide the source test of a default, use sp_hidetext.

• You can create a default only in the current database.

• You cannot combine create default statements with other statements in a single batch.

• You must drop a default with drop default before you create a new one of the same name; you must unbind a default with sp_unbindefault, before you drop it.

• Adaptive Server generates an error message when it tries to insert a default value that is not compatible with the column's datatype. For example, if you bind a character expression such as "N/A" to an integer column, any insert that does not specify the column value fails.

• If a default value is too long for a character column, Adaptive Server either truncates the string or generates an exception, depending on the setting of the string_rtruncation option. For more information, see the set command.

• Default definitions are stored in syscomments.

• After a default is bound to a column, its object ID is stored in syscolumns. After a default is bound to a user-defined datatype, its object ID is stored in systypes.

• To rename a default, use sp_rename.

• For a report on the text of a default, use sp_helptext.

• If a column has both a default and a rule associated with it, the default value must not violate the rule. A default that conflicts with a rule cannot be inserted. Adaptive Server generates an error message each time it attempts to insert such a default.

• If a column does not allow nulls, and you do not create a default for the column, when a user attempts to insert a row but does not include a value for that column, the insert fails and Adaptive Server generates an error message.

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

  Table 1-5: Relationship between nulls and column defaults

  Column null type	No entry, no default	No entry, default exists	Entry Is null, No default	Entry Is null, default exists
  NULL	Null inserted	Default value inserted	Null inserted	Null inserted
  NOT NULL	Error, command fails	Default value inserted	Error, command fails	Error, command fails

• You can define column defaults using the default clause of the create table statement as an alternative to using create default. However, these column defaults are specific to that table; you cannot bind them to other tables. See create table and alter table for information about integrity constraints.

Standards

ANSI SQL - Compliance level: Transact-SQL extension.

Use the default clause of the create table statement to create ANSI SQL-compliant defaults.

Permissions

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

See also

Commands

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

System procedures

sp_bindefault, sp_help, sp_helptext, sp_rename, sp_unbindefault