What happens when ALTER TABLE ... DROP COLUMN targets a column a view depends on?

## The statement ```sql ALTER TABLE <table> DROP COLUMN <name> [ CASCADE | RESTRICT ] ``` Dropping a column removes it from the table definition and takes its stored values with it. It is one of the few genuinely destructive DDL operations that looks innocuous in a migration file, because unlike `DROP TABLE` it does not read as "delete everything" — yet it discards one column's worth of data across every row, irreversibly, and no backup taken *after* the migration will contain it. ## Two categories of related object It helps to separate objects that *belong to* the column from objects that *depend on* it. **Belonging** — dropped automatically, no keyword required: - indexes that include the column. PostgreSQL documents that indexes and table constraints involving the column are dropped automatically, and it drops the whole index even when the column is only one member of a composite index. That is a real surprise: dropping a rarely used column can silently remove the composite index that a hot query depended on. - the column's `DEFAULT`, its `NOT NULL`, and any `UNIQUE`/`CHECK` constraint written over that column alone. **Depending** — governed by `RESTRICT` / `CASCADE`: - views and materialized views whose definition names the column, - multi-column `CHECK` constraints and foreign keys that also cover other columns, - generated/computed columns whose expression reads it, - in some engines, routines and rules that reference it. ## RESTRICT: use the error as a tool `RESTRICT` — the standard's and PostgreSQL's default — refuses the statement and names the blocking object: ```sql CREATE VIEW v_headcount AS SELECT dept_code, COUNT(*) AS people FROM employees GROUP BY dept_code; ALTER TABLE employees DROP COLUMN dept_code RESTRICT; -- ERROR: cannot drop column dept_code ... because other objects depend on it -- DETAIL: view v_headcount depends on column dept_code ``` Running the restricted drop in a transaction you intend to roll back is a legitimate reconnaissance technique in engines with transactional DDL: you get the authoritative dependency list from the engine itself, rather than from a grep across the repository that will miss views created by hand years ago. ## CASCADE: what it really costs ```sql ALTER TABLE employees DROP COLUMN dept_code CASCADE; -- succeeds; v_headcount no longer exists ``` The view is **dropped**, not disabled or left in a broken state you could inspect and repair. Its definition is gone from the catalog. Whoever owned that view learns about it when their dashboard 500s, and reconstructing it means finding the SQL somewhere outside the database. This is why `CASCADE` on a column drop deserves the same suspicion as `CASCADE` on a table drop: it converts a failure at deploy time, when you are watching, into a failure at query time, when you are not. The disciplined sequence is: run with `RESTRICT`, read the dependency list, then for each dependent decide explicitly — rewrite the view without the column and `CREATE OR REPLACE` it, or drop it in its own reviewed statement — and only then drop the column with `RESTRICT` still in place, so that any dependency you missed still stops you. ## Finding dependents before you start Beyond the error message, the catalog can be queried directly. `information_schema.view_column_usage` lists which views reference which table columns, and `information_schema.constraint_column_usage` does the same for constraints; engines also expose richer native catalogs. None of these see the consumers that matter most — application code, saved reports, BI tools, downstream ETL — which is why a column drop is normally the *last* step of an expand-and-contract sequence rather than a standalone change: stop writing the column, stop reading it, observe for a period, then drop. ## Portability The `CASCADE`/`RESTRICT` keywords on `DROP COLUMN` are not universally supported; engines that omit them apply their own fixed rules, typically refusing when a foreign key covers the column and silently dropping single-column indexes. Some engines also make `DROP COLUMN` metadata-only, leaving the values on disk until the table is rewritten — which is a storage detail, but worth knowing before you assume a drop has made sensitive data unreachable. ## The reversibility point A rename is instantly reversible; a drop is not. When the goal is "this column should no longer be used", renaming it to something loud like `dept_code__deprecated_20260820` breaks exactly the readers that still depend on it — visibly, at once, with a trivial undo — and lets you drop it for real after an observation window.