DROP command in SQL is a DDL command that permanently removes the data (or existing table) from the database and free the space from the memory. In this article, we will briefly discuss how to drop column, table, or database in SQL.
In the previous article, we have discussed how to remove the data from the existing table using SQL TRUNCATE and SQL DELETE command. Now in this article, we will again discuss how to remove the data or table from the database using DROP command.
So, what’s the difference between SQL TRUNCATE, SQL DELETE and SQL DROP and how SQL DROP works.
Let’s explore in this article.
Table of Content
- What is the DROP command in SQL?
- SQL DROP Column
- SQL DROP TABLE
- DROP TABLE IF EXISTS
- DROP vs. DELETE vs. TRUNCATE
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.
- 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
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.
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;
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.
ALTER TABLE table_nameDROP COLUMN column_names;
Must Read: Introduction to SQL
Example: Drop the column name Marks from the Student table.
ALTER TABLE StudentDROP COLUMN Marks;
Must Read: UPDATE query in SQL
Example: Drop marks and subject columns from the Student table.
ALTER TABLE StudentDROP COLUMN Marks, Subject;
DROP TABLE table_name;
Example: Remove the Student table from the database.
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: 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.
DROP TABLE IF EXISTS table_name;
Must Read: SQL LIMITS
DELETE vs. TRUNCATE vs. 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 command as it deletes one row at a time base on the specified conditions||It is faster than both the DELETE and TRUNCATE command as it deletes all the records at a time without any condition||It is faster than DELETE but slower than TRUNCATE asit firstly deletes the rows and then the table from the database.|
Must Read: All about DML Command in SQL
Must Read: SQL ACID Properties
In this article, we have briefly discussed how to use drop command in sql with examples.
Hope this article, will help you in your data science/data analysis journey.
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 automatically confirm the operation. 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.
Download this article as PDF to read offlineDownload as PDF