How to DROP COLUMN in MariaDB

Galaxy Glossary

How do I drop a column in MariaDB without locking my table?

DROP COLUMN removes one or more columns from an existing MariaDB table.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

When should I use DROP COLUMN?

Use DROP COLUMN when a field is obsolete, storing redundant data, or replaced by another column. Removing it keeps schemas lean and queries fast.

What is the basic syntax?

The command is issued with ALTER TABLE, followed by one or more DROP COLUMN clauses.Options let you control locking and algorithm behavior.

How do I drop a single column?

ALTER TABLE Products DROP COLUMN stock;

This permanently deletes the stock column and its data.

Can I drop multiple columns at once?

ALTER TABLE Customers DROP COLUMN email, DROP COLUMN created_at;

Batch execution runs faster than two separate statements.

How do I avoid table locks?

ALTER TABLE Orders DROP COLUMN total_amount, ALGORITHM=INPLACE, LOCK=NONE;

INPLACE keeps the table writable; LOCK=NONE prevents read/write blocks.

What if other objects depend on the column?

DROP COLUMN fails if foreign keys, indexes, or generated columns rely on it.Remove those dependencies first or use CASCADE.

Best practices for production?

• Take backups before schema changes.
• Drop in off-peak hours or with minimal locking options.
• Verify application code no longer references the column.

Is there a reversible alternative?

You cannot UNDROP a column.Simulate a rollback with backup/restore or by creating a new column and migrating data instead of dropping.

Example: cleaning up legacy discount column

-- Remove no longer used discount field ALTER TABLE OrderItems DROP COLUMN discount, ALGORITHM=INPLACE, LOCK=NONE;

The table stays online while the obsolete discount column disappears.

Why How to DROP COLUMN in MariaDB is important

How to DROP COLUMN in MariaDB Example Usage


-- Remove the deprecated 'created_at' column and avoid locking
ALTER TABLE Customers 
    DROP COLUMN created_at,
    ALGORITHM=INPLACE,
    LOCK=NONE;

How to DROP COLUMN in MariaDB Syntax


ALTER TABLE table_name
    DROP [COLUMN] column_name
    [, DROP [COLUMN] column_name2 ...]
    [CASCADE | RESTRICT]
    [ALGORITHM={DEFAULT|INPLACE|COPY}]
    [LOCK={DEFAULT|NONE|SHARED|EXCLUSIVE}];

-- Ecommerce example
ALTER TABLE Products 
    DROP COLUMN stock,
    ALGORITHM=INPLACE,
    LOCK=NONE;

Common Mistakes

Omitting ALGORITHM and LOCK options. Default behavior can copy the entire table, causing long locks. Fix: specify ALGORITHM=INPLACE and LOCK=NONE when supported.
Forgetting dependent indexes or foreign keys. DROP COLUMN fails if dependencies exist. Fix: drop or alter those objects first or use CASCADE (MariaDB 10.3+).