SQL as a Data Definition Language : DROP, RENAME, TRUNCATE and COMMENT


The DROP command is used to drop or delete tables, functions, procedures, packages, views, synonym, sequences, table spaces etc. For example with DROP TABLE command we can completely delete a table from a database, and this command will also destroy the table structure. The basic syntax of DROP TABLE statement is as follows :
 DROP TABLE table_name;
Example :
 DROP TABLE Employee;
The above statement will delete the Employee table completely. We can also delete a database by using the DROP DATABASE command. The basic syntax of DROP DATABASE statement is as follows :
 DROP DATABASE database_name;
Example :
 DROP DATABASE first_db;
The above statement will delete the the database named first_db from the system.


The RENAME command is used to rename a table. The basic syntax of RENAME command is as follows :
 RENAME old_table_name to new_table_name;
Example :
 RENAME EmployeeList to EmpLst;
The above statement will rename the table EmployeeList to EmpLst.


The TRUNCATE command is used to delete all the rows of an existing table, but unlike DROP TABLE command, it will not destroy the table's structure. The basic syntax of TRUNCATE TABLE command is as follows :
 TRUNCATE TABLE table_name;
Example :
The above query will delete all the records from Employee table. And remember when we apply truncate command on a table its Primary key is initialized. For example if we have a table with 10 rows and an auto_increment primary key, and if we use DELETE command to delete all the rows, then it will delete all the rows, but will not initialize the primary key, hence if you will insert any row after using delete command, the auto_increment primary key will start from 11. But in case of truncate command, primary key is re-initialized.


The COMMENT statement is used to add a comment about a table, view, materialized view, or column into the data dictionary. The basic syntax of COMMENT statement is as follows :
 COMMENT ON TABLE table_name IS 'Your Comment';
Example :
 COMMENT ON TABLE Employee IS 'Holds the employee Details';
And to drop a comment from the database, set it to the empty string ' '.

Next Topic :

No comments:

Post a Comment