DEFAULT Specification (default_spec)

A DEFAULT specification (default_spec) is formed by specifying the keyword DEFAULT and a DEFAULT value. The maximum length of a default value is 254 characters.

Structure

Syntax Syntax

<default_spec> ::=
  DEFAULT <literal>
| DEFAULT NULL
| DEFAULT USER
| DEFAULT USERGROUP
| DEFAULT SYSDBA
| DEFAULT DATE
| DEFAULT TIME
| DEFAULT TIMESTAMP
| DEFAULT UTCDATE
| DEFAULT UTCDIFF
| DEFAULT TIMEZONE
| DEFAULT TRUE
| DEFAULT FALSE
| DEFAULT TRANSACTION
| DEFAULT STAMP
| DEFAULT SERIAL[<unsigned_integer>]

End of the code.

Examples

SQL Tutorial, Number Generator for a Single Column of a Table

Explanation

If a DEFAULT specification has been made for a column, the default value (<literal>, NULL, USER,...) must be a value that can be inserted in the column.

DEFAULT Specification (default_spec)
DEFAULT Specification	Explanation
DEFAULT <literal>	The literal must be comparable with the data type of the column.
DEFAULT USER	Returns the name of the current database user and can only be specified for columns that have the data type [VAR]CHAR(n) or CHAR(n) (n >= 32)
DEFAULT USERGROUP	Returns the user group name for members of a user group, or the user name for database users that do not belong to a user group. This DEFAULT specification can only be specified for columns of the data type [VAR]CHAR(n) (n>=32).
DEFAULT SYSDBA	Returns the name of the database administrator and can only be specified for columns of the data type [VAR]CHAR(n) (n>=32).
DEFAULT DATE	Returns the current date and can only be specified for columns of the data type DATE.
DEFAULT TIME	Returns the current time and can only be specified for columns of the data type TIME.
DEFAULT TIMESTAMP	Returns the current time stamp and can only be specified for columns of the data type TIMESTAMP.
DEFAULT UTCDATE	Returns the current UTC time stamp (Greenwich Mean Time) and can only be specified for columns that have the data type TIMESTAMP.
DEFAULT UTCDIFF	Returns the time difference (in hours) between your local time and UTC time and can only be specified for columns that have the data type FLOAT(p) (p >= 4) or FIXED(p,s) (s >= 2, p >= s+2).
DEFAULT TIMEZONE	Returns the time difference in the format hhmmss between your local time and UTC time and can only be specified for columns of the data type FLOAT(p) (p>=6) or FIXED(p,s) (s>=0, p>=s+6).
DEFAULT TRUE/DEFAULT FALSE	Can only be specified for columns of the data type BOOLEAN.
DEFAULT TRANSACTION	Returns the identification of the current transaction and can only be specified for columns of the data type CHAR(n) BYTE (n>=8).
DEFAULT STAMP	Returns a value of eight characters in length that is unique within the database system and can only be specified for columns of the data type CHAR(n) BYTE (n>=8). If a table is defined without a key column, the database system creates a key column SYSKEY CHAR(8) BYTE implicitly. The key values in the SYSKEY column are only unique within a table. This means the SYSKEY column in two different tables may contain the same values. If a unique key is desired across the entire database system, a key column can be defined with the DEFAULT specification STAMP.
DEFAULT SERIAL [(<unsigned_integer>)]	Delivers a number generator for positive integers and can only be specified for columns that have the data type INTEGER, SMALLINT, and FIXED without decimal places (see below: Number Generator) The first value generated by the generator can be defined by specifying an unsigned integer (unsigned_integer) (must be greater than 0). If this definition is missing, 1 is defined as the first value. If the value 0 is inserted in this column by an INSERT statement, the current number generator value is supplied and not the value 0. Each table may not contain more than one column with the DEFAULT specification DEFAULT SERIAL.

Number Generator

Specifying the DEFAULT specification DEFAULT SERIAL(n) or the data type SERIAL(n) causes a number generator to be generated that creates positive whole numbers implicitly from 1 or from a prespecified value starting with n.

The data type SERIAL(n) can be used as a DEFAULT specification for columns which can only contain fixed point numbers or decimal places, as SERIAL(n) is mapped to FIXED(10) DEFAULT SERIAL(n).

The maximum number generator value generated is (10**m)-1 if the default specification DEFAULT SERIAL is defined for a column of the data type FIXED(m).

SERIAL columns can only be assigned a value when a row is inserted. The values of a SERIAL column cannot be changed with an UPDATE statement. A SERIAL column, therefore, can be used to determine the insertion sequence and identify a row in a table uniquely.

More Information

DEFAULT Predicate (default_predicate)