Categories:

Table, View, & Sequence DDL

ALTER TABLE … ALTER COLUMN

This topic describes how to modify one or more column properties for a table using an ALTER COLUMN clause in a ALTER TABLE statement.

The following table describes the supported/unsupported actions for modifying column properties:

Action

Supported

Unsupported

Notes

Default Values

Drop the default for a column (i.e. DROP DEFAULT).

Change the default sequence for a column (i.e. SET DEFAULT seq_name.NEXTVAL).

Use only for columns that have a sequence already.

Change the default for a column, unless the default is a sequence.

Add a default for a column.

Nullability

Change the nullability of a column (i.e. SET NOT NULL or DROP NOT NULL).

Data Types

Change a column data type to a synonymous type (e.g. STRING to VARCHAR).

Change a column data type to a different type (e.g. STRING to NUMBER).

Increase the length of a text/string column (e.g. VARCHAR(50) to VARCHAR(100)).

Decrease the length of a text/string column (e.g. VARCHAR(50) to VARCHAR(25)).

Increase the precision of a number column (e.g. NUMBER(10,2) to NUMBER(20,2)).

Decrease the precision of a number column (e.g. NUMBER(20,2) to NUMBER(10,2)).

Only allowed if the new precision is sufficient to hold all values currently in the column. In addition, decreasing the precision can impact Time Travel (see Usage Notes for details).

Change the scale of a number column (e.g. NUMBER(10,2) to NUMBER(10,4)).

Comments

Add or overwrite the comment for a column.

In this Topic:

Syntax

ALTER TABLE <name> { ALTER | MODIFY } [ ( ]
                                              [ COLUMN ] <col1_name> DROP DEFAULT
                                            , [ COLUMN ] <col1_name> SET DEFAULT <seq_name>.NEXTVAL
                                            , [ COLUMN ] <col1_name> { [ SET ] NOT NULL | DROP NOT NULL }
                                            , [ COLUMN ] <col1_name> [ [ SET DATA ] TYPE ] <type>
                                            , [ COLUMN ] <col1_name> COMMENT '<string>'
                                          [ , [ COLUMN ] <col2_name> ... ]
                                          [ , ... ]
                                      [ ) ]

Usage Notes

  • A single ALTER TABLE statement can be used to modify multiple columns in a table. Each change is specified as a clause consisting of the column and column property to modify, separated by commas:

    • Use either the ALTER or MODIFY keyword to initiate the list of clauses (i.e. columns/properties to modify) in the statement.

    • Parentheses can be used for grouping the clauses, but are not required.

    • The COLUMN keyword can be specified in each clause, but is not required.

    • The clauses can be specified in any order.

  • When setting a column to NOT NULL, if the column contains NULL values, an error is returned and no changes are applied to the column.

  • To change the default sequence for a column, the column must already have a default sequence. You cannot use the command ALTER TABLE ... SET DEFAULT <seq_name> to add a sequence to a column that does not already have a sequence.

  • When setting the TYPE for a column, the specified type (i.e. type) must be NUMBER or a text data type (VARCHAR, STRING, TEXT, etc.).

    • For the NUMBER data type, TYPE can be used to:

      • Increase the precision of the specified text column.

      • Decrease the precision of the specified text column if the new precision is sufficient to hold all data values currently in the column.

    • For text data types, TYPE can be used only to increase the length of the column.

  • If the precision of a column is decreased below the maximum precision of any column data retained in Time Travel, you will not be able to restore the table without first increasing the precision.

Examples

Example setup:

CREATE OR REPLACE TABLE t1 (
   c1 NUMBER NOT NULL,
   c2 NUMBER DEFAULT 3,
   c3 NUMBER DEFAULT seq1.nextval,
   c4 VARCHAR(20) DEFAULT 'abcde',
   c5 STRING);

DESC TABLE t1;

+------+-------------------+--------+-------+-------------------------+-------------+------------+-------+------------+---------+
| name | type              | kind   | null? | default                 | primary key | unique key | check | expression | comment |
|------+-------------------+--------+-------+-------------------------+-------------+------------+-------+------------+---------|
| C1   | NUMBER(38,0)      | COLUMN | N     | NULL                    | N           | N          | NULL  | NULL       | NULL    |
| C2   | NUMBER(38,0)      | COLUMN | Y     | 3                       | N           | N          | NULL  | NULL       | NULL    |
| C3   | NUMBER(38,0)      | COLUMN | Y     | DB1.PUBLIC.SEQ1.NEXTVAL | N           | N          | NULL  | NULL       | NULL    |
| C4   | VARCHAR(20)       | COLUMN | Y     | 'abcde'                 | N           | N          | NULL  | NULL       | NULL    |
| C5   | VARCHAR(16777216) | COLUMN | Y     | NULL                    | N           | N          | NULL  | NULL       | NULL    |
+------+-------------------+--------+-------+-------------------------+-------------+------------+-------+------------+---------+

Make the following changes to t1:

  • Change NULL column c1 to NOT NULL.

  • Drop the default for column c2 and change the default sequence for column c3.

  • Increase the length of column c4 and drop the default for the column.

  • Add a comment for column c5.

ALTER TABLE t1 ALTER COLUMN c1 DROP NOT NULL;

ALTER TABLE t1 MODIFY c2 DROP DEFAULT, c3 SET DEFAULT seq5.nextval ;

ALTER TABLE t1 ALTER c4 SET DATA TYPE VARCHAR(50), COLUMN c4 DROP DEFAULT;

ALTER TABLE t1 ALTER c5 COMMENT '50 character column';

DESC TABLE t1;

+------+-------------------+--------+-------+-------------------------+-------------+------------+-------+------------+---------------------+
| name | type              | kind   | null? | default                 | primary key | unique key | check | expression | comment             |
|------+-------------------+--------+-------+-------------------------+-------------+------------+-------+------------+---------------------|
| C1   | NUMBER(38,0)      | COLUMN | Y     | NULL                    | N           | N          | NULL  | NULL       | NULL                |
| C2   | NUMBER(38,0)      | COLUMN | Y     | NULL                    | N           | N          | NULL  | NULL       | NULL                |
| C3   | NUMBER(38,0)      | COLUMN | Y     | DB1.PUBLIC.SEQ5.NEXTVAL | N           | N          | NULL  | NULL       | NULL                |
| C4   | VARCHAR(50)       | COLUMN | Y     | NULL                    | N           | N          | NULL  | NULL       | NULL                |
| C5   | VARCHAR(16777216) | COLUMN | Y     | NULL                    | N           | N          | NULL  | NULL       | 50 character column |
+------+-------------------+--------+-------+-------------------------+-------------+------------+-------+------------+---------------------+

Same as previous example, but with the following changes to illustrate the versatility/flexibility of the command:

  • All actions executed in a single ALTER COLUMN clause.

  • The order of the columns within the clause is different.

  • SET DATA TYPE shortened to simply TYPE.

ALTER TABLE t1 ALTER (
   c1 DROP NOT NULL,
   c5 COMMENT '50 character column',
   c4 TYPE VARCHAR(50),
   c2 DROP DEFAULT,
   COLUMN c4 DROP DEFAULT,
   COLUMN c3 SET DEFAULT seq5.nextval
  );

This example produces the same results.