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)