SQL DROP Keyword

What is DROP Command in SQL?

Consider we created a table student and inserted student data into it. At some point, we may no longer need the student table and the data present in it. We can use the DROP TABLE command in SQL to remove the student table with all its data and associated information.

The Drop command in SQL not only deletes a table but also deletes other entities such as database, column, index, constraint, etc. For example, the DROP COLUMN command deletes the specified column from the table, and the DROP DATABASE command deletes the specified database and all of its information.

SQL's drop command is a DDL (Data Definition Language) command. DDL commands are used to create or alter the structure of the database objects such as a table, column, etc. For example, CREATE TABLE is a DDL command used to create the table with the given columns. Objects deleted using the Drop command are lost permanently and cannot be rolled back.

The syntax for the Drop commands varies for different database providers such as MySql, Oracle, SQL Server, etc. We follow the Oracle SQL syntax throughout this article for simplicity.

Uses of DROP Command in SQL

Below are the various possible ways to use the DROP command to delete different types of database entities.

DROP DATABASE

The DROP DATABASE command is used to delete the entire database. When a database is deleted, all the tables, indexes, constraints, etc., present in it will be deleted. All the data stored in the database will be deleted. The syntax for dropping a database is

• database_name - The name of the database to be deleted.

DROP TABLE

The DROP TABLE command is used to delete the specified table and the data stored. The syntax for dropping a table is

• table_name - The name of the table to be deleted.

DROP COLUMN

The DROP COLUMN command is used to delete a column in a table. The syntax for dropping a column is

• column_name - The name of the column to be deleted.
• table_name - The table on which the column is present.

DROP INDEX

The DROP INDEX command is used to delete an index on a column or a group of columns in a table. The syntax for dropping an index is

• index_name - The name of the index to be deleted.

DROP CONSTRAINT

The DROP CONSTRAINT command is used to delete a constraint in a table. The syntax for dropping a constraint is

• constraint_name - The name of the constraint to be deleted.
• table_name - The table in which the constraint is present.

How to Use DROP Command in SQL?

Drop command in SQL can be clubbed with other keywords to delete specific database entities. For example, the DROP TABLE command is used to delete a table, and the DROP COLUMN command is used to delete a column in a table. The next section will cover the different ways of using the Drop command to delete entities such as a table, database, column, etc.

Examples of DROP Command in SQL

Let's understand the different Drop commands with the below student table. We will be using this table in the section to perform various drop operations on it.

First, we create the student table with the columns id, name, age, and gender.

Add a constraint for the column gender so that the gender values can be Male, Female, or Other.

Add an index for the age column in the student table.

Finally, we populate student data in the student table.

The student table with the inserted data is

Example - 1 : Delete the Constraint c_gender from the Student Table

We will delete the constraint c_gender from the table student with the DROP CONSTRAINT command.

Output:

When a SELECT query is run on the table, the result will be the same as before, but the constraint c_gender is no more present. So the restriction of adding only Male, Female, or Other values to the gender column is removed, and we can add any values to it.

Example - 2: Delete the Index i_age from the Student Table

We will delete the i_age index from the student table with the DROP INDEX command.

Output:

The result will be the same when a SELECT query is run on the table after deleting the index i_age. But the index on the column age will not be present, and the values of the age column won't be indexed anymore.

Example - 3: Delete the Column 'AGE' from the Student Table

We will delete the age column from the student table with the DROP COLUMN command.

Output:

The table looks like the one below when a SELECT query is performed on the table after deleting the age column.

Example - 4: Delete the Student Table

We will delete the student table with the DROP TABLE command.

Output: