ALTER TABLE v13

Name

ALTER TABLE -- change the definition of a table.

Synopsis

ALTER TABLE <name>
  action [, ...]
ALTER TABLE <name>
  RENAME COLUMN <column> TO <new_column>
ALTER TABLE <name>
  RENAME TO <new_name>
ALTER TABLE <name>
  { NOPARALLEL | PARALLEL [ <integer> ] }

where action is one of:

ADD <column type> [ <column_constraint> [ ... ] ]
DROP COLUMN <column>
ADD <table_constraint>
DROP CONSTRAINT <constraint_name> [ CASCADE ]

Description

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 CREATE TABLE.

• DROP COLUMN

  This form drops a column from a table. Indexes and table constraints involving the column will be automatically dropped as well.

• ADD table_constraint

  This form adds a new constraint to a table; for details, see CREATE TABLE.

• DROP CONSTRAINT

  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

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.

The PARALLEL clause sets the degree of parallelism for a table. The NOPARALLEL clause resets the values to their defaults; reloptions will show the parallel_workers parameter as 0.

A superuser has permission to create a trigger on any user's table but a user can create a trigger only on the table they own. However, when the ownership of a table is changed, the ownership of the trigger's implicit objects is updated when they are matched with a table owner owning a trigger.

You can use ALTER TRIGGER ...ON AUTHORIZATION command to alter a trigger's implicit object owner, for information, see ALTER TRIGGER.

You must own the table to use ALTER TABLE.

Parameters

name

The name (possibly schema-qualified) of an existing table to alter.

column

Name of a new or existing column.

new_column

New name for an existing column.

new_name

New name for the table.

type

Data type of the new column.

table_constraint

New table constraint for the table.

constraint_name

Name of an existing constraint to drop.

CASCADE

Automatically drop objects that depend on the dropped constraint.

PARALLEL

Specify PARALLEL to select a degree of parallelism; you can also specify the degree of parallelism by setting the parallel_workers parameter when performing a parallel scan on a table. If you specify PARALLEL without including a degree of parallelism, the index will use default parallelism.

NOPARALLEL

Specify NOPARALLEL to reset parallelism to default values.

integer

The integer indicates the degree of parallelism, which is the number of parallel_workers used in the parallel operation to perform a parallel scan on a table.

Notes

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.

Examples

To add a column of type VARCHAR2 to a table:

ALTER TABLE emp ADD address VARCHAR2(30);

To drop a column from a table:

ALTER TABLE emp DROP COLUMN address;

To rename an existing column:

ALTER TABLE emp RENAME COLUMN address TO city;

To rename an existing table:

ALTER TABLE emp RENAME TO employee;

To add a check constraint to a table:

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

To remove a check constraint from a table:

ALTER TABLE emp DROP CONSTRAINT sal_chk;

To reset the degree of parallelism to 0 on the emp table:

ALTER TABLE emp NOPARALLEL;

The following example creates a table named dept, and then alters the dept table to define and enable a unique key on the dname column. The constraint dept_dname_uq identifies the dname column as a unique key. The USING_INDEX clause creates an index on a table dept with the index statement specified to enable the unique constraint.

CREATE TABLE dept (
   deptno          NUMBER(2),
   dname           VARCHAR2(14),
   loc             VARCHAR2(13)
);

ALTER TABLE dept
  ADD CONSTRAINT dept_dname_uq UNIQUE(dname)
     USING INDEX (CREATE UNIQUE INDEX idx_dept_dname_uq ON dept (dname));

The following example creates a table named emp, and then alters the emp table to define and enable a primary key on the ename column. The emp_ename_pk constraint identifies the column ename as a primary key of the emp table. The USING_INDEX clause creates an index on a table emp with the index statement specified to enable the primary constraint.

CREATE TABLE emp (
   empno           NUMBER(4) NOT NULL,
   ename           VARCHAR2(10),
   job             VARCHAR2(9),
   sal             NUMBER(7,2),
   deptno          NUMBER(2)
);

ALTER TABLE emp
  ADD CONSTRAINT emp_ename_pk PRIMARY KEY (ename)
     USING INDEX (CREATE INDEX idx_emp_ename_pk ON emp (ename));

See Also

CREATE TABLE, DROP TABLE