Oracle ALTER TABLE operator

The Oracle ALTER TABLE statement is used to add a column, change a column, delete a column, rename a column or rename a table (with syntax, examples and practical exercises).

The Oracle/PLSQL ALTER TABLE statement is used to add, modify or delete a column in a table. The Oracle/PLSQL ALTER TABLE operator is also used to rename tables.

Add a column to a table (ALTER TABLE)

ALTER TABLE syntax for adding a column to an Oracle table:

ALTER TABLE table_name
ADD column_name column-definition;

Let’s take an example of how to add a column to an Oracle table using the ALTER TABLE operator.
For example:

ALTER TABLE customers
ADD customer_name varchar2(45);

In this example, the ALTER TABLE operator will add a customer_name column to the customers table.

Adding multiple columns to the table

ALTER TABLE syntax to add multiple columns to an existing Oracle table:

ALTER TABLE table_name
ADD (column_1 column-definition,
column_2 column-definition,
…
column_n column_definition);

Consider an example that shows how to add multiple columns to an Oracle table using the ALTER TABLE operator. For example:

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

In this example, ALTER TABLE will add two columns, customer_name varchar2 (45) and city varchar2 (40).

Change the column in the table

ALTER TABLE syntax for changing a column in an existing Oracle table:

ALTER TABLE table_name
MODIFY column_name column_type;

Consider an example that shows how to change a column in an Oracle table using the ALTER TABLE operator.
For example:

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

In this example, ALTER TABLE will modify the customer_name column to VARCHAR2(100) data type and indicate that the column does not accept NULL values.

Modifying several columns in a table

ALTER TABLE syntax for editing multiple columns in an existing Oracle table:

ALTER TABLE table_name
MODIFY (column_1 column_type,
column_2 column_type,
…
column_n column_type);

Consider an example that shows how to change multiple columns in an Oracle table using the ALTER TABLE operator. For example:

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

In this example, ALTER TABLE modifies the customer_name and city columns.

Removing a column from a table

ALTER TABLE syntax to delete a column in an existing Oracle table:

ALTER TABLE table_name
DROP COLUMN column_name;

Consider an example that shows how to remove a column from an Oracle table using the ALTER TABLE operator. For example:

ALTER TABLE customers
DROP COLUMN customer_name;

In this example, Oracle/PLSQL ALTER TABLE will remove the customer_name column from the customers table.

Rename the column in the table

As of Oracle 9i Release 2, you can rename the column. ALTER TABLE syntax to rename a column in an existing Oracle table:

ALTER TABLE table_name
RENAME COLUMN old_name to new_name;

Consider an example that shows how to rename a column in an Oracle table using the ALTER TABLE operator. For example:

ALTER TABLE customers
RENAME COLUMN customer_name to cname;

In this example, Oracle/PLSQL ALTER TABLE renames the column customer_name to cname.

Rename the table

ALTER TABLE syntax for renaming the Oracle table:

ALTER TABLE table_name
RENAME TO new_table_name;

Consider an example that shows how to rename a table in Oracle using the ALTER TABLE operator. For example:

ALTER TABLE customers
RENAME TO contacts;

In this example, Oracle/PLSQL ALTER TABLE renames the customer table to contacts.

Practical Exercise #1

Based on the departments table below, rename the departments table as depts.

CREATE TABLE
( department_id number(10) not null,
Department_name varchar2(50) not null,
CONSTRAINT departments_pk PRIMARY KEY (department_id)
);

The solution for practical exercise #1:

The next Oracle/PLSQL ALTER TABLE operator will rename the table as depts:

ALTER TABLE
RENAME TO depts;

Practical Exercise #2

Based on the table below, add a column with the bonus name and data type number (6).

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)
);

The solution for practical exercise #2:

The next Oracle/PLSQL ALTER TABLE operator will add a bonus column to the employees table:

ALTER TABLE employees
ADD bonus number(6);

Practical Exercise #3

Based on the table below, add two columns. One column with the name contact_name and data type VARCHAR2 (50), the second column with the name last_contacted and data type DATE.

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)
);

The solution for practical exercise #3:

The next Oracle/PLSQL ALTER TABLE operator will add the contact_name and last_contacted columns to the customers table:

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

Practical Exercise #4

Based on the employees table below, modify the employee_name column data type to varchar2 (80).

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)
);

The solution for practical exercise #4:

The next Oracle/PLSQL ALTER TABLE operator modifies the data type for the employee_name column in varchar2 (80):

ALTER TABLE employees
MODIFY employee_name varchar2(80);

Practical Exercise #5

Based on the table below, modify the customer_name column so that it cannot take NULL values and change the data type in the state column to varchar2 (2).

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

The solution for practical exercise #5:

The following Oracle/PLSQL ALTER TABLE operator modifies the customer_name and state columns in the customers table:

ALTER TABLE customers
MODIFY (customer_name varchar2(50) not null,
state varchar2(2));

Practical Exercise #6

Based on the table below, remove the column.

CREATE TABLE employees
( employee_number number(10) not null,
employee_name varchar2(50) not null,
department_id number(10),
salary number(6),
CONSTRAINT employees_pk PRIMARY KEY (employee_number)
);

The solution for practical exercise #6:

The next Oracle/PLSQL ALTER TABLE operator will remove the salary column from the employees table:

ALTER TABLE employees
DROP COLUMN salary;

Practical Exercise #7

Based on the table below, rename the column department_name to dept_name.

CREATE TABLE
( department_id number(10) not null,
Department_name varchar2(50) not null,
CONSTRAINT departments_pk PRIMARY KEY (department_id)
);

The solution for practical exercise #7

The next Oracle/PLSQL ALTER TABLE operator will rename the department_name column to dept_name in the departments table:

ALTER TABLE
RENAME COLUMN department_name to dept_name;

Oracle – SQL – Alter Table