DDL

DROP TABLE

Q: How do I drop a table only if it exists?

DROP TABLE IF EXISTS table_name is supported on Postgres, MySQL, SQLite, SQL Server (2016+), Snowflake, BigQuery, DuckDB. Useful in idempotent migration scripts.

Q: What's the difference between DROP TABLE CASCADE and RESTRICT?

RESTRICT (default) refuses the drop if anything (foreign keys, views) depends on the table. CASCADE drops the table and every dependent object. CASCADE is convenient and occasionally dangerous: a single command can take out views you didn't know existed.

Q: Can I undo DROP TABLE?

Inside a transaction on Postgres / SQL Server / DuckDB: ROLLBACK reverses it. On MySQL InnoDB and Oracle: no — DDL implicitly commits. After commit on every engine: only a backup restore. The safer pattern: rename first, monitor for a week, then drop.

Q: How do I drop a column instead of a table?

ALTER TABLE table_name DROP COLUMN column_name — works on Postgres, MySQL, SQL Server, Oracle, DuckDB, Snowflake, BigQuery. Older SQLite (<3.35) requires the rebuild-and-copy pattern.

Q: Does DROP TABLE delete the data permanently?

Yes — once committed (and outside any time-travel feature), the rows are gone. Snowflake has Time Travel (recoverable within retention window). Postgres has pg_repack and similar tools but no native undelete. Backup restore is the universal fallback.

Q: Can I drop multiple tables at once?

Yes on most engines: DROP TABLE t1, t2, t3 (Postgres, MySQL, SQL Server). Add IF EXISTS for idempotency. Be deliberate — the comma-separated form is convenient but each table is gone the moment the statement commits.

Remove a table and every row inside it. Permanent, fast, and takes its indexes, constraints, and triggers with it. The single statement most likely to ruin a Friday afternoon when run against the wrong environment.

Syntax #

sql

DROP TABLE [IF EXISTS] table_name [CASCADE | RESTRICT];

-- Drop a column from an existing table.
ALTER TABLE table_name DROP COLUMN column_name;

Example #

Loading SQL editor...

CASCADE vs RESTRICT #

When other objects depend on the table (foreign keys, views, materialized views), the engine has to decide what to do:

RESTRICT (the default on most engines): refuse the drop if anything depends on the table. Forces you to clean up the dependents first.
CASCADE: drop the table and every dependent object. Convenient, occasionally dangerous: a single CASCADE can take out views you didn't know existed.

sql

-- Refuse if anything references this table (default).
DROP TABLE orders RESTRICT;

-- Drop the table and every dependent view, FK, etc.
DROP TABLE orders CASCADE;

Dialect notes #

IF EXISTS: Supported in Postgres, DuckDB, MySQL, SQLite, SQL Server (2016+), Snowflake, BigQuery. Always use it in idempotent migrations.
MySQL: CASCADE and RESTRICT are accepted but currently no-ops; DROP TABLE does not auto-drop dependents.
DROP TABLE in a transaction: Postgres, SQL Server, and DuckDB roll it back if the transaction aborts. MySQL with InnoDB does an implicit commit on DDL, so you cannot un-drop with ROLLBACK. Know your engine before relying on transactional DDL.
DROP COLUMN: Standard in Postgres, MySQL, SQL Server, DuckDB, Snowflake, BigQuery. Older SQLite (<3.35) requires the rebuild-and-copy dance.

FAQ #

How do I drop a table only if it exists?

DROP TABLE IF EXISTS table_name is supported on Postgres, MySQL, SQLite, SQL Server (2016+), Snowflake, BigQuery, DuckDB. Useful in idempotent migration scripts.

What's the difference between DROP TABLE CASCADE and RESTRICT?

RESTRICT (default) refuses the drop if anything (foreign keys, views) depends on the table. CASCADE drops the table and every dependent object. CASCADE is convenient and occasionally dangerous: a single command can take out views you didn't know existed.

Can I undo DROP TABLE?

Inside a transaction on Postgres / SQL Server / DuckDB: ROLLBACK reverses it. On MySQL InnoDB and Oracle: no — DDL implicitly commits. After commit on every engine: only a backup restore. The safer pattern: rename first, monitor for a week, then drop.

How do I drop a column instead of a table?

ALTER TABLE table_name DROP COLUMN column_name — works on Postgres, MySQL, SQL Server, Oracle, DuckDB, Snowflake, BigQuery. Older SQLite (<3.35) requires the rebuild-and-copy pattern.

Does DROP TABLE delete the data permanently?

Yes — once committed (and outside any time-travel feature), the rows are gone. Snowflake has Time Travel (recoverable within retention window). Postgres has pg_repack and similar tools but no native undelete. Backup restore is the universal fallback.

Can I drop multiple tables at once?

Yes on most engines: DROP TABLE t1, t2, t3 (Postgres, MySQL, SQL Server). Add IF EXISTS for idempotency. Be deliberate — the comma-separated form is convenient but each table is gone the moment the statement commits.

Warning

Don't DROP TABLE to "fix" production. Rename it first (ALTER TABLE orders RENAME TO orders__deprecated_2026_04_26), wait a week with monitoring, then drop. The rename is reversible in seconds, the drop is not. The same logic applies to DROP COLUMN — once it's gone, the data is gone, and a backup restore is the only way back.