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.
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>)]
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 |
Explanation |
DEFAULT <literal> |
The literal must be comparable with the data type of the column. |
DEFAULT USER |
Returns the user name of the current 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 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 system database administrator user and can only be specified for columns of 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 timestamp and can only be specified for columns of the data type TIMESTAMP. |
DEFAULT UTCDATE |
Returns the current UTC timestamp (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) (from Version 7.4.04.02). |
DEFAULT TIMEZONE |
Returns the time difference in the format hhmmss between your local time and UTC time and can only be specified for columns that have 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 implicitly creates a key column SYSKEY CHAR(8) BYTE. 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. |
See also:
DEFAULT Predicate (default_predicate)
Number Generator
Specifying the DEFAULT SERIAL(n) DEFAULT specification 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 n at the start.
The SERIAL(n) data type can be used as a DEFAULT specification for columns that can contain only fixed point numbers without 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 SERIAL default specification 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.