Default values for table columns
Db2 defines some default values, and you define others (by using the DEFAULT clause in the CREATE TABLE or ALTER TABLE statement).
If a column is defined as NOT NULL WITH DEFAULT or if you do not specify NOT NULL, Db2 stores a default value for a column whenever an insert or load does not provide a value for that column. If a column is defined as NOT NULL, Db2 does not supply a default value.
Default values defined by Db2
Db2 generates a default value for ROWID columns. Db2 also determines default values for columns that users define with NOT NULL WITH DEFAULT, but for which no specific value is specified, as shown in the following table.
| For columns of... | Data types | Default |
|---|---|---|
| Numbers | SMALLINT, INTEGER, BIGINT, DECIMAL, NUMERIC, REAL, DOUBLE, DECFLOAT, or FLOAT | 0 |
| Fixed-length strings | CHAR or GRAPHIC BINARY
|
Blanks Hexadecimal zeros
|
| Varying-length strings | VARCHAR, CLOB, VARGRAPHIC, DBCLOB, VARBINARY, or BLOB | Empty string |
| Dates | DATE | CURRENT DATE |
| Times | TIME | CURRENT TIME |
| Timestamps | TIMESTAMP | CURRENT TIMESTAMP |
| ROWIDs | ROWID | Db2-generated |
User-defined default values
You can specify a particular default value, such as:
DEFAULT 'N/A'When you choose a default value, you must be able to assign it to the data type of the column. For example, all string constants are VARCHAR. You can use a VARCHAR string constant as the default for a CHAR column even though the type isn't an exact match. However, you could not specify a default value of 'N⁄A' for a column with a numeric data type.
In the next example, the columns are defined as CHAR (fixed length). The special registers (USER and CURRENT SQLID) that are referenced contain varying length values.
For example, if you want a record of each user who inserts any row of a table, define the table with two additional columns:
PRIMARY_ID CHAR(8) WITH DEFAULT USER,
SQL_ID CHAR(8) WITH DEFAULT CURRENT SQLID,You can then create a view that omits those columns and allows users to update the view instead of the base table. Db2 then adds, by default, the primary authorization ID and the SQLID of the process.
When you add columns to an existing table, you must define them as nullable or as not null with default. Assume that you add a column to an existing table and specify not null with default. If Db2 reads from the table before you add data to the column, the column values that you retrieve are the default values. With few exceptions, the default values for retrieval are the same as the default values for insert.
Default values for ROWID
Db2 always generates the default values for ROWID columns.