Oracle / PLSQL: ALTER TABLE Statement

This Oracle tutorial explains how to use the OracleALTER table statementto add a column,modify a column,drop a column,rename a column or rename a table (with Syntax,examples and practice exercises).

Description

The Oracle ALTER table statement is used to add,modify,or drop/delete columns in a table. The Oracle ALTER table statement is also used to rename a table.

Add column in table

Syntax

To ADD A ColUMN in a table,the Oracle ALTER table Syntax is:

ALTER table table_name
  ADD column_name column-deFinition;

Example

Let's look at an example that shows how to add a column in an Oracle table using the ALTER table statement.

For example:

ALTER table customers
  ADD customer_name varchar2(45);

This Oracle ALTER table example will add a column calledcustomer_nameto thecustomerstable.

Add multiple columns in table

Syntax

To ADD MulTIPLE ColUMNS to an existing table,246)">ALTER table table_name ADD (column_1 column-deFinition,column_2 column-deFinition,... column_n column_deFinition);

Example

Let's look at an example that shows how to add multiple columns in an Oracle table using the ALTER table statement.

For example:

ALTER table customers
  ADD (customer_name varchar2(45),city varchar2(40));

This Oracle ALTER table example will add two columns,customer_nameas a varchar2(45) fIEld andcityas a varchar2(40) fIEld to thecustomerstable.

Modify column in table

Syntax

To MODIFY A ColUMN in an existing table,246)">ALTER table table_name MODIFY column_name column_type;

Example

Let's look at an example that shows how to modify a column in an Oracle table using the ALTER table statement.

For example:

ALTER table customers
  MODIFY customer_name varchar2(100) not null;

This Oracle ALTER table example will modify the column calledcustomer_nameto be a data type of varchar2(100) and force the column to not allow null values.

Modify Multiple columns in table

Syntax

To MODIFY MulTIPLE ColUMNS in an existing table,246)">ALTER table table_name MODIFY (column_1 column_type,column_2 column_type,... column_n column_type);

Example

Let's look at an example that shows how to modify multiple columns in an Oracle table using the ALTER table statement.

For example:

ALTER table customers
  MODIFY (customer_name varchar2(100) not null,city varchar2(75));

This Oracle ALTER table example will modify both thecustomer_nameandcitycolumns.

Drop column in table

Syntax

To DROP A ColUMN in an existing table,246)">ALTER table table_name DROP ColUMN column_name;

Example

Let's look at an example that shows how to drop a column in an Oracle table using the ALTER table statement.

For example:

ALTER table customers
  DROP ColUMN customer_name;

This Oracle ALTER table example will drop the column calledcustomer_namefrom the table calledcustomers.

Rename column in table
(NEW in Oracle 9i Release 2)

Syntax

Starting in Oracle 9i Release 2,you can Now rename a column.

To REname A ColUMN in an existing table,246)">ALTER table table_name REname ColUMN old_name to new_name;

Example

Let's look at an example that shows how to rename a column in an Oracle table using the ALTER table statement.

For example:

ALTER table customers
  REname ColUMN customer_name to cname;

This Oracle ALTER table example will rename the column calledcustomer_nametocname.

Rename table

Syntax

To REname A table,246)">ALTER table table_name REname TO new_table_name;

Example

Let's look at an example that shows how to rename a table in Oracle using the ALTER table statement.

For example:

ALTER table customers
  REname TO contacts;

This Oracle ALTER table example will rename thecustomerstable tocontacts.

Practice Exercise #1:

Based on thedepartmentstable below,rename thedepartmentstable todepts.

CREATE table departments
( department_ID number(10) not null,department_name varchar2(50) not null,CONSTRAINT departments_pk PRIMARY KEY (department_ID)
);

Solution for Practice Exercise #1:

The following Oracle ALTER table statement would rename thedepartmentstable todepts:

ALTER table departments
  REname TO depts;

Practice Exercise #2:

Based on theemployeestable below,add a column calledbonusthat is a number(6) datatype.

CREATE table employees
( employee_number number(10) not null,employee_name varchar2(50) not null,department_ID number(10),CONSTRAINT employees_pk PRIMARY KEY (employee_number)
);

Solution for Practice Exercise #2:

The following Oracle ALTER table statement would add abonuscolumn to theemployeestable:

ALTER table employees
  ADD bonus number(6);

Practice Exercise #3:

Based on thecustomerstable below,add two columns - one column calledcontact_namethat is a varchar2(50) datatype and one column calledlast_contactedthat is a date datatype.

CREATE table customers
( customer_ID number(10) not null,customer_name varchar2(50) not null,address varchar2(50),city varchar2(50),state varchar2(25),zip_code varchar2(10),CONSTRAINT customers_pk PRIMARY KEY (customer_ID)
);

Solution for Practice Exercise #3:

The following Oracle ALTER table statement would add thecontact_nameandlast_contactedcolumns to thecustomerstable:

ALTER table customers
  ADD (contact_name varchar2(50),last_contacted date);

Practice Exercise #4:

Based on theemployeestable below,change theemployee_namecolumn to a varchar2(75) datatype.

 Solution for Practice Exercise #4: 
 
The following Oracle ALTER table statement would change the datatype for theemployee_namecolumn to varchar2(75):
 
 
ALTER table employees
  MODIFY employee_name varchar2(75);
 
 
 Practice Exercise #5:
 
 
Based on thecustomerstable below,change thecustomer_namecolumn to NOT allow null values and change thestatecolumn to a varchar2(2) datatype.
 
 
 Solution for Practice Exercise #5: 
 
The following Oracle ALTER table statement would modify thecustomer_nameandstatecolumns accordingly in thecustomerstable:
 
 
ALTER table customers
  MODIFY (customer_name varchar2(50) not null,state varchar2(2));
 
 
 Practice Exercise #6:
 
 
Based on theemployeestable below,drop thesalarycolumn.
 
 
 Solution for Practice Exercise #6: 
 
The following Oracle ALTER table statement would drop thesalarycolumn from theemployeestable:
 
 
ALTER table employees
  DROP ColUMN salary;
 
 
 Practice Exercise #7:
 
 
Based on thedepartmentstable below,rename thedepartment_namecolumn todept_name.
 
 
 Solution for Practice Exercise #7: 
 
The following Oracle ALTER table statement would rename thedepartment_namecolumn todept_namein thedepartmentstable:
 
 
ALTER table departments
  REname ColUMN department_name to dept_name;
 
 
from https://www.techonthenet.com/oracle/index.PHP