SQL ALTER Statement

---

In Oracle ALTER TABLE statement to add, modify, rename,drop a column and ALTER TABLE statement also use for rename a table.

ADD Column In a Table: ALTER  TABLE statement is use to add columns in a existing table. We can add single or multiple column.

Syntax: Add single column

ALTER TABLE table_name
ADD column_name column_definition;

Add multiple columns

ALTER TABLE table_name
  ADD (column_name1 column_definition,
       column_name2 column_definition,
       ...
       column_nameN column_definition);

Example:

ALTER TABLE STUDENTS
ADD PHONE_NUMBER number(15);

ALTER TABLE example will add a column to the STUDENTS table column name called PHONE_NUMBER.

MODIFY Column In a Table: ALTER  TABLE statement is use to modify columns in a existing table. We can modify single or multiple columns.

Syntax: Modify single column

ALTER TABLE table_name
MODIFY column_name column_type; 

Modify multiple columns

ALTER TABLE table_name
  MODIFY (column_name1 column_type,
          column_name2 column_type,
          ...
          column_nameN column_type);

Example:

ALTER TABLE STUDENTS
MODIFY (PHONE_NUMBER VARCHAR2(15));

ALTER TABLE example will MODIFY a column to the STUDENTS table column name called PHONE_NUMBER from NUMBER to VARCHAR2.

RENAME Column In a Table: ALTER  TABLE statement is use to rename column in a existing table. 

Syntax: Rename single column

ALTER TABLE table_name
RENAME COLUMN old_column_name new_column_name;

Example:

ALTER TABLE STUDENTS
RENAME COLUMN PHONE_NUMBER PH_NO;

ALTER TABLE example will RENAME a column to the STUDENTS table old column name called PHONE_NUMBER and new column PH_NO.



DROP Column In a Table: ALTER  TABLE statement is use to drop column in a existing table. 

Syntax: DROP  column

ALTER TABLE table_name
DROP COLUMN column_name;

Example:

ALTER TABLE STUDENTS
DROP COLUMN PH_NO;

ALTER TABLE example will DROP a column to the STUDENTS table  column name called PH_NO.


RENAME Table: ALTER  TABLE statement is use to rename TABLE existing table. 

Syntax: 

ALTER TABLE table_name
RENAME TO new_table_name;

Example:

ALTER TABLE STUDENTS
RENAME TO STUDENT;

ALTER TABLE example will RENAME table name  STUDENTS to STUDENT.

NB:
If a view, trigger, check constraint, foreign key constraint,  an attempt to rename it will generate an error.


The RENAME COLUMN statement is not allowed if there are any open cursors that reference the column that is being altered.