Changes the definition of a table.
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 ]
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.
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.
|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.|
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;