Category Archives: Alter Table

ALTER TABLE Option in Oracle.


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;