You must have often come across a situation when you have already created a table but need to rename it. There are two ways to do so.
Drop the whole table altogether and recreate it with a new name. This is a logical choice if you are a student using your table for practice purposes only and can afford to lose the valuable data that your table contains. In practical environment there is a BIG NO for this method as Data held by database in tables is valuable and you cannot afford to lose it. If you opt for this method then I have a disclaimer for you –
“I do not recommend this method of renaming a table. If you opt this method then you will be responsible for your own loss.”
The second way of renaming a table is by using ALTER TABLE data manipulation language (DDL) statement. This is the recommended way of renaming a table. Let’s see how to do it.
In order to demonstrate how to rename a table using SQL ALTER TABLE DDL statement we will first create a very simple table by the name of TEST. This test table will have only one column test_name. You can define as many columns as you want but since we are concentrating over the renaming of the table therefore I am taking into account only one column.
CREATE TABLE test
(
test_name VARCHAR2 (15)
);
Let’s say we want to change the name of this table from TEST to EXAMPLE. For that we have to execute an ALTER TABLE statement.
First let’s see the syntax of ALTER TABLE statement:
Syntax
ALTER TABLE old_name_of_table RENAME TO new_name_of_table;
Now let’s change the name of our table from TEST to EXAMPLE according to the above syntax.
ALTER TABLE test RENAME TO example;
That’s all you have to do. Now I have a small but fun practice exercise for you –
Activity
- Create a table by the name of emp_master with three columns
emp_id with data type NUMBER and column size 3
emp_name with data type VARCHAR2 and column size 30
emp_salary with data type NUMBER and column size 5
First column i.e. emp_id must be the Primary key.
- Now rename this table from emp_master to employee_master.
Hope you enjoyed reading. You can also watch my video on how to rename a table using SQL ALTER TABLE statement. Please help me spread the word by sharing my video and this page on your social networking sites.
Thanks for reading. Have a great day!
