ALTER TABLE v11

Suggest edits

Name

ALTER TABLE -- change the definition of a table.

Synopsis

ALTER TABLE <name>
  action [, ...]
ALTER TABLE <name>
  RENAME COLUMN <column> TO <new_column>
ALTER TABLE <name>
  RENAME TO <new_name>

where action is one of:

ADD <column type> [ <column_constraint> [ ... ] ]
DROP COLUMN <column>
ADD <table_constraint>
DROP CONSTRAINT <constraint_name> [ CASCADE ]

Description

ALTER TABLE changes the definition of an existing table. There are several subforms:

  • ADD column type

    This form adds a new column to the table using the same syntax as CREATE TABLE.

  • DROP COLUMN

    This form drops a column from a table. Indexes and table constraints involving the column will be automatically dropped as well.

  • ADD table_constraint

    This form adds a new constraint to a table; for details, see CREATE TABLE.

  • DROP CONSTRAINT

    This form drops constraints on a table. Currently, constraints on tables are not required to have unique names, so there may be more than one constraint matching the specified name. All matching constraints will be dropped.

RENAME

The RENAME forms change the name of a table (or an index, sequence, or view) or the name of an individual column in a table. There is no effect on the stored data.

You must own the table to use ALTER TABLE.

Parameters

name

The name (possibly schema-qualified) of an existing table to alter.

column

Name of a new or existing column.

new_column

New name for an existing column.

new_name

New name for the table.

type

Data type of the new column.

table_constraint

New table constraint for the table.

constraint_name

Name of an existing constraint to drop.

CASCADE

Automatically drop objects that depend on the dropped constraint.

Notes

When you invoke ADD COLUMN, all existing rows in the table are initialized with the column’s default value (null if no DEFAULT clause is specified). Adding a column with a non-null default will require the entire table to be rewritten. This may take a significant amount of time for a large table; and it will temporarily require double the disk space. Adding a CHECK or NOT NULL constraint requires scanning the table to verify that existing rows meet the constraint.

The DROP COLUMN form does not physically remove the column, but simply makes it invisible to SQL operations. Subsequent insert and update operations in the table will store a null value for the column. Thus, dropping a column is quick but it will not immediately reduce the on-disk size of your table, as the space occupied by the dropped column is not reclaimed. The space will be reclaimed over time as existing rows are updated.

Changing any part of a system catalog table is not permitted. Refer to CREATE TABLE for a further description of valid parameters.

Examples

To add a column of type VARCHAR2 to a table:

ALTER TABLE emp ADD address VARCHAR2(30);

To drop a column from a table:

ALTER TABLE emp DROP COLUMN address;

To rename an existing column:

ALTER TABLE emp RENAME COLUMN address TO city;

To rename an existing table:

ALTER TABLE emp RENAME TO employee;

To add a check constraint to a table:

ALTER TABLE emp ADD CONSTRAINT sal_chk CHECK (sal > 500);

To remove a check constraint from a table:

ALTER TABLE emp DROP CONSTRAINT sal_chk;

See Also

CREATE TABLE, DROP TABLE

← Prev

ALTER SYNONYM

Next →

ALTER TABLESPACE