ALTER TABLE

Specifications of 'ALTER TABLE' are as follows

Adding Foreign key

ALTER TABLE table_name
ADD CONSTRAINT constraint_name FOREIGN KEY (col1, col2)
REFERENCES table_2 (cola,colb);

Adding unique constraint

The following example adds a unique constraint on the column column_name in the table whose name is table_name. The name of the constraint is constraint_name.

ALTER TABLE table_name
add constraint constraint_name
unique (column_name)

Disabling constraints

Integrity constraints can be disabled with the ALTER TABLE command.

ALTER TABLE table-name disable constraint-specification;
ALTER TABLE table-name disable constraint constraint-name;

Adding new Column

ALTER TABLE foo_table add bar_column char(1);
ALTER TABLE foo_table add (bar_column1 char(1),bar_column2 char(1));

Modifying a column

Renaming a column name

ALTER TABLE some_table rename column column_name to new_column_name;

Changing a column's type

A column's type can be changed if the column is empty within the entire table, otherwise Oracle gives an ORA-01440: column to be modified must be empty to decrease precision or scale.

ALTER TABLE some_table modify (column_name number);

Changing null to not null or vice versa

ALTER TABLE some_table modify (column_name not null);

ALTER TABLE some_table modify col_not_null number null;

Specifying tablespace for index

ALTER TABLE tbl add constraint pk_tbl
primary key (col_1, col_2)
using index tablespace ts_idx

Removing a constraint

ALTER TABLE table_name drop constraint constraint_name;