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


Restrictions


Datatype compatibility


Getting information about defaults


Defaults and rules

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

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