How to Delete a Column in SQL

What you’ll build or solve

You’ll modify a table by removing one or more columns.

When this approach works best

Deleting a column works best when you:

• Remove unused or deprecated fields
• Clean up test columns added during development
• Refactor a schema to simplify data storage

For example, you might remove a middle_name column from users, drop a temporary_flag from orders, or delete a notes field that is no longer used.

This is a bad idea if existing application code, reports, or constraints still rely on that column. Removing a column is a permanent structural change.

Prerequisites

• Access to the database
• Permission to alter tables
• Basic SQL knowledge
• A backup if working in production

Step-by-step instructions

Step 1: Delete the column with ALTER TABLE

Use ALTER TABLE ... DROP COLUMN to remove a column.

Single column (MySQL, PostgreSQL, and SQL Server)

ALTERTABLE users
DROPCOLUMN middle_name;

Batch multiple columns (same operation)

MySQL or PostgreSQL (repeat DROP COLUMN):

ALTERTABLE users
DROPCOLUMN middle_name,
DROPCOLUMN nickname;

SQL Server (comma-separated names):

ALTERTABLE users
DROPCOLUMN middle_name, nickname;

What to look for

• The column and all its data are permanently removed
• Queries referencing the column will fail after the change
• Foreign keys, indexes, or constraints that reference the column must be removed first
• You cannot undo the drop without restoring from a backup
• Check column names before dropping to avoid mistakes:

DESCRIBE users;

Examples you can copy

Example 1: Remove a temporary column

You added a test column during development.

ALTERTABLE orders
DROPCOLUMN temporary_flag;

Example 2: Stop storing a field

You decide to remove middle names from user profiles.

ALTERTABLE users
DROPCOLUMN middle_name;

Example 3: Remove multiple deprecated fields

You are simplifying a table:

ALTERTABLE products
DROPCOLUMN old_price,
DROPCOLUMN legacy_code;

In SQL Server:

ALTERTABLE products
DROPCOLUMN old_price, legacy_code;

Common mistakes and how to fix them

Mistake 1: Dropping a column used in constraints

What you might do:

ALTERTABLE orders
DROPCOLUMN customer_id;

Why it fails: If customer_id is part of a foreign key or index, the database blocks the operation.

Correct approach: Drop the constraint first, then drop the column:

ALTERTABLE orders
DROPCONSTRAINT fk_orders_customer;

ALTERTABLE orders
DROPCOLUMN customer_id;

Mistake 2: Forgetting to update application code

What you might do: Drop the column but keep using it in queries or inserts.

Why it breaks: The database can no longer resolve the column name.

Correct approach: Remove references in your codebase and queries first, then deploy the database change.

Mistake 3: Dropping the wrong column

What you might do: Mistype the column name or confuse similar names.

Why it causes issues: You may delete important data permanently.

Correct approach: Inspect the table schema before dropping:

DESCRIBE users;

Then run the drop command with the exact column name.

Troubleshooting

• If you see “permission denied,” confirm you have ALTER privileges.
• If you see “cannot drop column because it is referenced,” drop related constraints or indexes first.
• If queries break after deletion, search your codebase for references to the removed column.
• If you dropped a column in production by mistake, restore from backup.

Quick recap

• Use ALTER TABLE ... DROP COLUMN to delete a column
• Batch multiple drops in one statement if your database supports it
• Remove dependent constraints first
• Update application code to avoid query failures
• Back up production data before structural changes