DROP/TRUNCATE/RENAME in DBMS

DROP/TRUNCATE/RENAME

In this article, we will learn about DROP/TRUNCATE/RENAME in DBMS . Sometimes the user wants to permanently delete the existing table or wants to delete the existing data alone or change the table name all these purposes are served using 3 DDL(data definition language) commands drop, truncate and rename respectively.

Drop in DBMS

DROP/TRUNCATE/RENAME In DBMS

1. Dropping a table using DROP in DBMS

  • Drop command delete the table existence completely i.e drop statement destroys the existing database object of that particular table, index or view
  • After dropping a table if you try to use the table then compiler shows an error as ” table or view does not exist

Syntax

DROP TABLE table_name;

Example

drop table emp;

2. TRUNCATE command

  • The truncate command will make the table empty i.e all the table data will be deleted but the structure and database object is still alive and the table can be reused normally
  • The truncate command logically nothing but using delete command for deleting the records in the table without specifying the where condition i.e all the rows get deleted in that case

Syntax

TRUNCATE TABLE table_name;

Example

truncate table emp;

O/P

Table emp truncated successfully

After truncating the table if you try to display the table data using a select statement then you’ll get a message as” 0 rows selected” i.e nothing to display because the table is empty without any rows

3. Renaming a table: RENAME COMMAND

The rename command is used to change the existing table name and give a new name to the table.

Syntax

rename old_table _name to new_table_name

Example

rename emp to Employees

O/P

Table emp  renamed successfully

The above query changes the emp table name from emp to employees all operations on the table must be done using the new name employees otherwise it will raise an error.

Prime Course Trailer

Related Banners

Get PrepInsta Prime & get Access to all 200+ courses offered by PrepInsta in One Subscription