SQL DROP

What does the SQL DROP statement do?

Permanently deletes a database object such as a table, view, index, or entire database.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL DROP: Supported by PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite, Snowflake, BigQuery, DB2, and virtually all relational databases

SQL DROP Full Explanation

The SQL DROP statement is a data definition language (DDL) command that irreversibly removes a specified database object from the catalog and deletes all data stored in that object. Depending on the object type, associated constraints, indexes, triggers, and privileges are also removed. Because the operation is destructive and typically non-recoverable without backups, most systems require elevated privileges to execute it. Many dialects provide optional clauses like IF EXISTS to avoid errors when the target object is missing, and CASCADE or RESTRICT to control whether dependent objects are dropped automatically or the statement should fail. Once executed, a DROP cannot be rolled back unless it is issued inside a transactional DDL-supporting database (for example, PostgreSQL) and the transaction is rolled back before commit.

SQL DROP Syntax

-- Drop a table
drop table [if exists] table_name [cascade|restrict];

-- Drop a database
drop database [if exists] database_name;

-- Drop other object types (generic form)
drop {view|index|schema|sequence|function|procedure} [if exists] object_name [cascade|restrict];

SQL DROP Parameters

IF EXISTS (keyword) - Instructs the database to skip the drop and return a notice if the object does not exist.
CASCADE (keyword) - Automatically drops objects that depend on the target object.
RESTRICT (keyword) - Refuses to drop the object if other objects depend on it.
object_name (identifier) - The name of the table, database, or other object to be removed.

Example Queries Using SQL DROP

-- Safely drop a table only if it exists
DROP TABLE IF EXISTS temp_sales;

-- Drop a table along with all foreign-key references
DROP TABLE orders CASCADE;

-- Remove a view
DROP VIEW quarterly_summary;

-- Delete an entire database
DROP DATABASE IF EXISTS staging_db;

Expected Output Using SQL DROP

The specified object and its data are deleted from disk and the system catalog
Queries referencing that object fail until recreated

Use Cases with SQL DROP

Cleaning up temporary or staging tables after ETL jobs
Removing obsolete views or indexes to reclaim storage
Deleting and recreating objects during iterative schema design
Dropping a test database before restoring a fresh copy

Common Mistakes with SQL DROP

Forgetting that DROP is irreversible and losing critical data
Omitting IF EXISTS and causing errors in deployment scripts when objects are missing
Dropping a table without CASCADE when other objects depend on it, resulting in failure
Assuming every database supports transactional DDL; many cannot roll back a DROP after execution

First Introduced In

SQL-92

Frequently Asked Questions

What objects can I drop?

You can drop tables, views, indexes, sequences, schemas, functions, stored procedures, and entire databases, depending on your database privileges.

Is DROP the same as DELETE?

No. DELETE removes rows from a table but keeps the table structure. DROP removes the table structure and its data from the database entirely.

How do I avoid errors if the object might not exist?

Add the IF EXISTS clause. The database will skip the operation and return a notice instead of throwing an error.

Will DROP free disk space immediately?

Most systems reclaim the space right away, but some engines may defer actual file deletion until internal maintenance operations complete.