DROP COLUMN
SQL · Working with Tables
Theory
ALTER TABLE ... DROP COLUMN permanently removes a column and all the data stored in it from every row. ALTER TABLE employees DROP COLUMN fax_number leaves the other columns and all rows untouched. Many engines accept DROP COLUMN IF EXISTS for re-runnable scripts.
Dependencies can block the drop: a view, index, foreign key, CHECK constraint or primary key that uses the column. Depending on the engine you must remove those first, or add CASCADE to drop dependent objects as well (PostgreSQL). Some engines only mark the column as unused and reclaim space later. Application code or reports that still select the column will start failing, so search for its uses before dropping. You cannot drop the last remaining column of a table.
Why we use it
Unused or obsolete columns confuse users, waste storage and can hold sensitive data that should not be kept. DROP COLUMN cleans them out of the schema for good.
When to use it
Use DROP COLUMN when a field is truly no longer needed and nothing depends on it. If you are unsure, rename it or stop writing to it first, and keep a backup. To remove rows instead of a column, use DELETE.
How it works
- ALTER TABLE employees DROP COLUMN fax_number
- Do views or constraints use it? Yes: Drop them first or use CASCADE. No: Continue.
- Remove the column definition
- Discard the stored values for every row
- Queries using the column now fail
Syntax
ALTER TABLE table_name
DROP COLUMN column;Example
ALTER TABLE employees DROP COLUMN fax_number;Key points
- Irreversible: data in that column is lost
- Dependent views, indexes and constraints must be handled first
- PostgreSQL supports CASCADE to drop dependents too
- Search code and reports for the column before dropping
- Use DELETE to remove rows; DROP COLUMN removes a column