3.3.8 ALTER TABLE

Table of Contents Previous Next


3 The SQL Language : 3.3 SQL Commands : 3.3.8 ALTER TABLE

ALTER TABLE -- change the definition of a table
action [, ...]
RENAME COLUMN column TO new_column
RENAME TO new_name
where action is one of:
ADD column type [ column_constraint [ ... ] ]
ADD table_constraint
DROP CONSTRAINT constraint_name [ CASCADE ]
ALTER TABLE changes the definition of an existing table. There are several subforms:
ADD column type
ADD table_constraint
The 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.
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 CHECK or NOT NULL constraint requires scanning the table to verify that existing rows meet the constraint.
The 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:

3 The SQL Language : 3.3 SQL Commands : 3.3.8 ALTER TABLE

Table of Contents Previous Next