Why to rename table name?

Table consists of data in the form of rows and columns. Table name plays important role while creating, using the table and table name specified while creating table. The reasons behind of renaming the table are –

  • Table name specified incorrectly.
  • Requirements change leads to table name changes to sync with requirements.
  • Table name not fit for the requirements and needs a change.

ALTER TABLE statement fulfils the requirement of renaming table.

How to use RENAME TABLE?

ALTER TABLE statement is used to rename the table from old to new. The syntax varies for different databases.

Syntax (Oracle, MySQL, MariaDB) -

ALTER TABLE table_name RENAME TO new_table_name;

ALTER TABLE renames the columns also by specifying new name with the use of ALTER TABLE.

Syntax (Oracle) -

ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name; 

Syntax (MySQL, MariaDB) -

ALTER TABLE table_name CHANGE COLUMN old_column_name TO new_column_name; 

  • table_name - Specifies the name of the table.
  • old_column_name - Specifies the current column name.
  • new_column_name - Specifies the new column name.

Example -

Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 60000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Renaming table

We can rename the table using ALTER TABLE statement. The syntax is –

ALTER TABLE table_name RENAME COLUMN new_table_name;

In the below example, we are renaming the employee_details to emp_details. The query for the same is -

 ALTER TABLE employee_details RENAME TO emp_details; 

After successful execution of the above query, the table gets renamed as emp_details. The table looks like below -

emp_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 60000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Renaming column name (Oracle)

We can rename column from old to new. The syntax is -

ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name; 

In the below example, we are renaming emp_id column to eid of employee_details table. The query for the same is -

 ALTER TABLE employee_details RENAME COLUMN emp_id TO eid; 

After successful execution of the above query, the emp_id column renamed as eid and the column accessed as eid in future. The table looks like below -

employee_details

eid emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 60000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Renaming column name (MySQL, MariaDB)

We can rename column from old to new. The syntax is -

ALTER TABLE table_name CHANGE COLUMN old_column_name TO new_column_name; 

In the below example, we are renaming emp_id column to eid of employee_details table. The query for the same is -

 ALTER TABLE employee_details CHANGE COLUMN emp_id TO eid; 

After successful execution of the above query, the emp_id column renamed as eid and the column accessed as eid in future. The table looks like below -

employee_details -

eid emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 60000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000