COLUMN Change Definition (column_change_definition) 

You can modify the properties of a column by specifying a COLUMN change definition (column_change_definition) in the ALTER TABLE statement.

Syntax

<column_change_definition> ::=
  COLUMN <column_name> ADD <default_spec>
| COLUMN <column_name> ALTER <default_spec>
| COLUMN <column_name> DEFAULT NULL
| COLUMN <column_name> DROP DEFAULT
| COLUMN <column_name> NOT NULL

Explanation

ADD <default_spec>

The column must not contain a DEFAULT specificaton (default_spec) before the ALTER TABLE statement is executed with ADD <default_spec>. ADD <default spec> assigns a DEFAULT value to the column.

ALTER <default spec>

ALTER <default spec> changes the DEFAULT value assigned to the column. All of the rows that contain the old default value in the column remain unaltered.

DEFAULT NULL

DEFAULT NULL allows a NULL value for the column. The system does not check whether a NULL value violates existing CONSTRAINT definitions in the table. For this reason, inserting the NULL value can fail when an INSERT or UPDATE statement is executed.

DROP DEFAULT

DROP DEFAULT drops the DEFAULT specification of the column. If the column is the foreign key column of a referential CONSTRAINT definition with the DELETE RULE ON DELETE SET DEFAULT, the ALTER TABLE statement will fail.

NOT NULL

NOT NULL can only be specified if the column does not contain NULL values. You cannot add a NULL value to the column once the ALTER TABLE statement has been successfully executed.