The SQL DROP command is used to remove an object from the database. If you drop a table, all the rows in the table is deleted and the table structure is removed from the database. Once a table is dropped we cannot get it back, so be careful while using RENAME command. When a table is dropped all the references to the table will not be valid.
Syntax to drop a sql table structure:
DROP TABLE table_name;
For Example: To drop the table employee, the query would be like
DROP TABLE employee;
Difference between DROP and TRUNCATE Statement:
If a table is dropped, all the relationships with other tables will no longer be valid, the integrity constraints will be dropped, grant or access privileges on the table will also be dropped, if want use the table again it has to be recreated with the integrity constraints, access privileges and the relationships with other tables should be established again. But, if a table is truncated, the table structure remains the same, therefore any of the above problems will not exist.
Remarks on SQL DROP TABLE
- Before executing the SQL DROP TABLE you need to to consider it seriously. You have to asked yourself whether the table being removed is still used in the future. If this is the case, you have to backup the database first before droping table.
- You should always find all the foreign key constraints or referencing table that reference to the table being removed. Then you remove all the references first before dropping table.
- It is safer to specify the database name when you use SQL DROP TABLE statement. Because in the database landscape you may have different database systems and names for development, testing and production systems. Sometimes you have to go to those systems at the same time to do the work. You may confuse the production, testing and development systems and potentially drop table in the production system with the thought that it is development system.
- If you just want to delete the data use SQL DELETE or SQL TRUNCATE statement instead.