SQL guide · Tables, views & databases (DDL)

SQL DROP COLUMN: How to Remove a Column from a Table

Removing a column from an existing table is done with ALTER TABLE ... DROP COLUMN. The column and all the values stored in it are deleted, while the rest of the table stays intact. It is the right tool when a field is obsolete, was added by mistake, or has been moved to another table, and you want the schema to reflect that without rebuilding the table.

Dropping a column is a schema change with consequences. Queries, views, reports, stored procedures and application code that mention the column will start to fail, so search for usages before you run it. The data in that column is also lost for good unless you have a backup, so some teams first rename the column or stop writing to it, wait a release cycle, and only then drop it.

Databases protect you from some mistakes. A column that is part of a primary key, a foreign key, an index, a check constraint or a default constraint usually cannot be dropped until that dependency is removed or the system is told to cascade. The details differ by dialect, and the notes below cover the main ones. For other table changes, such as adding a column or changing a type, see the ALTER TABLE guide.

Basic syntax

ALTER TABLE table_name
DROP COLUMN column_name;

-- Several columns (PostgreSQL, MySQL)
ALTER TABLE table_name
  DROP COLUMN column1,
  DROP COLUMN column2;

Name the table in ALTER TABLE and the column in DROP COLUMN. Some dialects let you add IF EXISTS or CASCADE, and the keyword COLUMN is optional in MySQL and PostgreSQL but required in SQL Server.

SQL DROP COLUMN examples

Drop one column

This removes the fax_number column from customers. Every value stored in it is deleted, and any query that selects the column will fail afterward, so check dependencies first.

ALTER TABLE customers
DROP COLUMN fax_number;

Drop several columns in one statement

PostgreSQL and MySQL allow multiple DROP COLUMN clauses separated by commas. Doing it in one statement is faster than separate ones on a large table because the table is rewritten or processed once.

ALTER TABLE products
  DROP COLUMN legacy_code,
  DROP COLUMN old_price,
  DROP COLUMN notes;

Drop a column only if it exists

PostgreSQL supports IF EXISTS on the column, which keeps migration scripts from failing when run against a table that already lacks it. This is helpful for repeatable deployments.

ALTER TABLE employees
DROP COLUMN IF EXISTS middle_name;

Remove the blocking constraint first in SQL Server

SQL Server will not drop a column that has a default constraint. You drop the named constraint first and then the column. The constraint name can be found in the system catalog views if you did not name it yourself.

ALTER TABLE orders
DROP CONSTRAINT DF_orders_status;

ALTER TABLE orders
DROP COLUMN status;

Where SQL dialects differ

PostgreSQL: Supports IF EXISTS and CASCADE to also drop dependent views and constraints. The drop is fast because it marks the column as dropped rather than rewriting the table; space is reclaimed later.

MySQL: Supports multiple DROP COLUMN clauses. A column cannot be dropped if it is the only column in the table. In MySQL 8.0 and later, many drops use the INSTANT algorithm. IF EXISTS on a column is supported in MariaDB but not in MySQL.

SQL Server: Requires the keyword COLUMN. Constraints, defaults, indexes and statistics that depend on the column must be dropped first. IF EXISTS is supported from SQL Server 2016. Multiple columns can be listed after one DROP COLUMN.

SQLite: DROP COLUMN was added in version 3.35.0 (March 2021). It fails if the column is a primary key, is unique, is indexed, or is used in a view or trigger. On older versions you must recreate the table.

Oracle: Use ALTER TABLE t DROP COLUMN c, with CASCADE CONSTRAINTS to remove related constraints. For large tables, SET UNUSED marks the column invisible quickly and lets you drop it later.

Common mistakes

  • Dropping a column that a view, report or application still reads, causing errors elsewhere. Search the codebase and dependent objects first.
  • Assuming the data can be recovered. Once the column is dropped its values are gone unless you restore a backup.
  • Trying to drop a column that is a primary key, foreign key or indexed field without removing that constraint or index first.
  • Running the change on a very large production table during busy hours. Some dialects lock the table or rewrite it, so schedule it carefully.
  • Using DROP COLUMN where UPDATE ... SET col = NULL was intended. Clearing values keeps the column, while dropping removes it entirely.

Related SQL guides

Browse all SQL guides

Need help writing the SQL?

Describe your tables and the result you want, then review the generated query against your database before running it.

Try the AI SQL generator