skills.rest
mariadb-corporation
mariadb-corporationOFFICIAL
Explore all skills by this author →
WebsiteGitHub

mariadb-truncate-table

Documents MariaDB-specific TRUNCATE TABLE syntax, privileges, and behavioral restrictions.

28|115|Updated Jan 28, 2025
Search Tags:#SQL#database#DDL#mariadb#foreign key#truncate table#auto_increment
One-click install
npx skills add https://github.com/mariadb-corporation/mariadb-docs --skill mariadb-truncate-table-mariadb-corporation
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: mariadb-truncate-table
Source: https://github.com/mariadb-corporation/mariadb-docs/tree/main/agent-skills/granular/statements/mariadb-truncate-table
Command: npx skills add https://github.com/mariadb-corporation/mariadb-docs --skill mariadb-truncate-table-mariadb-corporation

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve? TRUNCATE TABLE in MariaDB behaves differently from what most developers and LLMs assume: it requires the DROP privilege instead of DELETE, commits implicitly, resets AUTO_INCREMENT, skips ON DELETE triggers, and is rejected on FK-parent tables, system-versioned tables, and sequences. This Skill prevents those mistakes when writing or reviewing TRUNCATE statements. ## Core Features & Use Cases - Privilege and transaction semantics: Explains that TRUNCATE checks DROP (not DELETE), causes an implicit commit, and cannot be rolled back. - Object-type restrictions: Covers rejection on system-versioned tables (ER_VERS_NOT_SUPPORTED), sequences (ER_ILLEGAL_HA), views, and FK-parent tables (ER_TRUNCATE_ILLEGAL_FK), with workarounds like SET FOREIGN_KEY_CHECKS=0 and ALTER SEQUENCE ... RESTART. - MariaDB extensions: Documents the WAIT n | NOWAIT lock-wait clause and the Oracle-mode DROP STORAGE / REUSE STORAGE no-op syntax. - Use Case: When an agent suggests wrapping TRUNCATE in a transaction for rollback safety, this Skill corrects it to use DELETE inside the transaction instead, since TRUNCATE always commits implicitly. ## Quick Start Ask the AI to write or review a MariaDB TRUNCATE TABLE statement for your table and verify the required privileges and restrictions.

Frequently Asked Questions about mariadb-truncate-table

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I truncate a table in MariaDB?▼

Run TRUNCATE TABLE tbl_name, optionally with WAIT n or NOWAIT to control lock waiting. The TABLE keyword is optional, and the name can be schema-qualified as db_name.tbl_name. You need the DROP privilege, not DELETE.

What is the difference between DELETE and TRUNCATE in MariaDB?▼

DELETE is rollback-able DML requiring the DELETE privilege, fires ON DELETE triggers, and never resets AUTO_INCREMENT. TRUNCATE requires DROP, commits implicitly, resets AUTO_INCREMENT unconditionally, never fires triggers, and always reports 0 rows affected.

Can TRUNCATE TABLE be rolled back in MariaDB?▼

No. TRUNCATE TABLE causes an implicit commit like other DDL and cannot be rolled back; it also commits any prior uncommitted work in the same transaction. Use DELETE inside the transaction if rollback must remain possible.

Why does TRUNCATE TABLE fail with ER_TRUNCATE_ILLEGAL_FK?▼

InnoDB rejects truncating a table that is the parent in a non-self-referencing foreign key. Self-referencing FKs do not block it, and SET FOREIGN_KEY_CHECKS=0 for the session bypasses the check entirely.

Does TRUNCATE TABLE work on system-versioned tables or sequences in MariaDB?▼

No. System-versioned tables reject TRUNCATE with ER_VERS_NOT_SUPPORTED; use DELETE or DELETE HISTORY instead. Sequences reject it with ER_ILLEGAL_HA; use ALTER SEQUENCE ... RESTART to reset one.

Why does TRUNCATE TABLE always show 0 rows affected?▼

MariaDB implements TRUNCATE as a DDL-like drop-and-recreate rather than row-by-row deletion, so it always reports 0 rows affected regardless of actual row count. Treat the count as no information, not zero rows removed.

Related Skills

Explore More Software Engineering Skills→
sebauvraysebauvray

db-schema

Documents the exact MariaDB schema of the Toollab Laravel application including tables, constraints, and known inconsistencies.

Community
Intermediate
starlake-aistarlake-ai
209

freshness

Identify stale data across configured tables in data warehouses and ETL pipelines.

Official
Intermediate
MozurokMozurok
6

migration-safety-steward

Audits pending DDL migrations for production-unsafe patterns before they are applied.

Community
Advanced
yunqiqiliangyunqiqiliang
8

clickzetta-data-recovery

Recover dropped or modified ClickZetta Lakehouse tables using Time Travel and UNDROP.

Community
Advanced