ALTER TABLE Option in Oracle.

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;

 

 

 

Advertisements