DROP

DROP

DROP removes a database object and its definition from the catalog — not just its contents. When you run DROP TABLE orders, the engine deletes the table's rows, its data files on disk, every index built on it, every constraint (primary key, unique, check, foreign key), the privileges granted on it, and finally the row in the system catalog (pg_class / information_schema.tables) that made the name "orders" mean anything at all. After a successful DROP, the identifier orders no longer exists — a query against it fails with "relation does not exist," not "empty table." That is the whole point, and it is what separates DROP from its two cousins.

DROP vs TRUNCATE vs DELETE — the distinction that gets tested

All three "remove data," but they operate at different levels. DELETE is DML: it removes rows you select with a WHERE clause, writes each removed row to the transaction log (so it can be rolled back), and fires row-level triggers. TRUNCATE is DDL-flavored: it discards all rows at once by deallocating the table's data pages rather than deleting row by row, which is why it is fast and barely logged — but the empty table, its columns, and its indexes remain. DROP goes one level higher: it removes the table itself.

Question	DELETE	TRUNCATE	DROP
What is removed	Selected rows	All rows	The whole object (rows + definition)
Command class	DML	DDL (row-set reset)	DDL
Supports WHERE?	Yes	No	No
Speed on a big table	Slow (per-row)	Fast (drops pages)	Fast (drops the object)
Row triggers fired?	Yes	No	No
Identity/AUTO_INCREMENT	Not reset	Reset to seed**	Gone with the table
Table exists after?	Yes (empty of matches)	Yes (empty)	No
Logging	Full (each row)	Minimal	Minimal (metadata)
Rollback-able?	Yes	Engine-dependent*	Engine-dependent*
Lock taken	Row/range locks	Exclusive table lock	Exclusive table lock

*See the transactional-DDL section below: in PostgreSQL both TRUNCATE and DROP are transactional and roll back; in MySQL and Oracle they trigger an implicit commit and cannot.

**MySQL and SQL Server reset the identity counter automatically; PostgreSQL keeps the sequence unless you write TRUNCATE … RESTART IDENTITY.

Dependents force a choice: RESTRICT vs CASCADE

The interesting case is a table other objects point at — a view that selects from it, a foreign key in a child table, a sequence it owns. The SQL standard gives DROP two drop-behaviors for exactly this:

• RESTRICT — refuse the drop if any dependent exists. This is the default in PostgreSQL and Oracle (you rarely type the word). It is the safe choice: the engine protects you from unknowingly breaking a view or a referential constraint.
• CASCADE — drop the target and every object that depends on it, recursively. It succeeds where RESTRICT refuses, but it is the dangerous choice: it will silently delete views, drop foreign-key constraints, and remove dependent sequences.

A traced example

Suppose orders has three dependents: two views and a child table's foreign key.

CREATE VIEW v_recent  AS SELECT * FROM orders WHERE created_at > now() - interval '7 days';
CREATE VIEW v_summary AS SELECT status, count(*) FROM orders GROUP BY status;
CREATE TABLE shipments (
    id         bigint PRIMARY KEY,
    order_id   bigint REFERENCES orders(id)   -- foreign key into orders
);

Now attempt the drop, step by step (PostgreSQL):

1. DROP TABLE orders; → the engine checks the dependency graph, finds v_recent, v_summary, and the shipments FK, and — because RESTRICT is the default — aborts:

   ERROR:  cannot drop table orders because other objects depend on it
   DETAIL: view v_recent depends on table orders
           view v_summary depends on table orders
           constraint shipments_order_id_fkey on table shipments depends on table orders
   HINT:   Use DROP ... CASCADE to drop the dependent objects too.

   Nothing changes. The database is exactly as it was.
2. DROP TABLE orders CASCADE; → succeeds, and in one statement it removes:
   • orders itself (rows, indexes, constraints, privileges, catalog row);
   • v_recent and v_summary — the views are gone entirely, not just invalidated;
   • the shipments_order_id_fkey constraint — shipments survives, but the referential integrity link is silently dropped.

Notice what CASCADE did to shipments: the table stays, but the guarantee that every order_id matches a real order is gone, and no error told you. That is why "just add CASCADE to make the error go away" is a classic production accident.

IF EXISTS — idempotency for migrations

Plain DROP TABLE orders errors if orders is already gone. In a migration or teardown script that must be safe to re-run, that error aborts the whole batch. DROP TABLE IF EXISTS orders turns "not there" into a harmless notice instead of an error, so the statement is idempotent — running it once or five times leaves the same end state. This is why teardown and "reset the schema" migrations almost always use IF EXISTS: a half-applied earlier run must not block the retry.

Can a DROP be rolled back? It depends on the engine

This is the fact interviewers use to separate people who have read a manual from people who have shipped:

• PostgreSQL — DDL is transactional. A DROP issued inside BEGIN … ROLLBACK is fully undone; the table (with its data, indexes, and constraints) comes back as if nothing happened. Postgres versions DDL through the same MVCC catalog machinery as data, so you can wrap a risky migration in a transaction and abort it cleanly.

  BEGIN;
  DROP TABLE orders;   -- table appears gone within this transaction
  ROLLBACK;            -- table is fully restored, data intact

• MySQL and Oracle — DDL causes an implicit COMMIT. Issuing DROP TABLE orders first commits any open transaction and then performs the drop, which is itself immediately committed. There is no transaction left to roll back — the drop is permanent the instant it runs. (In MySQL this is true across the common InnoDB engine; MySQL 8's atomic DDL makes a single statement crash-safe but still not user-rollback-able.) Recovery means restoring from a backup or point-in-time recovery, not ROLLBACK.

So "wrap the DROP in a transaction to be safe" is sound advice on PostgreSQL and false comfort on MySQL/Oracle. Know which engine you are on before you type it.

Locking — a DROP is an exclusive operation

To remove an object, the engine must be sure nobody is using it, so DROP takes the strongest table lock: ACCESS EXCLUSIVE in PostgreSQL, an exclusive metadata lock in MySQL. That lock conflicts with every other lock, including plain reads. The practical consequence: a DROP queued behind one long-running transaction that still touches the table will block, and because it now holds (or waits for) that exclusive lock, every new query against the table piles up behind it. A careless DROP in business hours can stall all traffic on a table even though the drop itself is instant.

Pitfalls

• CASCADE deletes more than you think. It silently removes dependent views, sequences, and FK constraints. Before running it, list dependents (in Postgres, the RESTRICT error is that list — read it, don't just append CASCADE).
• Assuming DROP rolls back everywhere. On MySQL/Oracle it does not. There is no undo after the implicit commit.
• Privileges vanish with the object. Dropping and recreating a table does not restore the grants that were on the old one — you must re-grant. This bites teams who "drop and rebuild" a table and then get permission-denied errors from apps that worked yesterday.
• DROP DATABASE is the nuclear option. It removes every table, view, index, and routine at once, and most engines refuse while sessions are still connected. There is no per-object confirmation — one command, the whole database gone.
• Blocking under load. The exclusive lock plus a long-running reader can stall the table; schedule schema-destroying DDL for low-traffic windows.

When to reach for which

Choose by what should exist afterward, not by speed:

• DELETE — when you want to remove some rows by predicate, need triggers to fire, or need transactional undo of a partial change. It is the only one that takes a WHERE.
• TRUNCATE — when you want to empty a table fast and keep its structure (e.g. reloading a staging table nightly), and you are fine losing the identity counter and firing no row triggers.
• DROP — only when the object itself should cease to exist: retiring a feature's table, tearing down a schema, undoing a bad CREATE. If you will recreate the same table tomorrow with the same data, you wanted DELETE or TRUNCATE, not DROP.

Takeaways

• DROP removes the object and its catalog definition (data, indexes, constraints, privileges) — DELETE removes rows, TRUNCATE empties the table, DROP removes the table.
• Dependents force RESTRICT (default: refuse) vs CASCADE (drop dependents too — silently, so dangerous). Read the RESTRICT error before reaching for CASCADE.
• Use IF EXISTS for idempotent migrations that must survive re-runs.
• Rollback is engine-specific: PostgreSQL DROP is transactional and undoable; MySQL/Oracle implicitly commit, so it is immediate and irreversible — recovery is backup/PITR.

---

Sources: PostgreSQL documentation (DROP TABLE, dependency tracking, transactional DDL); MySQL Reference Manual (implicit commit for DDL, metadata locking, atomic DDL in 8.0); Oracle SQL Language Reference; SQL:2016 standard (RESTRICT/CASCADE drop behavior). Re-authored/Deepened for this guide.

🎯 STANDOUT elevation: Why / example / when-not / failure / panel / drills — DROP

Why this exists / the decision it encodes

DROP removes the object definition from the catalog (rows, indexes, constraints, privileges, name). It is not DELETE (row DML) or TRUNCATE (empty table, keep definition). RESTRICT vs CASCADE is the decision about dependents; transactional DDL is engine-dependent.

Worked example with numbers or traced SQL/FD

Dependents: v_recent, v_summary, shipments.order_id FK → orders
DROP TABLE orders;          -- RESTRICT default (PG): ERROR, nothing removed
DROP TABLE orders CASCADE;  -- drops views entirely; drops FK constraint; shipments table remains
                            -- but referential guarantee is silently gone
PG: BEGIN; DROP TABLE orders; ROLLBACK; -- table restored
MySQL/Oracle: DROP implies commit — no user ROLLBACK of the drop
IF EXISTS: idempotent migrations / teardown scripts

When NOT / named alternative

Prefer TRUNCATE when you need an empty table with the same schema. Prefer DELETE WHERE for selective row removal and triggers. Never type CASCADE to silence an error without listing dependents (\d+ / information_schema). On MySQL, do not "wrap DROP in a transaction for safety."

Failure mode / ops fingerprint / interview trap

Ops fingerprint: CASCADE dropped 12 reporting views in prod; or MySQL DROP during migration with no rollback path — restore from backup. Interview trap: claiming DROP is always transactional.

Domain judgment (K11 theory-bridge / K12 concurrency / K13 query-judgment)

K12/ops: DDL transactional semantics differ by engine — catastrophic if assumed universal. K13: CASCADE is a dependency-graph operation, not a data delete.

Hostile-panel drills (with model answers)

Q1. DELETE vs TRUNCATE vs DROP — one line each.
Model answer: DELETE: remove selected rows (DML, logged, triggers). TRUNCATE: deallocate all rows, keep table. DROP: remove table definition and all dependents per behavior.

Q2. What does CASCADE do to a child FK?
Model answer: It drops the foreign-key constraint (and may cascade further); the child table can survive without the integrity link — a silent correctness loss.

Q3. Is DROP rollback-safe?
Model answer: In PostgreSQL yes inside a transaction. In MySQL/Oracle DDL auto-commits — DROP is immediate and permanent without backup/PITR.

I just read a lesson on **DROP** (Databases) and want to truly understand it. Explain DROP from first principles using ONE vivid real-world analogy and a visual mental model — draw it as ASCII art or a clear step-by-step diagram — with a concrete example using real numbers. Then ask me one question to check I got the mental picture, and wait for my reply. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Teach me **DROP** interactively. Ask me ONE guiding question at a time, wait for my answer, and adapt to my confusion — build the idea with me step by step instead of explaining it all at once. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Quiz me on **DROP** with 5 questions, easy to tricky, ONE at a time. Tell me if each answer is right; at the end, explain clearly what I got wrong and why. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Help me remember **DROP** for the long term: give the one-sentence intuition, a memorable hook/mnemonic, a tiny worked example, and 3 active-recall flashcards (Q -> A). If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.