CREATE TABLE in MariaDB
Last updated: 2026-06-01
This skill covers the delta between standard SQL CREATE TABLE and MariaDB's. It assumes the agent already knows standard CREATE TABLE name (col type, …). Everything below documents MariaDB-specific syntax, table options, column attributes, and behaviors.
Default context: Assume MariaDB 11.8 LTS (GA May 2025) unless the user specifies 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
| If the agent writes / suggests… |
…prefer the MariaDB form |
DROP TABLE x; CREATE TABLE x … |
CREATE OR REPLACE TABLE x … (atomic equivalent, no window where the table is missing) |
CHARSET = utf8 |
CHARSET = utf8mb4 — utf8 is an alias for the legacy 3-byte form (utf8mb3) and cannot store 4-byte characters (most emoji, some CJK) |
Manual created_at / updated_at audit columns |
CREATE TABLE … WITH SYSTEM VERSIONING — see the mariadb-system-versioned-tables skill |
ROW_FORMAT = COMPRESSED for "modern" disk savings |
That's the older InnoDB compressed row format. For new tables on modern InnoDB/Aria, prefer PAGE_COMPRESSED = 1 plus optional PAGE_COMPRESSION_LEVEL = N |
Generated column with STORED |
Works, but MariaDB's historical keyword is PERSISTENT. Both are accepted; PERSISTENT shows up in older code and in SHOW CREATE TABLE output |
| Foreign keys on a MyISAM / Aria table |
Silently parsed and discarded — only InnoDB enforces foreign keys |
IF NOT EXISTS on a table with a different schema |
Skips creation and emits a warning (not error); the existing table is left untouched even if its definition differs |
Wrapping CREATE TABLE in a transaction |
DDL triggers an implicit commit. There is no way to roll back a CREATE TABLE; rely on atomic DDL (below) for crash safety instead |
CREATE OR REPLACE
CREATE OR REPLACE TABLE t (id INT PRIMARY KEY);
- Atomic equivalent of
DROP TABLE IF EXISTS t; CREATE TABLE t ….
- Cannot be combined with
IF NOT EXISTS.
- Crash-safe but not fully atomic in the strict sense — recovery picks one side cleanly, but observers mid-statement may see the table missing. Plain
CREATE TABLE is fully atomic (since 10.6.1).
Temporal Tables — PERIOD FOR …
MariaDB supports three temporal forms via the PERIOD FOR clause:
-- System-versioned (transaction time, automatic history):
CREATE TABLE t (id INT, val INT) WITH SYSTEM VERSIONING;
-- Application-time period (business-valid time, application-managed):
CREATE TABLE t (
id INT, val INT,
valid_from DATE, valid_to DATE,
PERIOD FOR effective (valid_from, valid_to)
);
-- Bitemporal (both at once):
CREATE TABLE t (
id INT, val INT,
valid_from DATE, valid_to DATE,
PERIOD FOR effective (valid_from, valid_to)
) WITH SYSTEM VERSIONING;
For querying history, partitioning by SYSTEM_TIME, or removing old history, defer to the mariadb-system-versioned-tables skill.
Specific columns can be excluded from versioning:
CREATE TABLE t (
id INT,
login_counter INT WITHOUT SYSTEM VERSIONING
) WITH SYSTEM VERSIONING;
UPDATE behavior note: for timestamp-based system versioning, a new history row is created for every UPDATE that touches a versioned column, even if no column value actually changes. See mariadb-system-versioned-tables for details.
Column-Level MariaDB Extensions
| Clause |
Effect |
col VARCHAR(N) COMPRESSED |
Per-value zlib compression on the column (transparent to clients) |
col INT INVISIBLE |
Excluded from SELECT *; explicit SELECT col still works; INSERT without column list skips it |
DEFAULT (expr) |
Defaults can be any deterministic expression — DEFAULT (1+1), DEFAULT(NOW() + INTERVAL 1 YEAR), DEFAULT USER() |
col INT GENERATED ALWAYS AS (expr) PERSISTENT |
Stored generated column. PERSISTENT is MariaDB's keyword; STORED is also accepted as an alias. Use VIRTUAL (default) for computed-on-read |
col INT GENERATED ALWAYS AS (expr) VIRTUAL |
Computed-on-read generated column (no storage cost, evaluated per query) |
col VECTOR(N) (since 11.7) |
Fixed-length float vector column — see the mariadb-vector skill |
Index Definitions — MariaDB-Specific Options
INDEX idx_name (col1, col2) [index_type] [index_option …]
| Option |
Meaning |
IGNORED / NOT IGNORED (since 10.6) |
Optimizer pretends the index doesn't exist (still maintained on writes). Use to "soft-disable" an index before dropping it |
INVISIBLE / VISIBLE |
Optimizer ignores the index; DML still updates it |
COMMENT '…' |
Free-text annotation, visible in INFORMATION_SCHEMA.STATISTICS |
WITH PARSER name |
Fulltext indexes only — pluggable parser |
CLUSTERING=YES |
Spider engine only |
Keywords INDEX and KEY are interchangeable in MariaDB.
Table Options — MariaDB-Specific or Often-Confused
| Option |
Meaning |
ENGINE = InnoDB |
Default engine. Set globally via default_storage_engine. If an engine isn't installed, substitution happens silently unless sql_mode includes NO_ENGINE_SUBSTITUTION |
ENCRYPTED = YES |
Per-table at-rest encryption. Requires an encryption key-management plugin (e.g. file_key_management) and innodb_encrypt_tables. Combine with ENCRYPTION_KEY_ID = N |
PAGE_COMPRESSED = 1 |
InnoDB / Aria page-level compression (LZ4 / zstd / lzma / lz4hc / bzip2 / snappy, depending on which compression provider plugins are loaded). Distinct from ROW_FORMAT = COMPRESSED (older InnoDB zlib mechanism). Add PAGE_COMPRESSION_LEVEL = 0..9 |
ROW_FORMAT = … |
Per-engine row layout. InnoDB: COMPACT | DYNAMIC | COMPRESSED | REDUNDANT. Aria: PAGE | FIXED | DYNAMIC. MyISAM: FIXED | DYNAMIC | COMPRESSED |
SEQUENCE = 1 |
Makes this CREATE TABLE produce a sequence instead of a regular table. Prefer the dedicated CREATE SEQUENCE statement |
TRANSACTIONAL = 1 |
Crash-safe Aria. Aria-only; ignored on other engines |
IETF_QUOTES = YES |
Aria's IETF-style identifier quoting. Aria-only |
WITH SYSTEM VERSIONING |
Table-level versioning (see temporal section) |
UNION = (t1, t2, …) |
MERGE engine table list |
CONNECTION = '…' |
Connection string for FederatedX / CONNECT engines |
DATA DIRECTORY = '…' / INDEX DIRECTORY = '…' |
Place table data or indexes on a different filesystem. Absolute paths only |
Atomic DDL
- Plain
CREATE TABLE is atomic since 10.6.1 — crash recovery either has the table fully created or not at all.
CREATE OR REPLACE TABLE is crash-safe but not fully atomic — see above.- All DDL still triggers an implicit commit; there is no way to roll back a
CREATE TABLE from inside a transaction.
See Also
This page is: Copyright © 2026 MariaDB. All rights reserved.
1---2name: mariadb-create-table3description: "MariaDB-specific syntax and behavior for CREATE TABLE — atomic CREATE OR REPLACE, INVISIBLE / COMPRESSED columns, system-versioned and application-time PERIOD clauses, IGNORED indexes, table-level encryption, page compression, sequence and Aria table options, and the utf8/utf8mb4 trap. Use when writing, generating, or reviewing CREATE TABLE statements that target MariaDB."4---56# CREATE TABLE in MariaDB78*Last updated: 2026-06-01*910This skill covers the **delta between standard SQL `CREATE TABLE` and MariaDB's**. It assumes the agent already knows standard `CREATE TABLE name (col type, …)`. Everything below documents MariaDB-specific syntax, table options, column attributes, and behaviors.1112> **Default context:** Assume MariaDB **11.8 LTS** (GA May 2025) unless the user specifies 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.1314## What LLMs Often Miss1516| If the agent writes / suggests… | …prefer the MariaDB form |17|---|---|18| `DROP TABLE x; CREATE TABLE x …` | `CREATE OR REPLACE TABLE x …` (atomic equivalent, no window where the table is missing) |19| `CHARSET = utf8` | `CHARSET = utf8mb4` — `utf8` is an alias for the legacy 3-byte form (`utf8mb3`) and cannot store 4-byte characters (most emoji, some CJK) |20| Manual `created_at` / `updated_at` audit columns | `CREATE TABLE … WITH SYSTEM VERSIONING` — see the `mariadb-system-versioned-tables` skill |21| `ROW_FORMAT = COMPRESSED` for "modern" disk savings | That's the older InnoDB compressed row format. For new tables on modern InnoDB/Aria, prefer `PAGE_COMPRESSED = 1` plus optional `PAGE_COMPRESSION_LEVEL = N` |22| Generated column with `STORED` | Works, but MariaDB's historical keyword is `PERSISTENT`. Both are accepted; `PERSISTENT` shows up in older code and in `SHOW CREATE TABLE` output |23| Foreign keys on a MyISAM / Aria table | Silently parsed and discarded — only InnoDB enforces foreign keys |24| `IF NOT EXISTS` on a table with a different schema | Skips creation and emits a **warning** (not error); the existing table is left untouched even if its definition differs |25| Wrapping `CREATE TABLE` in a transaction | DDL triggers an implicit commit. There is no way to roll back a `CREATE TABLE`; rely on atomic DDL (below) for crash safety instead |2627## CREATE OR REPLACE2829```sql30CREATE OR REPLACE TABLE t (id INT PRIMARY KEY);31```3233- Atomic equivalent of `DROP TABLE IF EXISTS t; CREATE TABLE t …`.34- Cannot be combined with `IF NOT EXISTS`.35- Crash-safe but **not fully atomic** in the strict sense — recovery picks one side cleanly, but observers mid-statement may see the table missing. Plain `CREATE TABLE` *is* fully atomic (since 10.6.1).3637## Temporal Tables — `PERIOD FOR …`3839MariaDB supports three temporal forms via the `PERIOD FOR` clause:4041```sql42-- System-versioned (transaction time, automatic history):43CREATE TABLE t (id INT, val INT) WITH SYSTEM VERSIONING;4445-- Application-time period (business-valid time, application-managed):46CREATE TABLE t (47 id INT, val INT,48 valid_from DATE, valid_to DATE,49 PERIOD FOR effective (valid_from, valid_to)50);5152-- Bitemporal (both at once):53CREATE TABLE t (54 id INT, val INT,55 valid_from DATE, valid_to DATE,56 PERIOD FOR effective (valid_from, valid_to)57) WITH SYSTEM VERSIONING;58```5960For querying history, partitioning by `SYSTEM_TIME`, or removing old history, defer to the **`mariadb-system-versioned-tables`** skill.6162Specific columns can be excluded from versioning:6364```sql65CREATE TABLE t (66 id INT,67 login_counter INT WITHOUT SYSTEM VERSIONING68) WITH SYSTEM VERSIONING;69```7071> **UPDATE behavior note:** for timestamp-based system versioning, a new history row is created for every `UPDATE` that touches a versioned column, even if no column value actually changes. See `mariadb-system-versioned-tables` for details.7273## Column-Level MariaDB Extensions7475| Clause | Effect |76|---|---|77| `col VARCHAR(N) COMPRESSED` | Per-value zlib compression on the column (transparent to clients) |78| `col INT INVISIBLE` | Excluded from `SELECT *`; explicit `SELECT col` still works; `INSERT` without column list skips it |79| `DEFAULT (expr)` | Defaults can be any deterministic expression — `DEFAULT (1+1)`, `DEFAULT(NOW() + INTERVAL 1 YEAR)`, `DEFAULT USER()` |80| `col INT GENERATED ALWAYS AS (expr) PERSISTENT` | Stored generated column. `PERSISTENT` is MariaDB's keyword; `STORED` is also accepted as an alias. Use `VIRTUAL` (default) for computed-on-read |81| `col INT GENERATED ALWAYS AS (expr) VIRTUAL` | Computed-on-read generated column (no storage cost, evaluated per query) |82| `col VECTOR(N)` *(since 11.7)* | Fixed-length float vector column — see the `mariadb-vector` skill |8384## Index Definitions — MariaDB-Specific Options8586```sql87INDEX idx_name (col1, col2) [index_type] [index_option …]88```8990| Option | Meaning |91|---|---|92| `IGNORED` / `NOT IGNORED` *(since 10.6)* | Optimizer pretends the index doesn't exist (still maintained on writes). Use to "soft-disable" an index before dropping it |93| `INVISIBLE` / `VISIBLE` | Optimizer ignores the index; DML still updates it |94| `COMMENT '…'` | Free-text annotation, visible in `INFORMATION_SCHEMA.STATISTICS` |95| `WITH PARSER name` | Fulltext indexes only — pluggable parser |96| `CLUSTERING=YES` | Spider engine only |9798Keywords `INDEX` and `KEY` are interchangeable in MariaDB.99100## Table Options — MariaDB-Specific or Often-Confused101102| Option | Meaning |103|---|---|104| `ENGINE = InnoDB` | Default engine. Set globally via `default_storage_engine`. If an engine isn't installed, substitution happens silently unless `sql_mode` includes `NO_ENGINE_SUBSTITUTION` |105| `ENCRYPTED = YES` | Per-table at-rest encryption. Requires an encryption key-management plugin (e.g. `file_key_management`) and `innodb_encrypt_tables`. Combine with `ENCRYPTION_KEY_ID = N` |106| `PAGE_COMPRESSED = 1` | InnoDB / Aria page-level compression (LZ4 / zstd / lzma / lz4hc / bzip2 / snappy, depending on which compression provider plugins are loaded). **Distinct from `ROW_FORMAT = COMPRESSED`** (older InnoDB zlib mechanism). Add `PAGE_COMPRESSION_LEVEL = 0..9` |107| `ROW_FORMAT = …` | Per-engine row layout. InnoDB: `COMPACT \| DYNAMIC \| COMPRESSED \| REDUNDANT`. Aria: `PAGE \| FIXED \| DYNAMIC`. MyISAM: `FIXED \| DYNAMIC \| COMPRESSED` |108| `SEQUENCE = 1` | Makes this `CREATE TABLE` produce a sequence instead of a regular table. Prefer the dedicated `CREATE SEQUENCE` statement |109| `TRANSACTIONAL = 1` | Crash-safe Aria. Aria-only; ignored on other engines |110| `IETF_QUOTES = YES` | Aria's IETF-style identifier quoting. Aria-only |111| `WITH SYSTEM VERSIONING` | Table-level versioning (see temporal section) |112| `UNION = (t1, t2, …)` | MERGE engine table list |113| `CONNECTION = '…'` | Connection string for FederatedX / CONNECT engines |114| `DATA DIRECTORY = '…'` / `INDEX DIRECTORY = '…'` | Place table data or indexes on a different filesystem. Absolute paths only |115116## Atomic DDL117118- Plain `CREATE TABLE` is **atomic** since 10.6.1 — crash recovery either has the table fully created or not at all.119- `CREATE OR REPLACE TABLE` is **crash-safe but not fully atomic** — see above.120- All DDL still triggers an implicit commit; there is no way to roll back a `CREATE TABLE` from inside a transaction.121122## See Also123124- **`mariadb-system-versioned-tables`** — full system-versioning, application-time, bitemporal, querying history, partitioning, removing history125- **`mariadb-vector`** — `VECTOR(N)` columns and vector indexes126- Canonical reference on `mariadb.com/docs`, consult only for edge cases not covered here: <https://mariadb.com/docs/server/reference/sql-statements/data-definition/create/create-table>127128<sub>_This page is: Copyright © 2026 MariaDB. All rights reserved._</sub>
mariadb-corporation