ALTER TABLE

Syntax

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

action can be one of the following clauses:

ADD column type [ column_constraint [ ... ] ]
DROP COLUMN column
ADD table_constraint
DROP CONSTRAINT constraint_name [ CASCADE ]
ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]
ALTER [ COLUMN ] column_name SET DEFAULT expression
ALTER [ COLUMN ] column_name DROP DEFAULT
ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL
DISABLE TRIGGER [ trigger_name | ALL | USER ]
OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
REPLICA IDENTITY { DEFAULT | USING INDEX index_name | FULL | NOTHING }

Description

ALTER TABLE modifies the definition of a table. Run ALTER TABLE only on tables you own. System catalog tables cannot be modified.

If a simple view is defined on a base table, you do not need to drop the view before running ALTER TABLE ... ALTER COLUMN to change a column's data type.

The following clauses are supported:

• `ADD column type`: Adds a new column to the table, using the same syntax as CREATE TABLE. Existing rows are filled with the column's default value. If no DEFAULT is specified, existing rows receive NULL.

  • If the default is volatile (for example, now()), the entire table is rewritten. For large tables, this can take significant time and doubles disk usage.

  • If the default is non-volatile, the table is not rewritten.

  • Adding a CHECK or NOT NULL constraint requires a full table scan to validate existing rows.

• `DROP COLUMN`: Makes a column invisible to SQL operations. Subsequent inserts and updates store NULL for that column. The column's disk space is not immediately reclaimed — it is freed as existing rows are updated.

• `ADD table_constraint`: Adds a new constraint to the table, using the same syntax as CREATE TABLE.

• `DROP CONSTRAINT`: Removes a constraint by name. If the name matches multiple constraints, all matching constraints are removed. Use CASCADE to automatically drop objects that depend on the removed constraint.

• `RENAME`: Renames the table, a column, an index, a sequence, or a view. The rename has no effect on stored data.

Parameters

For full parameter details, see CREATE TABLE.

Examples

Add a column with a data type:

ALTER TABLE emp ADD address VARCHAR2(30);

Existing rows receive NULL for the new address column, because no default value is specified.

Drop a column:

ALTER TABLE emp DROP COLUMN address;

The column becomes invisible to SQL operations immediately. Disk space is reclaimed as existing rows are updated.

Rename a column:

ALTER TABLE emp RENAME COLUMN address TO city;

Rename a table:

ALTER TABLE emp RENAME TO employee;

Add a CHECK constraint:

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

Drop a CHECK constraint:

ALTER TABLE emp DROP CONSTRAINT sal_chk;