In DROP TABLE, what does CASCADE do that RESTRICT does not?

## What DROP TABLE removes `DROP TABLE <name>` removes the table's definition and every row in it. It also removes the objects that belong to the table and have no independent existence: its indexes, its triggers, and the constraints declared on it, including foreign keys it declares *outward* to other tables. There is no undo at the statement level, and in engines where DDL implicitly commits, no surrounding transaction to roll back either. What `DROP TABLE` cannot silently remove are objects owned elsewhere that *point at* this table. Those are the dependencies the drop-behaviour keyword governs. ## RESTRICT and CASCADE SQL-92 made the drop behaviour mandatory: `DROP TABLE <name> CASCADE | RESTRICT`. In practice engines relaxed that and supply a default. - `RESTRICT` — refuse the drop if any other object depends on the table. Typical dependents are views and materialized views defined over it, foreign key constraints in other tables that reference its key, and (engine-dependent) routines or generated columns that reference it. The error message names the blocking object, which makes `RESTRICT` a useful *discovery* tool: run the drop, read the list, decide deliberately. - `CASCADE` — drop those dependent objects as part of the same statement. The view disappears. The foreign key constraint in `orders` disappears. Anything that depended on the dropped view disappears in turn, recursively. ```sql -- customers is referenced by orders.customer_id and by a reporting view DROP TABLE customers RESTRICT; -- ERROR: cannot drop table customers because other objects depend on it DROP TABLE customers CASCADE; -- customers is gone; v_customer_revenue is gone; -- the foreign key on orders is gone; every row of orders is still there ``` ## The misconception worth naming `CASCADE` in `DROP TABLE` is **not** `ON DELETE CASCADE`. They share a keyword and nothing else: | | when it acts | what it removes | |---|---|---| | `ON DELETE CASCADE` on a foreign key | DML — when a parent row is deleted | child **rows** referencing that parent | | `DROP TABLE ... CASCADE` | DDL — when the table is dropped | dependent **schema objects** (views, the FK constraint itself) | So dropping a parent table with `CASCADE` leaves the child table full of rows whose references now point at nothing — and, because the constraint that enforced the reference has been dropped too, nothing complains. That orphaned state is exactly why `CASCADE` deserves suspicion in a migration script: it converts a loud failure into a silent structural change. ## IF EXISTS `DROP TABLE IF EXISTS staging_orders` turns "table does not exist" from an error into a no-op (usually with a notice). It makes teardown and re-run scripts idempotent, which is why it is near-universal in test fixtures and in the down side of migrations. It says nothing about dependencies — `IF EXISTS` and the drop behaviour are orthogonal, and a statement may carry both: `DROP TABLE IF EXISTS customers CASCADE`. It is also a small hazard in a migration: `IF EXISTS` will happily do nothing when you have mistyped the table name, so a migration that "succeeded" may have changed nothing at all. ## Multiple tables in one statement Most engines accept a list — `DROP TABLE a, b, c` — which is more than a convenience: dropping mutually referencing tables in one statement sidesteps the ordering problem where each is blocked by the other's foreign key. Without that, drop in reverse dependency order (children before parents). ## Portability The drop-behaviour keywords are the least portable part of this statement. PostgreSQL implements both and defaults to `RESTRICT`. MySQL parses `RESTRICT` and `CASCADE` for compatibility but does nothing with them, and it refuses to drop a table that is the parent of a foreign key unless the referential checks are disabled — so writing `CASCADE` there gives you a false sense of having asked for something. SQLite has no drop-behaviour keywords at all. Because of that spread, the defensive habit is: never write `CASCADE` into a migration to make an error disappear. Read the dependency list, drop each dependent object explicitly in its own statement, and let `RESTRICT` (or the engine's refusal) stay as the tripwire it is meant to be. ## Practical guidance For a table you believe is dead, the low-risk sequence is to revoke access or rename it first, wait an observation period, and only then drop it — a rename is instantly reversible, a drop is not. When you do drop, name the dependents explicitly and let the statement fail if reality differs from your expectation.