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.

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.

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.

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.

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.

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.

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.

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.

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
                        
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!