SQL DROP Statement

The DROP statement is used to delete entire tables, databases, or specific constraints from a table. It removes the specified object and all associated data permanently, so use with caution.

In the statement below, we delete the entire Customers table from the database.

Example​

Drop the Customers table:

DROP TABLE Customers;

In this example, the DROP TABLE statement deletes the Customers table and all data within it.

Video Explanation​

Watch

Syntax​

To drop a table:

DROP TABLE table_name;

To drop a database:

DROP DATABASE database_name;

To drop a constraint from a table:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Demo Database​

The Customers table structure used in the examples would look like this before dropping:

Dropping a Table​

Example​

Delete the Orders table:

DROP TABLE Orders;

This deletes the entire Orders table and all of its data permanently.

Dropping a Database​

Example​

Delete the SalesDB database:

DROP DATABASE SalesDB;

Using DROP DATABASE will remove the database and all tables and data within it.

Dropping a Column Constraint​

Example​

Remove the UNIQUE constraint from the Email column in the Customers table:

ALTER TABLE Customers
DROP CONSTRAINT uc_email;

To drop constraints such as UNIQUE, FOREIGN KEY, or PRIMARY KEY, use ALTER TABLE followed by DROP CONSTRAINT and the specific constraint name.

Dropping an Index​

Example​

Delete an index named idx_customer_name:

DROP INDEX idx_customer_name;

Dropping an index improves space efficiency but may slow down query performance for indexed columns.

Dropping a View​

Example​

Delete a view named CustomerOrders:

DROP VIEW CustomerOrders;

Views can be removed using the DROP VIEW statement, similar to tables.

Important Notes​

• Irreversible: The DROP statement permanently removes objects, and this action cannot be undone.
• Dependencies: Dropping tables or databases with dependencies will fail unless the dependencies are removed first.
• Permissions: Dropping tables or databases may require specific user permissions.