MariaDB SQL DDL

Definition statements that change the schema : CREATE, ALTER, DROP, RENAME, plus sequences, generated columns, invisible columns, and partitioning. Read this before any schema migration on a production-size table.

Quick Reference

• ALWAYS specify ALGORITHM= and LOCK= on every ALTER TABLE so the server fails fast instead of silently falling back to COPY.
• ALWAYS prefer ALGORITHM=INSTANT for ADD COLUMN (10.3+) and DROP COLUMN (10.4+) when the table allows it. Metadata-only change, no rewrite.
• ALWAYS use ALGORITHM=INPLACE, LOCK=NONE for adding non-INSTANT-able indexes on big tables.
• NEVER default to ALGORITHM=COPY on production-size tables : rebuilds the whole table, blocks DML in earlier versions. From 11.2+ COPY supports LOCK=NONE but still rewrites everything.
• NEVER add an INSTANT column to a ROW_FORMAT=COMPRESSED table or to a table with a FULLTEXT index : both reject INSTANT.
• ALWAYS choose VIRTUAL for generated columns unless you need an FK pointing at the column, replication of the computed value, or skip the recompute cost on heavy reads. PERSISTENT/STORED is synonyms but PERSISTENT is the canonical MariaDB keyword.
• ALWAYS use atomic RENAME TABLE old TO retired, new TO old; for blue-green deployments. Multi-table RENAME is atomic within a single statement (10.6+ atomic DDL).
• NEVER use ALTER VIEW and expect rebuild : views require CREATE OR REPLACE VIEW.
• Sequences are MariaDB-only (10.3+), Oracle/PostgreSQL-compatible syntax. MySQL has no sequences.
• Partitioned tables CANNOT have foreign keys and CANNOT be referenced by foreign keys : hard architectural boundary.
• Atomic DDL is rollback-safe on server crash (10.6+) for single-table DDL. Multi-table DROP is crash-safe but not fully atomic.

Decision Trees