ALTER TABLE Statement in Oracle SQL – PLSQL

The ALTER TABLE statement in PLSQL allows us to alter or change the following:

1. Rename an already existing table in database.
2. Add, modify or delete/drop column(s) from an already existing table in database.

It is a Data Definition Language (DDL) statement.
Syntax for the ALTER TABLE to rename an existing table in PLSQL is:

ALTER TABLE table_name
RENAME TO new_table_name;

Let’s take an example for understanding:
Suppose we have a table named “Employee” in database as shown below.

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Scenario 1:

Here we will see how we can use ALTER TABLE statement to rename the ’employee’ table.

ALTER TABLE employee
RENAME TO employee_new;

The above ALTER TABLE statement will rename table ’employee’ as ’employee_new’ in the database. It does not affect the data of the table, hence if you query ’employee_new’ table as;

SELECT *
FROM employee_new;

You will get the following result:

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Scenario 2:

Here we will see how we can use ALTER TABLE statement to add a column to already existing table named ’employee’.

The syntax for adding a column to already existing table is:

ALTER TABLE table_name
ADD column_name datatype;

Suppose our ’employee’ table has the data as shown below:

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Now we will add one more column named “nick_name” in the ’employee’ table by writing the ALTER TABLE statement as:

ALTER TABLE employee
ADD nick_name VARCHAR2(100);

If we query the ’employee’ table as;

SELECT *
FROM employee;

We will get the following output:

Employee_ID Employee_Name Salary Department Commission Nick_Name
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Here we have successfully added a new column “nick_name” to ’employee’ table.


Scenario 3:

Adding multiple columns to existing database table, the syntax for adding multiple columns using ALTER TABLE statement is:

ALTER TABLE table_name
ADD(column_name1 datatype
,column_name2 datatype
,column_name3 datatype
.
.
.
,column_nameN datatype
);

Suppose we have ’employee’ table as shown below:

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Now we want to add 3 columns namely “nick_name”, “city” and “state” to the ’employee’ table. We can achieve the same by writing ALTER TABLE statement as:

ALTER TABLE employee
ADD (nick_name VARCHAR2(100)
     ,city VARCHAR2(200)
     ,state VARCHAR2(200)
    );

If we query the ’employee’ table as;

SELECT *
FROM employee;

We will get the following output:

Employee_ID Employee_Name Salary Department Commission Nick_Name City State
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Here we have successfully added 3 new columns “nick_name”,”city” and “state” to ’employee’ table using ALTER TABLE statement.


Scenario 4:

Altering or modifying already existing columns in a table.
The syntax of the ALTER TABLE statement to achieve this is;

ALTER TABLE table_name
MODIFY column_name datatype;

Suppose we create an ’employee’ table using the code shown below;

CREATE TABLE employee
(employee_id   NUMBER(10)    NOT NULL
,employee_name VARCHAR2(500) NOT NULL
,salary        NUMBER(20)    NOT NULL
,department    VARCHAR2(300) NOT NULL
,commission    VARCHAR2(100)
);

But then we realize that the “commission” column of the ’employee’ table should have been a NUMBER type rather than being a VARCHAR2 type and it should not allow NULL values.
So instead of deleting the ’employee’ table and creating the same again, we can use the ALTER TABLE statement to change the datatype of the “commission” column from VARCHAR2 to NUMBER as follows:

ALTER TABLE employee
MODIFY commission NUMBER(20) NOT NULL;

The NOT NULL in the code above will force the commission column to have values and will not allow NULL.


Scenario 5:

Altering or modifying multiple columns in a table at once using ALTER TABLE statement.
The syntax of the ALTER TABLE statement to achieve this is;

ALTER TABLE table_name
MODIFY (column_name1 datatype,
,column_name2 datatype
,column_name3 datatype
.
.
,column_nameN datatype
) ;

Suppose we create an ’employee’ table using the code shown below;

CREATE TABLE employee
(employee_id   NUMBER(10)    NOT NULL
,employee_name VARCHAR2(500) NOT NULL
,salary        NUMBER(20)    NOT NULL
,department    VARCHAR2(300) NOT NULL
,commission    VARCHAR2(100)
);

But then we realize that the “commission” column of the ’employee’ table should have been a NUMBER type rather than being a VARCHAR2 type and should not allow NULL values. Also we want the employee_name column to be of 300 length instead of 500.
So instead of deleting the ’employee’ table and creating the same again, we can use the ALTER TABLE statement to make the necessary changes as follows:

ALTER TABLE employee
MODIFY (employee_name VARCHAR2(300)
        ,commission NUMBER(20) NOT NULL
       );

Scenario 6:

Deleting or dropping column in a table using ALTER TABLE statement
The syntax of the ALTER TABLE statement to achieve this is;

ALTER TABLE table_name
DROP COLUMN column_name

) ;

Suppose we create an ’employee’ table using the code shown below;

CREATE TABLE employee
(employee_id   NUMBER(10)    NOT NULL
,employee_name VARCHAR2(500) NOT NULL
,salary        NUMBER(20)    NOT NULL
,department    VARCHAR2(300) NOT NULL
,commission    VARCHAR2(100)
);

But then we realize that the “commission” column is not required and can be dropped from the ’employee’ table.

So instead of deleting the ’employee’ table and creating the same again, we can use the ALTER TABLE statement to make the necessary changes as follows:

ALTER TABLE employee
DROP COLUMN commission;

Scenario 7:

Renaming a column in a table using ALTER TABLE statement

The syntax of the ALTER TABLE statement to rename a column in a table is;

ALTER TABLE table_name
RENAME COLUMN old_column_name TO new_column_name;

Suppose we create an ’employee’ table using the code shown below;

CREATE TABLE employee
(employee_id   NUMBER(10)    NOT NULL
,employee_name VARCHAR2(500) NOT NULL
,salary        NUMBER(20)    NOT NULL
,department    VARCHAR2(300) NOT NULL
,commission    VARCHAR2(100)
);

But then we realize that the “commission” column should be named as “comm”, so instead of deleting the ’employee’ table and creating the same again, we can use the ALTER TABLE statement to make the necessary changes as follows:

ALTER TABLE employee
RENAME COLUMN commission TO comm;

Tagged , , , , , , , , , , , . Bookmark the permalink.