Removing Tables with DROP
The DROP TABLE command statement in SQL database management systems is used to permanently delete a complete table with its data records and information from the database. The DROP TABLE command deletes all the data of the table, along with the table structure, columns, indexes, constraints, and any dependent table objects such as triggers, foreign key relationships, etc. Remember, the DROP TABLE statement in a database table is an irreversible operation. This means that once a table entity is deleted, the database user cannot recover it. However, if you have a backup of the database, you can restore that data from the backup.
Let’s understand the DROP TABLE statement in SQL database management systems better.
Syntax for dropping a table in SQL.
The basic syntax for dropping a table in an SQL database management system is as follows.
DROP TABLE table_name;
Elements of DROP TABLE.
- table_name – Here, in the DROP TABLE command, this is the name of the table that the database user wants to delete.
If the database table already exists, and the database user executes the DROP TABLE command statement, the existing table will be permanently deleted along with all its data. And it cannot be recovered.
Example of dropping a table in an SQL database.
DROP TABLE employee;
In this example.
- In this example, as soon as the DROP TABLE command is applied to the ’employee’ table, all the data and structure of the table will be permanently dropped or deleted from the database.
Conditional drop with IF EXISTS in an SQL database.
If the table to be dropped does not exist in the database, the database user can apply the IF EXISTS clause to avoid any kind of DROP TABLE error. This ensures that the DROP TABLE command statement will only run if the table is found in the database.
Syntax with IF EXISTS in a database table.
DROP TABLE IF EXISTS table_name;
Example of using IF EXISTS in an SQL table.
DROP TABLE IF EXISTS employe;
In this example.
- If the `employe` table already exists in the database, it will be dropped when this command is applied. If the table does not exist, no error will be displayed during the drop table operation, and the command will simply do nothing.
Dropping multiple tables in a SQL database.
Database users can also drop multiple tables simultaneously by adding the names of multiple tables separated by a comma operator in the DROP TABLE command statement.
Syntax for dropping multiple tables in a SQL database.
DROP TABLE table x, table y, table z;
Example of dropping multiple tables in a SQL database.
DROP TABLE IF EXISTS employe, company, sales, purchase;
In this example.
- If the `employe`, `company`, `sales`, and `purchase` tables already exist in your database, they will be dropped when this command is applied.
Complete example of dropping tables in a SQL database.
Step 1: Drop a single database table
Dropping the ’employe’ table in a SQL database.
DROP TABLE employe;
This command permanently deletes the `employe` table from the database along with all its existing data and structure.
Step 2: Drop a database table only if it already exists in the database.
Here, the database user drops the ’employe’ table only if it already exists in the storage location.
DROP TABLE IF EXISTS employe;
This process ensures that if the `employe` table does not exist in the database, the DROP operation will not display any error during the table deletion process.
Step 3: Drop multiple tables simultaneously in a SQL database.
Dropping the ’employe’, ‘company’, ‘sales’, and ‘purchase’ database tables simultaneously.
DROP TABLE IF EXISTS employe, company, sales, purchase;
This command will immediately drop the ’employe’, ‘company’, ‘sales’, and ‘purchase’ database tables from the database if they already exist.
Conclusion of Removing Tables with DROP in a SQL database.
- The DROP TABLE command statement in SQL databases is a powerful and useful SQL table deletion command. It is used to permanently delete a table from the database along with its data and structure.
- The basic syntax for dropping a table is: DROP TABLE table_name;
- For conditional drop statements in database tables, to avoid any errors that might occur if the database table does not already exist, use the command: DROP TABLE IF EXISTS table_name;
- To drop multiple tables in an SQL database, drop several tables at once by separating them with a comma operator in a single statement.
- Dropping a table in an SQL database is an irreversible process, and it can directly impact dependent table objects such as foreign keys, views, and stored procedures in the user database table.
