DEFAULT clause of ALTER TABLE

Use the DEFAULT clause of the ALTER TABLE statement to specify value that the database server should insert in a column in DML operations that specify no explicit value for the column.

This syntax fragment is part of the ADD Column Clause and the MODIFY Clause.

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	Literal default value for the column	Must be appropriate for the data type of the column. See Using a Literal as a Default Value.	Expression

Usage

If the table that you are altering already has rows in it when you use the ALTER TABLE ADD statement to add a column that contains a default value, the default values are applied to all existing rows and rows inserted after the ALTER TABLE ADD statement that added the new column.

Similarly, when the ALTER TABLE MODIFY statement uses the DEFAULT clause to define a new default value for a column that had no default or that had a different default, the rows that existed before the column was modified are unchanged, unless you update those rows to insert some NULL or non-NULL value. New rows that you insert will have the default value that the DEFAULT clause of the ALTER TABLE MODIFY statement specified, unless you insert some other value into the new column.

You cannot specify a default value for columns of type SERIAL, SERIAL8, or BIGSERIAL. For information about using the ALTER TABLE MODIFY statement to set the next value of a serial column to an arbitrary value higher than the current maximum, see Altering the Next Serial Value.

For columns of DISTINCT or OPAQUE data types, you cannot specify as the default value a constant expression (such as CURRENT, SYSDATE DBSERVERNAME, SITENAME, TODAY, USER, or CURRENT_USER) that behaves like a variant function.

The DEFAULT NULL keywords

If you use the ALTER TABLE ADD statement to add a new column with an explicit default value, or if you use the ALTER TABLE MODIFY statement to change the data type or the default value of an existing column, the following restrictions apply to NULL as a default value:

For columns of large-object data types like BYTE, TEXT, BLOB, or CLOB, or for columns of key-value pair (KVP) data types like BSON or JSON, the only valid default value is NULL.
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.
Serial columns cannot have a default value, including DEFAULT NULL.
If a column was created or altered with no DEFAULT clause, and with no implicit or explicit NOT NULL constraint, its implicit default value is NULL.

Examples of default column values

The following statement adds column item_velocity to the items table, with NULL as its implicit default value:

ALTER TABLE items
   ADD (item_velocity DECIMAL(6,3) BEFORE total_price);

The following statement modifies the default value of column item_velocity by replacing NULL with an explicit default value:

ALTER TABLE items
   MODIFY (item_velocity DECIMAL(6,3) DEFAULT 299792.458);

Each existing row in the items table has the default value of NULL for the item_velocity column. Any new rows have the default value of 299792.458.

The next example adds a new column of data type CHAR(12) to the items table, where the new item_color column has an explicit default value of translucent:

ALTER TABLE items 
   ADD (item_color CHAR(12) DEFAULT "translucent"
   BEFORE item_velocity);

Suppose that table tabB has the following schema:

CREATE TABLE tabB
  (
    id VARCHAR(128) NOT NULL,
    data DATE DEFAULT TODAY,
    modcount BIGINT,
    flags INTEGER DEFAULT 12,
  );

The following statement modifies tabB by changing the data type and the default value of the data column from type DATE with a default of TODAY to a BSON type with a default of NULL:

ALTER TABLE tabB
   MODIFY ( data "informix".BSON DEFAULT NULL);

The original default value of TODAY for the data column is not valid for a key-value pair data type like BSON.

For more information about the options of the DEFAULT clause, refer to the DEFAULT clause of CREATE TABLE topic of the CREATE TABLE statement.