# Mariadb Rename Table > Provides MariaDB-specific syntax, atomicity, privilege requirements, and restrictions for RENAME TABLE statements, including multi-table swaps, temporary tables, and cross-database moves. - Skill: `mariadb-corporation/mariadb-rename-table` (Agent Skill) - Install (CLI): `npx skillmds add mariadb-corporation/mariadb-rename-table` - Raw SKILL.md: https://api.skillmd.com/api/skills/mariadb-corporation/mariadb-rename-table/raw - Safety review: PASS (external: skill-scanner PASS, skillspector PASS) - Works with: Claude Code, Claude.ai, OpenAI Codex - Category: Coding & Dev Tools - Tags: Atomic Rename, Database, Ddl, Mariadb, Rename Table, Sql, Table Swap - Author: mariadb-corporation (https://skillmd.com/u/mariadb-corporation) - Updated: 2026-08-22 - Page: https://skillmd.com/skills/mariadb-corporation/mariadb-rename-table --- # RENAME TABLE in MariaDB *Last updated: 2026-07-20* `RENAME TABLE` renames — and optionally moves — one or more tables or views in a single atomic statement. It is the primitive behind the well-known "instant" table-swap pattern, but it has sharper edges (transaction/lock restrictions, exact privilege set, temporary-table handling) than most LLMs assume by analogy with other systems. > **Default context:** Assume MariaDB **11.8 LTS** (GA May 2025) unless the user states another version. Features available across current LTS branches (10.6, 10.11, 11.4, 11.8) are 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 | |---|---| | "Temporary tables can't be renamed with `RENAME TABLE`; use `ALTER TABLE ... RENAME TO` instead." | In MariaDB, `RENAME TABLE t1 TO t2;` works directly on a `TEMPORARY` table — no `ALTER TABLE` needed. | | `RENAME TABLE` inside a `BEGIN … COMMIT` block, assuming it just runs like other DDL. | MariaDB refuses outright: issuing `RENAME TABLE` inside an active transaction (or while `LOCK TABLES` is in effect) raises `ER_LOCK_OR_ACTIVE_TRANSACTION` ("Can't execute the given command because you have active locked tables or an active transaction") rather than implicitly committing and proceeding. | | Granting only `ALTER` (or only `DROP`) and assuming that covers a rename. | The full requirement is **`ALTER` + `DROP`** on the source table/database and **`CREATE` + `INSERT`** on the target table/database — all four privileges, split across both names. | | A multi-pair `RENAME TABLE t1 TO t2, t3 TO t4` where, if `t3` is missing, assuming `t1 TO t2` already "took" and only the failing pair errors. | The whole statement is atomic: renames are applied left-to-right, but if any pair fails, **every** rename in the statement is rolled back, including ones that already succeeded. | | Emulating a table swap with `DROP`/temp-name juggling across several separate statements. | Do it in one atomic statement: `RENAME TABLE t1 TO tmp, t2 TO t1, tmp TO t2;` — safe because the whole thing is one all-or-nothing operation. | | `RENAME TABLE db1.t TO db2.t;` assumed to always work for moving any object between databases. | It fails for **views** (`ER_FORBID_SCHEMA_CHANGE`, "Changing schema … is not allowed") and for tables that have **triggers** (`ER_TRG_IN_WRONG_SCHEMA`, "Trigger in wrong schema") — cross-database moves are blocked in both cases. | | Treating `IF EXISTS` as unsupported on `RENAME TABLE` (only assumed valid on `DROP TABLE`/`CREATE TABLE`). | `RENAME TABLE IF EXISTS t1 TO t2, ...` is valid — a missing source table becomes a note/warning instead of an error, and the statement continues with the remaining pairs. | | Assuming `RENAME TABLE` has no way to control metadata-lock waiting. | Each pair can carry `WAIT n` or `NOWAIT` (e.g. `RENAME TABLE t1 NOWAIT TO t2;`), which sets the lock-wait timeout for that statement's metadata-lock acquisition. | | Assuming `RENAME TABLES` (plural) is a different statement that must be used for multi-table renames, or that it behaves differently from `RENAME TABLE`. | `TABLE`/`TABLES` are interchangeable — the presence or absence of the `S` has zero effect on behavior, single or multi-table. | ## Syntax ```sql RENAME TABLE[S] [IF EXISTS] tbl_name [WAIT n | NOWAIT] TO new_tbl_name [, tbl_name2 [WAIT n | NOWAIT] TO new_tbl_name2] ... ``` - `tbl_name` may be qualified as `db_name.tbl_name` on either side of `TO`, which moves the table to another database as part of the rename (subject to the view/trigger restrictions below). - Multiple `tbl_name TO new_tbl_name` pairs may be given, comma-separated, in one statement; they execute strictly left-to-right. - `IF EXISTS` suppresses the error (turning it into a warning) when a named source table does not exist. - `WAIT n | NOWAIT` sets the lock-wait timeout for acquiring the metadata lock needed for that pair's rename. - Do not confuse this with `RENAME USER old TO new [, ...]`, which shares the `RENAME` keyword but is a completely different statement (account management, not table DDL). ## Atomicity, Ordering, and the Swap Trick `RENAME TABLE` with multiple pairs is one atomic operation: no other session can see any of the tables mid-rename, pairs are applied in the order written, and if any pair fails, all renames performed earlier in the same statement are reverted. This is what makes the classic swap safe: ```sql CREATE TABLE new_table (...); -- populate new_table -- RENAME TABLE old_table TO backup_table, new_table TO old_table; ``` or, to swap two existing tables' names outright: ```sql RENAME TABLE t1 TO tmp_table, t2 TO t1, tmp_table TO t2; ``` For most storage engines (including InnoDB, MyRocks, MyISAM, and Aria) this atomicity extends to crash safety: if the server crashes mid-statement, all tables — and any trigger files touched — revert to their pre-statement names on recovery. ## Restrictions on When RENAME TABLE Can Run `RENAME TABLE` cannot be issued: - inside an active (explicit or already-started) transaction, or - while the session holds tables via `LOCK TABLES`. Both cases raise `ER_LOCK_OR_ACTIVE_TRANSACTION` immediately, before any table is touched — there is no implicit commit-and-continue behavior here, unlike some other DDL statements. ## Moving Tables Between Databases Qualifying either side with a database name moves the table: ```sql RENAME TABLE db1.t TO db2.t; ``` This requires the databases to be reachable by the storage engine (effectively, the same filesystem/tablespace for file-based engines). Two cases are explicitly blocked: - **Tables with triggers** cannot be moved to another database — MariaDB raises `ER_TRG_IN_WRONG_SCHEMA` ("Trigger in wrong schema", error 1435). Triggers on a table that stays within the same database are automatically updated to reference the new table name. - **Views** cannot be moved to another database at all — MariaDB raises `ER_FORBID_SCHEMA_CHANGE` ("Changing schema from 'old_db' to 'new_db' is not allowed", error 1450). Renaming a view within the same database works normally. ## Temporary Tables `RENAME TABLE` works directly on `TEMPORARY` tables — there is no need to fall back to `ALTER TABLE ... RENAME TO`. Temporary-table renames are handled separately from permanent-table renames internally (they are not written to the DDL recovery log, since temporary tables disappear on crash anyway) but use identical syntax. ## Privileges Executing `RENAME TABLE` requires: - **`ALTER`** and **`DROP`** on the source table (or its database), and - **`CREATE`** and **`INSERT`** on the target table (or its database). All four privileges are checked, split across the two names — granting only a subset of them is a common under-provisioning mistake. ## Contrast With ALTER TABLE ... RENAME TO `ALTER TABLE tbl_name RENAME TO new_name` renames a single table as one clause of a (possibly larger) `ALTER TABLE` statement, and can also move a table between databases. It does not offer the multi-pair, all-or-nothing semantics of `RENAME TABLE` — there is no equivalent to the comma-separated swap trick. Use `RENAME TABLE` when the goal is purely renaming/swapping one or more tables atomically; use `ALTER TABLE ... RENAME TO` when the rename is one of several changes being made to a table in the same statement. ## See Also - **`mariadb-alter-table`** — the single-table `RENAME TO` alternative, and the only way to rename columns/indexes in the same statement. - **`mariadb-create-trigger`** — why a table with triggers can't be moved across databases with `RENAME TABLE`. - Canonical references on `mariadb.com/docs`: - - - _This page is: Copyright © 2026 MariaDB. All rights reserved._