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@H_301_19@
To ADD A COLUMN in a table,the Oracle ALTER TABLE Syntax is:
ALTER TABLE table_name ADD column_name column-definition;
Example@H_301_19@
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 called@H_404_33@customer_nameto the@H_404_33@customerstable.
Syntax@H_301_19@
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@H_301_19@
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,@H_404_33@customer_nameas a varchar2(45) field and@H_404_33@cityas a varchar2(40) field to the@H_404_33@customerstable.
Syntax@H_301_19@
To MODIFY A COLUMN in an existing table,246)">ALTER TABLE table_name MODIFY column_name column_type;
Example@H_301_19@
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 called@H_404_33@customer_nameto be a data type of varchar2(100) and force the column to not allow null values.
Syntax@H_301_19@
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@H_301_19@
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 the@H_404_33@customer_nameand@H_404_33@citycolumns.
Syntax@H_301_19@
To DROP A COLUMN in an existing table,246)">ALTER TABLE table_name DROP COLUMN column_name;
Example@H_301_19@
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 called@H_404_33@customer_namefrom the table called@H_404_33@customers.
(NEW in Oracle 9i Release 2)
Syntax@H_301_19@
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@H_301_19@
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 called@H_404_33@customer_nameto@H_404_33@cname.
Syntax@H_301_19@
To RENAME A TABLE,246)">ALTER TABLE table_name RENAME TO new_table_name;
Example@H_301_19@
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 the@H_404_33@customerstable to@H_404_33@contacts.
Based on the@H_404_33@departmentstable below,rename the@H_404_33@departmentstable to@H_404_33@depts.
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:@H_301_19@
The following Oracle ALTER TABLE statement would rename the@H_404_33@departmentstable to@H_404_33@depts:
ALTER TABLE departments RENAME TO depts;
Practice Exercise #2:
Based on the@H_404_33@employeestable below,add a column called@H_404_33@bonusthat 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:@H_301_19@
The following Oracle ALTER TABLE statement would add a@H_404_33@bonuscolumn to the@H_404_33@employeestable:
ALTER TABLE employees ADD bonus number(6);
Practice Exercise #3:
Based on the@H_404_33@customerstable below,add two columns - one column called@H_404_33@contact_namethat is a varchar2(50) datatype and one column called@H_404_33@last_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:@H_301_19@
The following Oracle ALTER TABLE statement would add the@H_404_33@contact_nameand@H_404_33@last_contactedcolumns to the@H_404_33@customerstable:
ALTER TABLE customers ADD (contact_name varchar2(50),last_contacted date);
Practice Exercise #4:
Based on the@H_404_33@employeestable below,change the@H_404_33@employee_namecolumn to a varchar2(75) datatype.
Solution for Practice Exercise #4:@H_301_19@The following Oracle ALTER TABLE statement would change the datatype for the@H_404_33@employee_namecolumn to varchar2(75):
ALTER TABLE employees MODIFY employee_name varchar2(75);Practice Exercise #5:
Based on the@H_404_33@customerstable below,change the@H_404_33@customer_namecolumn to NOT allow null values and change the@H_404_33@statecolumn to a varchar2(2) datatype.
Solution for Practice Exercise #5:@H_301_19@The following Oracle ALTER TABLE statement would modify the@H_404_33@customer_nameand@H_404_33@statecolumns accordingly in the@H_404_33@customerstable:
ALTER TABLE customers MODIFY (customer_name varchar2(50) not null,state varchar2(2));Practice Exercise #6:
Based on the@H_404_33@employeestable below,drop the@H_404_33@salarycolumn.
Solution for Practice Exercise #6:@H_301_19@The following Oracle ALTER TABLE statement would drop the@H_404_33@salarycolumn from the@H_404_33@employeestable:
ALTER TABLE employees DROP COLUMN salary;Practice Exercise #7:
Based on the@H_404_33@departmentstable below,rename the@H_404_33@department_namecolumn to@H_404_33@dept_name.
Solution for Practice Exercise #7:@H_301_19@The following Oracle ALTER TABLE statement would rename the@H_404_33@department_namecolumn to@H_404_33@dept_namein the@H_404_33@departmentstable:
ALTER TABLE departments RENAME COLUMN department_name to dept_name;from https://www.techonthenet.com/oracle/index.@R_301_461@
