ALTER TABLE v17

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> ] }

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 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 are dropped as well.

• ADD table_constraint

  This form adds a constraint to a table. For details, see CREATE TABLE.

• DROP CONSTRAINT

  This form drops constraints on a table. Currently, constraints on tables don't need unique names, so there might be more than one constraint matching the specified name. All matching constraints are dropped.

RENAME

The RENAME forms change the name of a table (or an index, sequence, or view) or the name of a 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 shows 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're matched with a table owner owning a trigger.

You can use the 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 uses 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 requires rewriting the entire table. This can take a long time for a large table, and it temporarily requires 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 doesn't physically remove the column but makes it invisible to SQL operations. Subsequent insert and update operations in the table store a null value for the column. Thus, dropping a column is quick, but it doesn't immediately reduce the on-disk size of your table since the space occupied by the dropped column isn't reclaimed. The space is reclaimed over time as existing rows are updated.

Changing any part of a system catalog table isn't 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;

This 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));

This 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