🧨DROP TABLE

Remove a relation from the catalog

DROP TABLE removes the table definition and, by default, its storage. Dependent objects (views, foreign keys from other tables, triggers) may block the drop or be removed automatically when you use CASCADE: behavior is dialect-specific and must be read twice in review. For decommissioning regulated data, archive to cold storage before the final drop.

SQL

Idempotent staging cleanup

DROP TABLE IF EXISTS staging.tmp_import_20260412 CASCADE;

✓Illustrative result set

result
DROP TABLE

SQL

List FKs pointing at a table (PostgreSQL)

SELECT conname, conrelid::regclass AS from_table
FROM pg_constraint
WHERE confrelid = 'core.patients'::regclass;

✓Illustrative result set

conname	from_table
fk_visits_patient	core.visits
fk_orders_patient	billing.orders

DROP … CASCADE in a shared migration template

CASCADE can delete views and constraints your teammates thought were unrelated, silent widening of blast radius.

Explicit drops in dependency order, or rename-to-archive first

Generate a dependency graph from the catalog, drop or rewrite dependents explicitly, or ALTER TABLE … RENAME TO archive_… and revoke privileges until retention policy allows delete.

⚖️TRUNCATE vs DROP

Both remove rows; ownership and rollback differ.

TRUNCATE quickly removes all rows but keeps the table shell and grants; DROP TABLE removes the object entirely. Pick truncate for “reset staging”; pick drop when the schema itself should disappear from migrations.

IF EXISTS

Makes teardown scripts rerunnable in CI. Pair with schema-qualified names so you do not drop the wrong patients in another schema.

Audit trail

Some compliance regimes require proof of retention or erasure, log the drop event with actor, ticket id, and backup snapshot id.

⚡ Advanced / dialect note

On PostgreSQL, DROP TABLE takes an ACCESS EXCLUSIVE lock, brief but blocking; on huge catalog metadata, still coordinate with application deploy windows.

Order of operations

Drop or migrate foreign keys from children before parents; migration tools should encode this DAG, not rely on CASCADE in production unless the team signs the risk register.

TL;DR

DROP TABLE removes a table definition and, depending on options, its rows, indexes, and dependent objects. CASCADE follows foreign-key and view dependencies, convenient in dev, dangerous in prod. Prefer DROP IF EXISTS only in idempotent scripts; always verify no application still references the table and that backups cover any data you might need later.