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
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 column data type to a synonymous type (e.g. STRING to VARCHAR).
 
Change column data type to a different type (e.g. STRING to NUMBER).
 
Increase the length of a text column (e.g. VARCHAR(50) to VARCHAR(100)).
 
Decrease the length of a text column (e.g. VARCHAR(50) to VARCHAR(25)).
 
Change the length (precision or scale) of a numeric column (e.g. NUMBER(5,0) to NUMBER(10,0)).
 
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 a text data type (VARCHAR, STRING, TEXT, etc.).

    Also, TYPE can be used only to increase the length of the specified text column.

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.