Changes the definition of a table.

Syntax

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

The action clause has the following types of syntax:

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

Description

You can use the ALTER TABLE command to change the definition of a table. This command has the following clauses:

  • ADD column type: adds a new column to the table by using the same syntax as the CREATE TABLE command.
  • DROP COLUMN: deletes a column from the table. Indexes and table constraints that involve the column are automatically deleted.
  • ADD table_constraint: adds a new constraint to the table by using the same syntax as the CREATE TABLE command.
  • DROP CONSTRAINT: deletes the constraints of the table. Table constraints do not need unique names and a specified name can match multiple constraints. All matched constraints are deleted.
  • RENAME: changes the name of a table or an individual column in the table. You can also use this type of syntax to change the name of an index, sequence, or view. The stored data is not affected.

Only the owner of a table can use the ALTER TABLE command.

Note When you use the ADD COLUMN clause, all rows in the table are initialized with the default value of the column. If no DEFAULT clause is specified, the value is null. To add a column with non-null default values, you must rewrite the table. Rewriting a large table is time-consuming and requires twice the disk space. To add a CHECK or NOT NULL constraint, you must scan the table to verify that existing rows meet the constraint.

The DROP COLUMN clause does not physically remove the column, but makes columns invisible to SQL operations. Subsequent insert and update operations in the table store null values for the column. Therefore, deleting a column is fast, but does not immediately reduce the disk space that is occupied by the table because the space that is occupied by the deleted column is not reclaimed. The space is reclaimed after existing rows are updated.

You are not allowed to modify any portion of the system directory table. For more information about valid parameters, see the CREATE TABLE topic.

Parameters

Parameter Description
name The name of the table to be modified. The name can be schema-qualified.
column The name of the new or existing column.
new_column The new name of the existing column.
new_name The new name of the table.
type The data type of the new column.
table_constraint The new constraint of the table.
constraint_name The name of the existing constraint to be deleted.
CASCADE If you specify the CASCADE parameter, the objects that depend on the deleted constraints are automatically deleted.

Examples

Add a column of the VARCHAR2 data type to a table:

ALTER TABLE emp ADD address VARCHAR2(30);

Delete a column from a table:

ALTER TABLE emp DROP COLUMN address;

Rename an existing column:

ALTER TABLE emp RENAME COLUMN address TO city;

Rename an existing table:

ALTER TABLE emp RENAME TO employee;

Add a CHECK constraint to a table:

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

Delete a CHECK constraint from a table:

ALTER TABLE emp DROP CONSTRAINT sal_chk;