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
In the preceding syntax, action
can be set to 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
You can execute the ALTER TABLE statement to redefine an existing table. The ALTER TABLE statement supports the following clauses:
ADD column type
: adds a column to the table. The syntax to use this clause in the ALTER TABLE statement is the same as the syntax to use this clause in the CREATE TABLE statement.DROP COLUMN
: deletes a column from the table. Indexes and table constraints that involve the column are also deleted.ADD table_constraint
: adds a constraint to the table. The syntax to use this clause in the ALTER TABLE statement is the same as the syntax to use this clause in the CREATE TABLE statement.DROP CONSTRAINT
: deletes a constraint from the table. Different constraints can have the same name. If the name that you specify in the clause matches multiple constraints, all constraints that match the specified constraint name are deleted.RENAME
: renames the table or a column in the table. You can also use this clause to rename an index, a sequence, or a view. The rename operation has no impact on stored data.
You can execute the ALTER TABLE statement only on tables that you own.
When you use the DROP COLUMN clause to delete a column, the column is not physically deleted. Instead, the column becomes invisible to SQL operations. During subsequent insert and update operations on the table, the cells of the column are filled with null values. After you execute the DROP COLUMN statement, the column is immediately deleted. However, the disk space that is occupied by the table remains unchanged because the disk space occupied by the deleted column is not reclaimed until the existing rows are updated.
You cannot modify the system directory table. For more information about valid parameters, see the CREATE TABLE topic.
Parameters
Parameter | Description |
---|---|
name | The name of the table that you want to modify. The name can be schema-qualified. |
column | The name of the column that you want to create or the name of the existing column that you want to rename. |
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 for the table. |
constraint_name | The name of the constraint that you want to delete. |
CASCADE | Specifies that the objects which 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 a column.
ALTER TABLE emp RENAME COLUMN address TO city;
Rename a 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;