How to use DROP command in SQL?
In SQL, the DROP command is used to delete a database, table, index, or view. To use the DROP command, you must be very careful because it permanently deletes the object from the database. Therefore, it's essential to backup the object before using the DROP command. This article will discuss how to use the DROP command in SQL and the precautions you need to take while using it.
In the previous article, we discussed removing the data from the existing table using SQL TRUNCATE and SQL DELETE commands. In this article, we will again discuss removing the data or table from the database using the DROP command.
So, what’s the difference between SQL TRUNCATE, SQL DELETE, and SQL DROP, and how does SQL DROP work?
Let’s explore this in this article.
Table of Content
Best-suited Database and SQL courses for you
Learn Database and SQL with these high-rated online courses
What is the DROP command in SQL?
DROP command in SQL removes table definition and all the data from one or more database tables.
- It is a Data Definition Language (DDL) command.
- It removes all data/indexes/triggers/constraints/ and permission specific to that table.
- SQL DROP TABLE statement permanently removes the data from the tables and the metadata that defines the table in the data dictionary.
Note
- Dropping a table or a database is an irreversible process, so be extra careful while dropping the table or database.
- SQL DROP TABLE command can’t be used to remove a table that a Foreign Key references
Syntax
The syntax to drop the table is a straightforward DROP TABLE command followed by the table names, i.e.,
DROP TABLE table_names;
Note: You can drop multiple tables at a time.
Now, let’s have some examples to understand better how to use the SQL DROP command.
So, firstly, we will create a student table of a class that contains the record of a student who scored maximum marks in a particular subject using the CREATE TABLE and INSERT command in SQL.
CREATE TABLE Student( RollNo int PRIMARY KEY, Name VARCHAR(100) NOT NULL, Gender TEXT NOT NULL; Subject VARCHAR(30), MARKS INT (3));
INSERT INTO Student VALUES (1, Vaibhav, M, Mathematic, 100);INSERT INTO Student VALUES (2, Vishal, M, Physics, 79);INSERT INTO Student VALUES (3, Saumya, F, Chemistry, 95);INSERT INTO Student VALUES (4, Arun, M, English, 78);INSERT INTO Student VALUES (5, Anjum, F, Hindi, 83);INSERT INTO Student VALUES (6, Radhika, F, Biology, 57);INSERT INTO Student VALUES (7, Harpreet, F, Physical Education, 68);
SELECT * FROM Student;
Output
| RollNo | Name | Gender | Subject | Marks |
| 1 | Vaibhav | M | Mathematics | 100 |
| 2 | Vishal | M | Physics | 79 |
| 3 | Saumya | F | Chemistry | 95 |
| 4 | Arun | M | English | 78 |
| 5 | Anjum | F | Hindi | 83 |
| 6 | Radhika | F | Biology | 57 |
| 7 | Harpreet | F | Education | 68 |
Must Read: SQL SELECT
Now, we will use the above Student table in our examples.
SQL DROP COLUMN
SQL DROP COLUMN is used to delete the specified column(s) from the table or the database.
Syntax
ALTER TABLE table_nameDROP COLUMN column_names;
Must Read: Introduction to SQL
Single Column
Example: Drop the column name Marks from the Student table.
Query
ALTER TABLE StudentDROP COLUMN Marks;
Output
| RollNo | Name | Gender | Subject |
| 1 | Vaibhav | M | Mathematics |
| 2 | Vishal | M | Physics |
| 3 | Saumya | F | Chemistry |
| 4 | Arun | M | English |
| 5 | Anjum | F | Hindi |
| 6 | Radhika | F | Biology |
| 7 | Harpreet | F | Education |
Must Read: UPDATE query in SQL
Must Read: How to CREATE, UPDATE, INSERT and DELETE SQL views
Multiple Columns
Example: Drop marks and subject columns from the Student table.
Query
ALTER TABLE StudentDROP COLUMN Marks, Subject;
Output
| RollNo | Name | Gender |
| 1 | Vaibhav | M |
| 2 | Vishal | M |
| 3 | Saumya | F |
| 4 | Arun | M |
| 5 | Anjum | F |
| 6 | Radhika | F |
| 7 | Harpreet | F |
Must Read: SQL Logical Operator
Must Read: Introduction to Normalization
SQL DROP TABLE
SQL DROP TABLE commands are used to delete the specified table from the database.
Syntax
DROP TABLE table_name;
Example: Remove the Student table from the database.
Query
DROP TABLE Student;
We can drop indexes, constraints, and databases like the SQL DROP COLUMN and DROP TABLE.
Syntax for DROP Database
DROP DATABASE database_name;
Syntax for DROP Constraints
ALTER TABLE table_nameDROP CONSTRAINTS constraint name;
Syntax to DROP Index
DROP INDEX index_name;
Must Read: What is the difference between SQL and MySQL?
Must Read: Difference between SQL and NoSQL
DROP TABLE IF EXISTS
When there are multiple tables in any database, and you want to remove any table from the database but are unsure whether that table exists in the database or not. So, in that case, we can add an optional IF EXISTS command while dropping the table.
Syntax
DROP TABLE IF EXISTS table_name;
Must Read: SQL LIMITS
Must Check: Database and SQL Online Course and Certifications
DELETE vs. TRUNCATE vs. DROP
| DELETE | TRUNCATE | DROP |
| It is a DML command | It is a DDL command. | It is a DDL command |
| DELETE FROM tble_nameWHERE conditions; | TRUNCATE TABLE table_name; | DROP TABLE table_name; |
| It removes one or more records from the table | It removes all the rows from the existing table | It completely removes the table from the database |
| It doesn’t free the allocated space of the table. | It doesn’t free the allocated space of the table. | It completely removes the allocated space for the table from the memory. |
| It is slower than the DROP and TRUNCATE commands as it deletes one row at a time based on the specified conditions. | It is faster than the DELETE and TRUNCATE commands as it deletes all the records simultaneously without any condition. | It is faster than DELETE but slower than TRUNCATE as it first deletes the rows and then the table from the database. |
Must Read: All about DML Command in SQL
Must Read: SQL ACID Properties
Conclusion
In this article, we have briefly discussed how to use the drop command in SQL with examples. Hope this article will help you in your data science/data analysis journey.
Keep Learning!!
Keep Sharing!!
FAQs
Can I use the DROP command without confirmation?
By default, many database management systems (DBMS) prompt for confirmation before executing the DROP command. However, some DBMSs provide options to disable the confirmation prompt or provide a way to confirm the operation automatically. It is important to be cautious when using these options to avoid accidental deletions.
Are there any restrictions on using the DROP command?
Yes, the DROP command may have restrictions depending on the database system and the user's permissions. Typically, only users with sufficient privileges, such as database administrators or owners of the object, can execute the DROP command. It is important to have the necessary permissions to avoid unauthorized deletions.
Can I recover the dropped objects using the DROP command?
No, the DROP command permanently removes the specified object and its data from the database. Once an object is dropped, it cannot be easily recovered. It is important to exercise caution when using the DROP command and ensure that you have a backup or a plan in place to handle data loss.
What is the difference between DROP and TRUNCATE commands?
The TRUNCATE command deletes all rows from a table but retains the table structure, whereas the DROP command removes the entire table structure along with its data. If you use TRUNCATE, the table can still be used afterwards, but if you use DROP, the table is completely removed and would need to be recreated.
What happens to the data integrity constraints when a table is dropped?
When a table is dropped, all integrity constraints associated with the table, such as primary keys and foreign keys, are also removed.
Does dropping a table affect its relationships with other tables?
Yes, dropping a table invalidates all relationships and references from other tables to the dropped table.
Can I drop multiple tables at once in SQL?
Yes, you can drop multiple tables by specifying them in a single DROP TABLE command, separated by commas.
Is there a risk of data loss with the DROP command?
Yes, there is a significant risk of data loss. The DROP command deletes the table and its data permanently, so it should be used with caution.
How does DROP command impact database performance?
Dropping a table can help improve database performance by freeing up space. However, it should be used judiciously as it leads to the permanent deletion of data.
Vikram has a Postgraduate degree in Applied Mathematics, with a keen interest in Data Science and Machine Learning. He has experience of 2+ years in content creation in Mathematics, Statistics, Data Science, and Mac... Read Full Bio
Top Picks & New Arrivals
