SQL DROP COLUMN

What is SQL DROP COLUMN?

Removes an existing column and all its data from a table.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL DROP COLUMN: PostgreSQL, MySQL (8.0+), MariaDB, SQL Server, Oracle, SQLite (3.35+), Snowflake, BigQuery. Minor syntax differences apply (e.g., SQL Server uses DROP COLUMN without IF EXISTS until SQL Server 2016).

SQL DROP COLUMN Full Explanation

SQL DROP COLUMN is a sub-command of ALTER TABLE that permanently deletes a column definition and every value stored in that column. Because the operation rewrites the underlying table metadata, it is irreversible without a backup. All dependent objects such as indexes, constraints, and views that reference the column must either be removed first or dropped automatically with the CASCADE option (where supported). Some engines rewrite the entire table to complete the operation, which can lock the table or require additional disk space. Always confirm that the column is no longer needed and that backups exist before executing DROP COLUMN.

SQL DROP COLUMN Syntax

ALTER TABLE table_name
    DROP COLUMN [IF EXISTS] column_name
    [CASCADE | RESTRICT];

SQL DROP COLUMN Parameters

- table_name (identifier) - Name of the target table.
- IF EXISTS (keyword, optional) - Drop only if the column is present; prevents an error when absent.
- column_name (identifier) - Name of the column to remove.
- CASCADE (keyword, optional) - Automatically drop objects that depend on the column.
- RESTRICT (keyword, optional) - Refuse to drop if any dependent objects exist (default in many dialects).

Example Queries Using SQL DROP COLUMN

-- Remove a single column
ALTER TABLE employees
    DROP COLUMN middle_name;

-- Drop a column only if it exists
ALTER TABLE sales
    DROP COLUMN IF EXISTS discount_rate;

-- Force removal plus dependent objects (PostgreSQL)
ALTER TABLE orders
    DROP COLUMN order_note CASCADE;

Expected Output Using SQL DROP COLUMN

The column definition disappears from the table
All data stored in that column is deleted
If CASCADE is used, dependent indexes, constraints, or views are also removed
Success returns an OK or completed message; no result set is produced

Use Cases with SQL DROP COLUMN

Cleaning up deprecated or redundant columns during schema refactoring.
Reducing table size after migrating data to a new structure.
Enforcing privacy by eliminating sensitive fields no longer required.
Simplifying schemas before exporting or open-sourcing a database.

Common Mistakes with SQL DROP COLUMN

Forgetting that the action is irreversible and losing historic data.
Omitting CASCADE in PostgreSQL, leading to an error when dependencies exist.
Expecting DROP COLUMN to be instantaneous on very large tables (it may lock or rewrite).
Using incorrect column name casing in case-sensitive databases.
Trying to drop a column that is part of a primary key or partitioning key without additional steps.

First Introduced In

SQL-92 (ALTER TABLE)

Frequently Asked Questions

What is the safest way to drop a column?

Back up the table, verify no application code relies on the column, and use IF EXISTS to avoid runtime errors. In PostgreSQL, test in a staging environment first.

Can I drop multiple columns at once?

Yes in most dialects. List each column separated by commas: `ALTER TABLE users DROP COLUMN age, DROP COLUMN nickname;`.

Will DROP COLUMN free disk space instantly?

Depends on the engine. Some reclaim space immediately; others wait until VACUUM or OPTIMIZE tasks run.

How do I see if a column has dependencies?

Query the system catalog (e.g., `pg_catalog.pg_depend` in PostgreSQL or `information_schema.key_column_usage` in MySQL) to locate constraints, indexes, or views that reference the column.