DROP command in SQL

quiz
Challenge Inside! : Find out where you stand! Try quiz, solve problems & win rewards!

Overview

The DROP command in SQL is used to drop entities such as databases, tables, columns, indexes, constraints, etc. When an entity is dropped, all its related information (data and metadata) will be deleted. For example, all related information, such as data, index, constraints, etc., will be deleted when a table is dropped.

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.

Syntax 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

DROP DATABASE <database_name>
  • 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

DROP TABLE <table_name>
  • 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

ALTER TABLE <table_name>
DROP COLUMN <column_name>;
  • 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

DROP INDEX <index_name>;
  • 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

ALTER TABLE <table_name>
DROP CONSTRAINT <constraint_name>;
  • 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.

Example 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.

CREATE TABLE student (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(100) NOT NULL,
    age NUMBER NOT NULL,
    gender VARCHAR2(10) NOT NULL
);

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

ALTER TABLE student
ADD CONSTRAINT c_gender
CHECK (
    gender IN ('Male', 'Female', 'Other')
);

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

CREATE INDEX i_age ON student(age);

Finally, we populate student data in the student table.

INSERT INTO student (id, name, age, gender) VALUES (1, 'Llywellyn Pember', 9, 'Male');
INSERT INTO student (id, name, age, gender) VALUES (2, 'Cheston Waldie', 14, 'Male');
INSERT INTO student (id, name, age, gender) VALUES (3, 'Giacobo Frizzell', 15, 'Male');
INSERT INTO student (id, name, age, gender) VALUES (4, 'Kaila Kildale', 9, 'Female');
INSERT INTO student (id, name, age, gender) VALUES (5, 'Cordi Le Clercq', 14, 'Other');
INSERT INTO student (id, name, age, gender) VALUES (6, 'Clarke Volk', 12, 'Male');
INSERT INTO student (id, name, age, gender) VALUES (7, 'Lamond Goodwyn', 13, 'Male');
INSERT INTO student (id, name, age, gender) VALUES (8, 'Broddie Gores', 7, 'Male');
INSERT INTO student (id, name, age, gender) VALUES (9, 'Jayne Entwistle', 9, 'Female');
INSERT INTO student (id, name, age, gender) VALUES (10, 'Faina Alsina', 13, 'Female');

The student table with the inserted data is

IDNAMEAGEGENDER
1Llywellyn Pember9Male
2Cheston Waldie14Male
3Giacobo Frizzell15Male
4Kaila Kildale9Female
5Cordi Le Clercq14Other
6Clarke Volk12Male
7Lamond Goodwyn13Male
8Broddie Gores7Male
9Jayne Entwistle9Female
10Faina Alsina13Female

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.

ALTER TABLE student
DROP CONSTRAINT c_gender;

Output:

Table altered.

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.

DROP INDEX i_age;

Output:

Index dropped.

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.

ALTER TABLE student
DROP COLUMN age;

Output:

Table altered.

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

IDNAMEGENDER
1Llywellyn PemberMale
2Cheston WaldieMale
3Giacobo FrizzellMale
4Kaila KildaleFemale
5Cordi Le ClercqOther
6Clarke VolkMale
7Lamond GoodwynMale
8Broddie GoresMale
9Jayne EntwistleFemale
10Faina AlsinaFemale

Example - 4: Delete the student table

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

DROP TABLE student;

Output:

Table dropped.

Conclusion

  • The Drop command in SQL is used to drop entities such as database, table, column, index, constraint, etc
  • The Drop command is a DDL (Data Definition Language) operation.
  • Objects deleted using the drop command are lost permanently and cannot be rolled back.
  • The DROP DATABASE command deletes the specified database.
  • The DROP TABLE command deletes the given table.
  • The DROP COLUMN command deletes the given column from a table.
  • The DROP CONSTRAINT and DROP INDEX command deletes a constraint and an index.
Challenge Time!
quiz
quiz
Time to test your skills and win rewards! Note: Rewards will be credited after the next product update.
Free Courses by top Scaler instructors
rcbGet a Free personalized Career Roadmap from