Mariadb Alter Table

ALTER TABLE in MariaDB

Last updated: 2026-06-02

This skill covers the delta between standard SQL ALTER TABLE and MariaDB's. It assumes the agent already knows the standard form (ALTER TABLE name ADD/DROP/MODIFY …). Everything below documents MariaDB-specific syntax, lock/algorithm behavior, and online-DDL nuances. For column attributes and index options themselves, defer to the mariadb-create-table skill — both statements share them.

Default context: Assume MariaDB 11.8 LTS (GA May 2025) unless the user states another version. Anything available in current LTS branches (10.6, 10.11, 11.4, 11.8) is shown without a "since" annotation; only newer-than-10.6 features carry one.

What LLMs Often Miss

Online DDL — ALGORITHM and LOCK

ALTER TABLE t … , ALGORITHM=…, LOCK=…;

ALGORITHM values

alter_algorithm (and the older old_alter_table) system variable was removed in 11.5 — set ALGORITHM=… explicitly per statement if you need to override.

LOCK values

ALTER ONLINE TABLE shorthand

ALTER ONLINE TABLE t ADD COLUMN c INT;
-- equivalent to:
ALTER TABLE t ADD COLUMN c INT, LOCK=NONE;

Fails with an error if the operation can't actually run with LOCK=NONE. Use this when you want the migration to refuse rather than silently block writers.

WAIT / NOWAIT — Lock Timeout per Statement

ALTER TABLE t WAIT 5 ADD COLUMN c INT;     -- wait up to 5s for MDL, then ER_LOCK_WAIT_TIMEOUT
ALTER TABLE t NOWAIT ADD COLUMN c INT;     -- fail immediately if MDL can't be acquired

Bounds how long the statement will wait for the metadata lock. Indispensable for safe migration tooling — without these the statement may queue indefinitely behind a long-running transaction. Applies to many other statements too (SELECT … FOR UPDATE WAIT N, LOCK TABLES … NOWAIT, etc.).

IF EXISTS / IF NOT EXISTS on Subclauses

MariaDB allows IF EXISTS / IF NOT EXISTS on individual subclauses, not just on the table:

ALTER TABLE t
  ADD COLUMN IF NOT EXISTS c INT,
  ADD INDEX IF NOT EXISTS idx_c (c),
  DROP COLUMN IF EXISTS legacy_col,
  DROP FOREIGN KEY IF EXISTS fk_old;

When the condition isn't met (column already exists, column doesn't exist, …), the statement emits a warning and skips that subclause, continuing to the next. This is how idempotent migrations are written in MariaDB. Supported subclauses: ADD COLUMN, ADD INDEX, ADD FOREIGN KEY, ADD PARTITION, CREATE INDEX, DROP COLUMN, DROP INDEX, DROP FOREIGN KEY, DROP PARTITION, CHANGE COLUMN, MODIFY COLUMN.

ALTER TABLE IF EXISTS t … (on the table itself) emits a warning if the table is missing rather than erroring.

System Versioning Operations

ALTER TABLE t ADD SYSTEM VERSIONING;     -- start versioning an existing table
ALTER TABLE t DROP SYSTEM VERSIONING;    -- stop versioning; DELETES ALL HISTORY
ALTER TABLE t ADD PERIOD FOR effective (valid_from, valid_to);   -- application-time period

Trap: any other ALTER on an already-system-versioned table requires system_versioning_alter_history = KEEP (session-level), otherwise the statement errors out. Setting this preserves existing history rows under the new schema — querying historical data may return rows that don't match the new column definitions, which is documented behavior.

See the mariadb-system-versioned-tables skill for the full versioning model.

DROP CONSTRAINT

ALTER TABLE t DROP CONSTRAINT c_name;

Single statement drops any kind of constraint by name — check constraints, named foreign keys, named unique constraints. Use this rather than older form-specific clauses like DROP CHECK.

FORCE — Rebuild the Table

ALTER TABLE t FORCE;

Rebuilds the table without changing its definition. Two main use cases:

• Reclaim space after large DELETE operations (requires innodb_file_per_table=ON, the default).
• Re-validate data under a stricter sql_mode — e.g. setting NO_ZERO_DATE and then FORCE will surface existing rows that violate the new mode.

Partition Operations

Standard SQL partition-management clauses are supported. MariaDB-specific or version-specific points:

For partitioning by SYSTEM_TIME (auto-rotating history partitions for system-versioned tables), see the mariadb-system-versioned-tables skill.

DISCARD / IMPORT TABLESPACE — InnoDB Transportable Tablespaces

-- On the source server:
FLUSH TABLES t FOR EXPORT;     -- creates t.cfg alongside t.ibd
-- copy t.ibd and t.cfg to the destination datadir
-- On the destination:
ALTER TABLE t DISCARD TABLESPACE;
-- ...place the .ibd file in the destination's datadir...
ALTER TABLE t IMPORT TABLESPACE;

InnoDB-only. Aria, MyISAM, etc. don't need this — they pick up data files placed in the datadir directly.

Atomic ALTER and Replication

• ALTER TABLE is atomic for InnoDB, MyRocks, MyISAM, and Aria — crash recovery leaves either the old or the new table intact, never partial #sql-* files. (Since 10.6.)
• DDL still triggers an implicit commit; no transactional rollback of an ALTER.
• Set binlog_alter_two_phase=ON (since 10.8) to start replicating ALTER when it begins on the primary rather than when it finishes. Eliminates the replication lag of a long-running schema change.

See Also

• mariadb-create-table — column definitions, index definitions, and table options (shared syntax surface)
• mariadb-system-versioned-tables — versioning model, system_versioning_alter_history, history-row semantics
• Canonical references on mariadb.com/docs, consult only for edge cases not covered here: https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table and https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table/online-schema-change

This page is: Copyright © 2026 MariaDB. All rights reserved.