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

default_name: is the name of the default, which 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.

constant_expression: is an expression that does not include the names of any columns or other database objects. It can include global variables and 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

Creates a default called D1 that uses the @@spid global variable:

create default D1 as @@spid

Example 2

Defines a default value, then binds it to the appropriate column or user-defined datatype:

create default phonedflt as "UNKNOWN"

Example 3

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 4

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 an 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.
create default performs error checking for check constraints before it creates the default.
To hide the source test of a default, use sp_hidetext.

Restrictions

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 using drop default before you create a new one of the same name; you must unbind a default using sp_unbindefault, before you drop it.

Datatype compatibility

Adaptive Server generates an error message when it attempts 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. See the set command.

Getting information about defaults

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.

Defaults and nulls

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-3 illustrates the relationship between the existence of a default and the definition of a column as NULL or NOT NULL.

**Table 1-3: 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

Specifying a default value in create table

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

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

Granular permissions enabled	With granular permissions enabled, you must have create default privilege. To create a default for another user, you must have create any default privilege.
Granular permissions disabled	With granular permissions disabled, you must be the database owner, a user with sa_role, or the create default privilege.

Auditing

Values in event and extrainfo columns of sysaudits are:

Event	Audit option	Command or access audited	Information in extrainfo
14	create	create default	Roles – current active roles Keywords or options – NULL Previous value – NULL Other information – NULL Current value – NULL Proxy information – original login name, if set proxy is in effect