ALTER TABLE v10
ALTER TABLE -- change the definition of a table.
action is one of:
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
This form drops a column from a table. Indexes and table constraints involving the column will be automatically dropped as well.
This form adds a new constraint to a table; for details, see CREATE TABLE.
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 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
The name (possibly schema-qualified) of an existing table to alter.
Name of a new or existing column.
New name for an existing column.
New name for the table.
Data type of the new column.
New table constraint for the table.
Name of an existing constraint to drop.
Automatically drop objects that depend on the dropped constraint.
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
NOT NULL constraint requires scanning the table to verify that existing rows meet the constraint.
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.
To add a column of type
VARCHAR2 to a table:
To drop a column from a table:
To rename an existing column:
To rename an existing table:
To add a check constraint to a table:
To remove a check constraint from a table: