7.10.1 Specification format and rules for the DEFAULT clause

The DEFAULT clause specifies the default value for a column. The default value for a column is the default value that is stored in the column in any of the circumstances described below.

When inserting rows with the INSERT statement

The default value for the column is stored in the following circumstances:
- DEFAULT is specified in the insertion value
- DEFAULT VALUES is specified
- A column name is not specified for the column into which data is to be inserted (unless all column names are omitted)
- A row is inserted into a viewed table (default values are stored in the columns of the viewed table that do not correspond to the columns of the underlying table)
When updating column values with the UPDATE statement

The default value for the column is stored if DEFAULT is specified in the update value.
When importing data with the adbimport command

When importing data with the adbimport command, the default value for the column is stored if the field data in the input data file is an empty character string.

If you want to store the null value rather than the default value for the column, specify NULL for the import option adb_import_null_string.

Organization of this subsection

(1) Specification format
(2) Explanation of specification format
(3) Rules
(4) Example

(1) Specification format

DEFAULT-clause ::= DEFAULT default-option
  default-option ::= {literal | CURRENT_DATE | CURRENT_TIME[(p)]
                    | CURRENT_TIMESTAMP[(p)] | CURRENT_USER | NULL}

To Page Top

(2) Explanation of specification format

literal:

Specifies the default value for a column in the form of a literal. For details about literals, see 6.3 Literals.

The following table shows the types of literals that can be specified in default-option depending on the data type of the column whose default value is to be specified.

Table 7‒2: Types of literals that can be specified in default-option depending on the data type of the column whose default value is to be specified
Data type of the column whose default value is to be specified		Literals that can be specified in default-option
		Numeric literal	Character string literal	Date literal	Time literal	Time stamp literal	Binary literal
Numeric data		Y	N	N	N	N	N
Character string data		N	Y^#1	N	N	N	N
Datetime data	`DATE` type	N	Y^#2	Y	N	Y	N
	`TIME` type	N	Y^#2	N	Y	N	N
	`TIMESTAMP` type	N	Y^#2	Y	N	Y	N
Binary data		N	N	N	N	N	Y^#1

Legend:

Y: Can be specified. However, storage assignment rules apply.^#3

N: Cannot be specified.

#1

Character string literals or binary literals of 1,024 bytes or more cannot be specified.

#2

The character string literal must be represented in a relevant predefined input representation. For information about predefined input representations, see 6.3.3 Predefined character-string representations.

#3

For details about the storage assignment rules, see (2) Storage assignments between data types in 6.2.2 Data types that can be converted, assigned, and compared.

For example, because the storage assignment rules are applied, the CREATE TABLE statement will result in an error if the data length of the character string literal specified as the default value for a column exceeds the data length of the column whose DEFAULT clause was specified.

CURRENT_DATE:

The default value for the column will be the date when the INSERT or UPDATE statement is executed, or when the adbimport command is launched.

CURRENT_DATE can be specified for a column of type DATE or TIMESTAMP.

For details about the rules for specifying CURRENT_DATE, see 6.4.1 CURRENT_DATE.

CURRENT_TIME[(p)]:

The default value for the column will be the time when the INSERT or UPDATE statement is executed, or when the adbimport command is launched.

Specify the fractional seconds precision (the number of digits to the right of the decimal point) in p. If (p) is omitted, it is assumed that p = 0.

CURRENT_TIME can be specified for a column of type TIME.

For details about the rules for specifying CURRENT_TIME, see 6.4.2 CURRENT_TIME.

CURRENT_TIMESTAMP[(p)]:

The default value for the column will be the date and time when the INSERT or UPDATE statement is executed, or when the adbimport command is launched.

Specify the fractional seconds precision (the number of digits to the right of the decimal point) in p. If (p) is omitted, it is assumed that p = 0.

CURRENT_TIMESTAMP can be specified for a column of type DATE or TIMESTAMP.

For details about the rules for specifying CURRENT_TIMESTAMP, see 6.4.3 CURRENT_TIMESTAMP.

CURRENT_USER:

The default value for the column will be the authorization identifier of the user executing the INSERT statement, UPDATE statement, or adbimport command.

CURRENT_USER can be specified for a column of type CHARACTER or VARCHAR.

For details about the rules for specifying CURRENT_USER, see 6.5.1 CURRENT_USER.

NULL:

The default value for the column will be the null value.

NULL cannot be specified for columns having the NOT NULL constraint (the constraint to not allow null values).

Note

When CURRENT_DATE, CURRENT_TIME[(p)], or CURRENT_TIMESTAMP[(p)] is specified, the corresponding date and time information is acquired by the HADB server.
When you store the default value for a column on multiple rows by using a single SQL statement, if you specify CURRENT_DATE, the same date is stored on all rows. If you specify CURRENT_TIME[(p)], the same time is stored on all rows. If you specify CURRENT_TIMESTAMP[(p)], the same date and time is stored on all rows.

To Page Top

(3) Rules

If the DEFAULT clause is omitted, the default value for the column will be the null value.
When storing data in a column where a default value is specified, storage assignment rules apply. For example, if CURRENT_DATE is specified for a TIMESTAMP type column, 00:00:00 is assigned to the time portion in accordance with the assignment rules. For details about the assignment rules, see (2) Storage assignments between data types in 6.2.2 Data types that can be converted, assigned, and compared.
The precision of the fractional seconds acquired by CURRENT_TIME(p) or CURRENT_TIMESTAMP(p) depends on the capabilities of the hardware. For example, if you specify CURRENT_TIME(12), depending on the hardware you are using, you might not be able to acquire 12 digits of fractional seconds precision.

Example:

10:35:55.123456000000

As shown above, if only six digits of fractional seconds precision can be acquired, the 7th and subsequent digits will be 0.

To Page Top

(4) Example

Example

Define a sales history table (SALESLIST) using the DEFAULT clause to set the default value of the date of purchase (PUR-DATE) column.

CREATE FIX TABLE "SALESLIST"
      ("USERID" CHAR(6),
       "PUR-CODE" CHAR(4),
       "PUR-NUM" SMALLINT,
       "PUR-DATE" DATE DEFAULT CURRENT_DATE)
    IN "DBAREA01"
    PCTFREE=20
    CHUNK=200

The underlined portion indicates the DEFAULT clause.

To Page Top