5.2.8 Setting a default value for a column (DEFAULT clause)

If you want to have predefined values stored automatically when data is added or updated, consider setting default values for columns. A default value for a column is the value that will be stored in the applicable column in the following cases:

• Row insertion by the INSERT statement

  A default value for a column is stored in the following cases:

  • DEFAULT is specified for the insertion value.

  • DEFAULT VALUES is specified.

  • The name of the column in which data is to be inserted is omitted (except when all column names are omitted).

  • A row is inserted in a viewed table (a default value for the column is stored in the column in the underlying table that does not correspond to a column in the viewed table).

• Column value updating by the UPDATE statement

  If DEFAULT is specified for an update value, the default value for the column is stored.

• Data import by the adbimport command

  When data is imported by the adbimport command and a field's data in the input data file is empty, the default value for the column is stored.

  Note that if NULL is specified in the adb_import_null_string import option, the null value, not the default value for the column, is stored.

To set a default value for a column, specify the DEFAULT clause in the CREATE TABLE statement. For details about the DEFAULT clause, see DEFAULT clause in the manual HADB SQL Reference.

Note

If the DEFAULT clause is omitted, the default value for the column will be the null value.

The following figure shows an example of data storage when a default value for a column is set by specifying the DEFAULT clause.

Figure 5‒5: Example of data storage when a default value for a column is set by specifying the DEFAULT clause

Explanation

1. Specifies the DEFAULT clause for the PUR-NUM column to define the numeric data 0 as the default value for the column.

2. Specifies the DEFAULT clause for the PUR-DATE column to define the date as the default value for the column. CURRENT_DATE means the date the INSERT statement or the UPDATE statement is executed or the date the adbimport command is started.

3. Because the PUR-NUM column is omitted from the columns subject to insertion, the default value for the column (numeric data 0) is stored in the PUR-NUM column.

4. Because DEFAULT is specified as the insertion value, the default value for the column (the date the INSERT statement is executed) is stored in the PUR-DATE column.

For details about the values that can be specified as the default value for a column, see DEFAULT clause in the manual HADB SQL Reference.

To Page Top