Using ALTER TABLE Statement in Oracle

This article is written about how to use the Oracle ALTER TABLE statement to add a column, alter 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 declaration is additionally 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 seem to be at an example that suggests how to add a column in an Oracle table the usage of the ALTER TABLE statement.

For example:

ALTER TABLE customers
  ADD customer_name varchar2(45);

This Oracle ALTER TABLE instance will add a column referred to as customer_name to the clients desk that is a facts kind of varchar2(45).

In a extra difficult example, you could use the ALTER TABLE declaration to add a new column that also has a default value:

ALTER TABLE customers
  ADD city varchar2(40) DEFAULT 'Seattle';

In this example, the column referred to as town has been added to the customers table with a data type of varchar2(40) and a default cost of ‘Seattle’.

Add multiple columns in table

Syntax

To ADD MULTIPLE COLUMNS to an existing table, the Oracle ALTER TABLE syntax is:

ALTER TABLE table_name
  ADD (column_1 column_definition,
       column_2 column_definition,
       ...
       column_n column_definition);

Example

Let’s seem at an example that shows how to add more than one columns in an Oracle table using the ALTER TABLE statement.

For example:

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

This Oracle ALTER TABLE example will add two columns, customer_name as a varchar2(45) subject and city as a varchar2(40) area with a default value of ‘Seattle’ to the clients table.

Modify column in table

Syntax

To MODIFY A COLUMN in an current table, the Oracle ALTER TABLE syntax is:

ALTER TABLE table_name
  MODIFY column_name column_type;

Example

Let’s look at an instance that shows how to modify a column in an Oracle desk the usage of the ALTER TABLE statement.

For example:

ALTER TABLE customers
  MODIFY customer_name varchar2(100) NOT NULL;

This Oracle ALTER TABLE instance will alter the column called customer_name to be a information type of varchar2(100) and force the column to not allow null values.

In a more difficult example, you should use the ALTER TABLE announcement to add a default price as nicely as modify the column definition:

ALTER TABLE customers
  MODIFY city varchar2(75) DEFAULT 'Seattle' NOT NULL;

In this example, the ALTER TABLE announcement would adjust the column known as metropolis to be a facts type of varchar2(75), the default fee would be set to ‘Seattle’ and the column would be set to not permit null values.

Modify Multiple columns in table

Syntax

To MODIFY MULTIPLE COLUMNS in an current table, the Oracle ALTER TABLE syntax is:

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

Example

Let’s seem to be at an instance that indicates how to regulate more than one columns in an Oracle table the use of the ALTER TABLE statement.

For example:

ALTER TABLE customers
  MODIFY (customer_name varchar2(100) NOT NULL,
          city varchar2(75) DEFAULT 'Seattle' NOT NULL);

This Oracle ALTER TABLE instance will adjust each the customer_name and town columns. The customer_name column will be set to a varchar2(100) facts type and not permit null values. The city column will be set to a varchar2(75) facts type, its default price will be set to ‘Seattle’, and the column will not enable null values.

Drop column in table

Syntax

To DROP A COLUMN in an current table, the Oracle ALTER TABLE syntax is:

ALTER TABLE table_name
  DROP COLUMN column_name;

Example

Let’s appear at an example that shows how to drop a column in an Oracle desk the usage of the ALTER TABLE statement.

For example:

ALTER TABLE customers
  DROP COLUMN customer_name;

This Oracle ALTER TABLE instance will drop the column known as customer_name from the desk referred to as customers.

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 current table, the Oracle ALTER TABLE syntax is:

ALTER TABLE table_name
  RENAME COLUMN old_name TO new_name;

Example

Let’s look at an instance that suggests 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 instance will rename the column called customer_name to cname.

Rename table

Syntax

To RENAME A TABLE, the Oracle ALTER TABLE syntax is:

ALTER TABLE table_name
  RENAME TO new_table_name;

Example

Let’s appear at an example that suggests how to rename a desk in Oracle the use of the ALTER TABLE statement.

For example:

ALTER TABLE customers
  RENAME TO contacts;

This Oracle ALTER TABLE example will rename the clients desk to contacts.

Practice Exercise #1:

Based on the departments table below, rename the departments table to 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:

The following Oracle ALTER TABLE announcement would rename the departments desk to depts:

ALTER TABLE departments
  RENAME TO depts;

Practice Exercise #2:

Based on the personnel desk below, add a column referred to as bonus that 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 assertion would add a bonus column to the employees table:

ALTER TABLE employees
  ADD bonus number(6);

Practice Exercise #3:

Based on the customers table below, add two columns – one column known as contact_name that is a varchar2(50) datatype and one column referred to as last_contacted that 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 assertion would add the contact_name and last_contacted columns to the customers table:

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

Practice Exercise #4:

Based on the employees desk below, change the employee_name column to a varchar2(75) 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 #4:

The following Oracle ALTER TABLE announcement would change the datatype for the employee_name column to varchar2(75):

ALTER TABLE employees
  MODIFY employee_name varchar2(75);

Practice Exercise #5:

Based on the customers table below, change the customer_name column to NOT allow null values and change the country column to a varchar2(2) datatype.

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

Solution for Practice Exercise #5:

The following Oracle ALTER TABLE statement would modify the customer_name and state columns as a result in the clients table:

ALTER TABLE customers
  MODIFY (customer_name varchar2(50) NOT NULL,
          state varchar2(2));

Practice Exercise #6:

Based on the employees desk below, drop the salary 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)
);

Solution for Practice Exercise #6:

The following Oracle ALTER TABLE statement would drop the profits column from the personnel table:

ALTER TABLE employees
  DROP COLUMN salary;

Practice Exercise #7:

Based on the departments desk below, rename the department_name column to dept_name.

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 #7:

The following Oracle ALTER TABLE assertion would rename the department_name column to dept_name in the departments table:

ALTER TABLE departments
  RENAME COLUMN department_name TO dept_name;