Default Values

Default values are preconfigured values that are inserted into table columns instead of NULL when there are no specific values provided during an INSERT operation.

Example
CREATE TABLE telephonelist(
              name VARCHAR(10),
          phone_number VARCHAR(10),
          type VARCHAR(10) DEFAULT 'home',
          alterationtime TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

INSERT INTO telephonelist (name, phone_number) VALUES ('Meyer', '1234');
INSERT INTO telephonelist (name, phone_number) VALUES ('Müller','5678');
INSERT INTO telephonelist (name, type, phone_number) VALUES ('Meyer', 'work', '9999');
UPDATE telephonelist SET name='Meier', alterationtime=DEFAULT WHERE phone_number='1234';

SELECT * FROM telephonelist;
Results
name phone_number type alterationtime
Meier 1234 home 2010-12-13 16:39:02.393000
Müller 5678 home 2010-12-13 16:39:00.823000
Meyer 9999 work 2010-12-13 16:39:01.62600

Statements that manipulate default values

Default values can be created, altered, and deleted with the following statements:

Default values are explicitly or implicitly used in tables with the following statements:

  • INSERT: DEFAULT as a 'value' from a column (INSERT INTO t(i,j,k) VALUES (1, DEFAULT, 5)) or DEFAULT VALUES for all columns (INSERT INTO t DEFAULT VALUES) or implicitly at column selection (for the columns that were not selected)
  • IMPORT: implicitly at column selection (for the columns that were not selected)
  • UPDATE: DEFAULT as a 'value' for a column (SET i=DEFAULT)
  • MERGE: DEFAULT as a 'value' for column in the INSERT and UPDATE parts or implicitly in the INSERT part at column selection (for the column that were not selected)
  • ADD COLUMN: if a default value was specified for the inserted column with DEFAULT

Permitted default values

The following expressions can be used as default values: 

  • Constants
  • Constant values at evaluation time like CURRENT_USER or CURRENT_DATE
  • Value expressions, which only contain the functions of the two aforementioned expressions

 Default values are limited to 2000 characters.

Example
CREATE TABLE t (i DECIMAL, vc VARCHAR(100) );
ALTER TABLE t ALTER COLUMN i SET DEFAULT 1;
ALTER TABLE t ALTER COLUMN i SET DEFAULT 1+2;
ALTER TABLE t ALTER COLUMN vc SET DEFAULT 'abc'||TO_CHAR(CURRENT_DATE);
ALTER TABLE t ALTER COLUMN vc SET DEFAULT CURRENT_USER;

Display of default values

Default values can be displayed in the column COLUMN_DEFAULT of system tables EXA_ALL_COLUMNS, EXA_DBA_COLUMNS, and EXA_USER_COLUMNS.

Example
CREATE TABLE T (I DECIMAL DEFAULT 3+4);
SELECT column_default FROM exa_all_columns
            WHERE column_table='T'
            AND   column_name='I';

Possible sources of error

In most cases, default values must work as expected. However, you should consider the following to ensure error-free operations:

  • With the MODIFY COLUMN, as an existing default value is adopted when changing the data type. If the old default value is not appropriate for the new data type, then an error message is returned.
  • If entries used as default values have different lengths, then it might not fit in the table column (for example, CURRENT_USER or CURRENT_SCHEMA). At the time of insertion, if the length of the default value is more than the specified value, then an error message is returned. The insertion will not be performed. In this case, the default value must be reduced to the length of the column using SUBSTR[ING].
  • Interpretation of default values can depend on the format model (for example, a DATE value). In this case, it is advisable to explicitly enforce the format (for example, using TO_DATE with format specification).
Example
CREATE TABLE t1 (d DATE DEFAULT '2006-12-31');
CREATE TABLE t2 (d DATE DEFAULT TO_DATE('2006-12-31','YYYY-MM-DD'));
ALTER SESSION SET NLS_DATE_FORMAT='DD.MM.YYYY';

-- with INSERT in t1 an error message occurs but not with t2
INSERT INTO t1 DEFAULT VALUES;
INSERT INTO t2 DEFAULT VALUES;