DEFAULT clause of CREATE TABLE

Use the DEFAULT clause in the CREATE TABLE statement to specify the default value for the database server to insert into a column when no explicit value for the column is specified.

This syntax fragment is part of the Column definition.

DEFAULT Clause

Notes:

¹ HCL OneDB™ extension
² See DATETIME Field Qualifier


Element	Description	Restrictions	Syntax
`label`	Name of a security label	Must exist and must belong to the security policy that protects the table. The column must be of type IDSSECURITYLABEL.	Identifier
`literal`	String of alphabetic or numeric characters	Must be an appropriate data type for the column. See Using a Literal as a Default Value.	Expression

You cannot specify default values for SERIAL, BIGSERIAL, or SERIAL8 columns.

Using NULL as a default value

If you specify no default value for a column, the default is NULL unless you place a NOT NULL constraint on the column. In this case, no default value exists.

If you specify NULL as the default value for a column, you cannot specify a NOT NULL constraint as part of the column definition. (For details of NOT NULL constraints, see Using the NOT NULL Constraint.)

NULL is not a valid default value for a column that is part of a primary key.

For columns of large-object data types like BYTE, TEXT, BLOB, or CLOB, or of field-value pair data types like BSON or JSON, the only valid default value is NULL.

Example of setting the default value of a BSON column

The following CREATE TABLE statement fails with an exception, because the BSON data type of the data column requires NULL as its default, rather than the specified field-value pair:

CREATE TABLE tab1
  (
    id VARCHAR(128) NOT NULL,
    data "informix".BSON DEFAULT '"{id:1}"::JSON',
    modcount BIGINT,
    flags INTEGER DEFAULT 12,
    PRIMARY KEY (data)
  );

(U0001) - bson_to_char: unhandled storage type '98'
Error in line 8

The following successful statement replaces the non-NULL default value for data with the only valid default for BSON columns, and drops the PRIMARY KEY constraint on the data column.

CREATE TABLE tab1
  (
    id VARCHAR(128) NOT NULL,
    data "informix".BSON DEFAULT NULL,
    modcount BIGINT,
    flags INTEGER DEFAULT 12
  );

If this successful revision of the first example had defined a the PRIMARY KEY constraint on the data column, however, as in the first example, the CREATE TABLE statement would have failed with a different error, because for any data type, a column that has NULL as its default value cannot be part of a primary key.

Using a Literal as a Default Value

You can designate a literal value as a default value. A literal value is a string of alphabetic or numeric characters. To use a literal value as a default value, you must adhere to the syntax restrictions in the following table.


For Columns of Data Type	Format of Default Value
BOOLEAN	Use `'t'` or `'f'` (respectively for true or false) as a Quoted String.
CHAR, CHARACTER VARYING, DATE, VARCHAR, NCHAR, NVARCHAR, LVARCHAR	Quoted String. DATE literals must be of the format that the DBDATE (or else GL_DATE) environment variable specifies. In the default locale, if neither DBDATE nor GL_DATE is set, date literals must be of the `mm/dd/yyyy` format.
DATETIME	Literal DATETIME
BIGINT, DECIMAL, FLOAT, INT8, INTEGER, MONEY, SMALLFLOAT, SMALLINT	Literal Number
INTERVAL	Literal INTERVAL
Opaque data types	Quoted String in format of Single-Column Constraint Format

For example, the following statement includes a column definition with a literal DATETIME:

CREATE TABLE tab1
  (
    id VARCHAR(128) NOT NULL,
    date DATETIME YEAR TO FRACTION(3) DEFAULT DATETIME(1971-01-01 00:00:00.000) 
         YEAR TO FRACTION(3),
    modcount BIGINT,
    flags INTEGER DEFAULT 12
  );

Using a Constant Expression as a Default Value

You can specify a constant expression as the default column value.

The following table lists constant expressions that you can specify, the data type requirements, and the recommended size (in bytes) for their corresponding columns.

Table 1. Constant expressions as default values
Constant Expression	Data Type Requirement	Recommended Size
CURRENT, SYSDATE	DATETIME column with matching qualifier	Enough bytes to store the longest DATETIME value for the locale
DBSERVERNAME, SITENAME	CHAR, VARCHAR, NCHAR, NVARCHAR, or CHARACTER VARYING column	128 bytes
TODAY	DATE column	Enough bytes to store the longest DATE value for the locale
USER, CURRENT_USER	CHAR, VARCHAR, NCHAR, NVARCHAR, or CHARACTER VARYING column	32 bytes

These column sizes are recommended because, if the column length is too small to store the default value during INSERT or ALTER TABLE operations, the database server returns an error.

You cannot designate a constant expression that behaves like a variant function (that is, CURRENT, SYSDATE, USER, TODAY, SITENAME, or DBSERVERNAME) as the default value for a column that holds an OPAQUE or DISTINCT data type. In addition, larger column sizes are required if the data values are encrypted, or if they are encoded in the Unicode character set of the UTF-8 locale. (See the description of the SET ENCRYPTION statement later in this chapter for more information about storage size requirements for encrypted data.)

For descriptions of these functions, see Constant Expressions.

The following example creates a table with columns that have literal default values. The acc_id column value defaults to the authorization identifier of the current user.

CREATE TABLE accounts (
   acc_num INTEGER DEFAULT 1,
   acc_type CHAR(1) DEFAULT 'A',
   acc_descr CHAR(20) DEFAULT 'New Account',
   acc_date DATETIME YEAR TO DAY DEFAULT SYSDATE DATETIME YEAR TO DAY,
   acc_id CHAR(32) DEFAULT CURRENT_USER);