5. Data Definition Language (DDL)

This chapter describes the statements that create, modify, and drop MIRAJ databases, tables, indexes, and views. For the list and ranges of data types, see Data Types.

CREATE DATABASE#

CREATE DATABASE [IF NOT EXISTS] nom_base;
CREATE SCHEMA [IF NOT EXISTS] nom_base;

SCHEMA is a synonym for DATABASE. Any character set options (CHARACTER SET, COLLATE) are accepted by the parser but have no effect.

CREATE DATABASE IF NOT EXISTS gestium;

DROP DATABASE#

DROP DATABASE [IF EXISTS] nom_base;
DROP SCHEMA [IF EXISTS] nom_base;

CREATE TABLE#

Full definition#

CREATE [OR REPLACE] [TEMPORARY | GLOBAL TEMPORARY] TABLE [IF NOT EXISTS] [base.]table (
    définition_colonne [, définition_colonne ...]
    [, PRIMARY KEY (colonne [, ...])]
    [, [CONSTRAINT [nom]] UNIQUE [KEY | INDEX] [nom] (colonne [, ...])]
    [, {INDEX | KEY} [nom] (colonne [, ...])]
    [, [CONSTRAINT [nom]] FOREIGN KEY [nom] (colonne [, ...])
         REFERENCES [base.]table_ref (colonne [, ...])
         [ON DELETE action] [ON UPDATE action]]
    [, [CONSTRAINT [nom]] CHECK (expression) [[NOT] ENFORCED]]
) [AUTO_INCREMENT = n] [ENGINE = ...] [CHARSET = ...] [COLLATE = ...] [COMMENT = '...'];

Where définition_colonne is written:

colonne type
    [NOT NULL | NULL]
    [DEFAULT valeur | DEFAULT (expression) | DEFAULT fonction(...)]
    [AUTO_INCREMENT]
    [UNIQUE [KEY]] [PRIMARY KEY]
    [COMMENT 'texte']
    [ON UPDATE CURRENT_TIMESTAMP[(n)]]
    [[CONSTRAINT [nom]] CHECK (expression)]
    [REFERENCES table_ref (colonne) [ON DELETE action] [ON UPDATE action]]
    [GENERATED ALWAYS AS (expression) [VIRTUAL | STORED | PERSISTENT]]

A realistic example:

CREATE TABLE client (
    id            INT PRIMARY KEY AUTO_INCREMENT,
    code          VARCHAR(20) NOT NULL,
    nom           VARCHAR(100) NOT NULL,
    email         VARCHAR(255),
    plafond       DECIMAL(12,2) NOT NULL DEFAULT 0,
    cree_le       DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (code),
    CHECK (plafond >= 0)
);

CREATE TABLE commande (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    client_id   INT NOT NULL,
    montant     DECIMAL(12,2) NOT NULL,
    statut      ENUM('nouvelle', 'expediee', 'annulee') NOT NULL DEFAULT 'nouvelle',
    KEY idx_statut (statut),
    FOREIGN KEY (client_id) REFERENCES client (id)
        ON DELETE RESTRICT ON UPDATE CASCADE
);

Table clauses#

Non-unique secondary indexes#

CREATE TABLE mouvement (
    id        INT PRIMARY KEY AUTO_INCREMENT,
    article   VARCHAR(20) NOT NULL,
    date_mvt  DATE NOT NULL,
    KEY idx_article (article)
);

CREATE INDEX idx_date ON mouvement (date_mvt);

The readme.txt file shipped with earlier versions still lists non-unique secondary indexes as unavailable. This is now inaccurate: KEY / INDEX in CREATE TABLE, CREATE INDEX, and ALTER TABLE ... ADD INDEX do create a secondary index, with an unlimited number per table.

These indexes are hash indexes (like primary key and UNIQUE indexes), rebuilt when the database is opened. They serve only an equality condition covering all of their columns (integers, decimals, strings, dates; never floats); they are never used for IN, a range, a prefix of their columns, or an ORDER BY. A VIRTUAL generated column may be included (see Data Types). The USING, COMMENT, and VISIBLE / INVISIBLE options are accepted with no effect. A UNIQUE index identical to an existing uniqueness index is ignored.

Foreign keys#

CREATE TABLE ligne_commande (
    id           INT PRIMARY KEY AUTO_INCREMENT,
    commande_id  INT NOT NULL,
    article_id   INT NOT NULL,
    quantite     INT NOT NULL,
    FOREIGN KEY (commande_id) REFERENCES commande (id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (article_id) REFERENCES article (id)
        ON DELETE RESTRICT ON UPDATE RESTRICT
);

Referential actions recognized by the parser: RESTRICT, CASCADE, SET NULL, NO ACTION, SET DEFAULT. Only the first four are actually applied: SET DEFAULT is parsed but rejected when the key is created with error 1215 (ER_CANNOT_ADD_FOREIGN).

The referenced table must be in the same database (otherwise: unsupported feature, error 1235).

CREATE TABLE ... LIKE#

CREATE TABLE archive_client LIKE client;
CREATE TABLE archive_client (LIKE client);

Copies the source table: columns (types, default values, generated expressions), primary key, UNIQUE constraints, secondary indexes, CHECK constraints, and any partitioning. The following are not copied: the AUTO_INCREMENT counter (restarts from zero) and foreign keys. The new table is empty.

CREATE TABLE ... [AS] SELECT (CTAS)#

CREATE TABLE bilan_client [IF NOT EXISTS] AS
SELECT c.id, c.nom, SUM(cmd.montant) AS total
FROM client c
JOIN commande cmd ON cmd.client_id = c.id
GROUP BY c.id, c.nom;

The word AS is optional; the query may be written in parentheses ((SELECT ...)) or not, and may draw on any source (table, view, join, UNION, subquery, aggregate, LIMIT).

What is taken from the source:

• the name (or alias) of each result column;
• the type of the expression: the declared type if the result column comes as-is from a table, otherwise the type that carries the values (a computed string, or one coming from a UNION or a view that makes it one, becomes LONGTEXT; an integer becomes INT or BIGINT depending on its width);
• the nullability of the result.

What is never taken: primary key, index, UNIQUE constraint, AUTO_INCREMENT, DEFAULT, generated column (which becomes an ordinary column holding the already computed value).

The statement returns the number of rows inserted (like an INSERT), not a result set. The source query is read in full (source tables locked for reading) before the table is created: it does not yet exist during the read, and a failure while populating leaves the table nonexistent.

CREATE OR REPLACE [TEMPORARY] TABLE#

CREATE OR REPLACE TABLE client (
    id   INT PRIMARY KEY AUTO_INCREMENT,
    nom  VARCHAR(100) NOT NULL
);

Equivalent to DROP TABLE IF EXISTS table followed by the corresponding CREATE TABLE: rows, indexes, keys, constraints, AUTO_INCREMENT counter, and triggers of the old table are lost.

• IF NOT EXISTS is forbidden with OR REPLACE (error 1221, ER_WRONG_USAGE), as for a view.
• The new definition is fully validated before the old table is dropped (columns, keys, indexes, constraints, default values — the definition is built in memory and then discarded for this check): an invalid definition leaves the old table intact.
• Whatever can only be verified at actual write time (file not written, rows from the ... [AS] SELECT rejected) occurs after the drop: the old table is then lost.
• A table referenced by another table's foreign key is still refused (error 3730), as is the name of a system database or an invalid table name (error 1103).
• A name already held by a view is never replaced by a table (error 1050).
• CREATE OR REPLACE TEMPORARY TABLE replaces only the session's temporary table, never a database table of the same name that it masks.
• Required privileges: CREATE and DROP on the table.

DROP TABLE#

DROP [TEMPORARY] TABLE [IF EXISTS] table [, table2 ...] [RESTRICT | CASCADE];

RESTRICT and CASCADE are accepted by the parser without changing the behavior (there is no cascading drop of the tables that reference it: see error 3730 above). DROP TEMPORARY TABLE drops only the session's temporary tables (error 1051 if it does not exist); an ordinary DROP TABLE first drops a session temporary table of the same name, and otherwise a database table.

TRUNCATE TABLE#

TRUNCATE [TABLE] table;

Empties the table (rows, associated LOB store) without going through the row-by-row journal as a DELETE does; the AUTO_INCREMENT counter restarts at 1 (unlike DELETE FROM table, which keeps it). A table held for reading by an open transaction of another session, or with uncommitted writes, conflicts with TRUNCATE (error 1213, without waiting). See also the section "TRUNCATE: details" at the end of the chapter.

ALTER TABLE#

ALTER TABLE [base.]table clause [, clause ...];

Multiple clauses separated by commas are applied all or nothing in the same statement, each on the state left by the preceding clauses (dropping then recreating a column or an index in a single statement does perform both).

Columns#

ALTER TABLE client ADD COLUMN telephone VARCHAR(20);
ALTER TABLE client ADD COLUMN IF NOT EXISTS telephone VARCHAR(20);
ALTER TABLE client ADD COLUMN a INT, ADD COLUMN b INT;
ALTER TABLE client DROP COLUMN telephone;
ALTER TABLE client DROP COLUMN IF EXISTS telephone;
ALTER TABLE client MODIFY COLUMN nom VARCHAR(150) NOT NULL;
ALTER TABLE client CHANGE COLUMN nom nom_complet VARCHAR(150) NOT NULL;
ALTER TABLE client RENAME COLUMN nom_complet TO nom;
ALTER TABLE client ALTER COLUMN plafond SET DEFAULT 0;
ALTER TABLE client ALTER COLUMN plafond DROP DEFAULT;

MODIFY and CHANGE COLUMN redefine the entire column: an existing ON UPDATE CURRENT_TIMESTAMP clause is therefore lost if it is not rewritten in the new definition.

Type conversions under MODIFY / CHANGE: an existing value that no longer fits in the new type returns, in strict mode, the generic truncation error 1265 ("Data truncated for column... at row..."), and not the code specific to an INSERT / UPDATE (neither 1406 nor 1138). Adding a NOT NULL date/time column without a DEFAULT to a non-empty table returns error 1366 (no zero date).

Indexes and keys#

ALTER TABLE commande ADD PRIMARY KEY (id);
ALTER TABLE commande DROP PRIMARY KEY;
ALTER TABLE commande ADD UNIQUE (numero);
ALTER TABLE commande ADD UNIQUE KEY IF NOT EXISTS uq_numero (numero);
ALTER TABLE commande ADD INDEX idx_statut (statut);
ALTER TABLE commande ADD KEY IF NOT EXISTS idx_statut (statut);
ALTER TABLE commande DROP INDEX idx_statut;
ALTER TABLE commande DROP INDEX IF EXISTS idx_statut;
ALTER TABLE commande ADD FOREIGN KEY (client_id) REFERENCES client (id);
ALTER TABLE commande DROP FOREIGN KEY commande_ibfk_1;

The column order of an added primary key always follows the order of the table's columns (ADD PRIMARY KEY (b, a) produces PRIMARY KEY (a, b), not the order written in the clause); a UNIQUE on a single column of a composite primary key is ignored, as in CREATE TABLE.

A foreign key without an explicit name receives the generated name <table>_ibfk_<n> (visible in SHOW CREATE TABLE and in error messages that cite it, such as DROP FOREIGN KEY).

AUTO_INCREMENT#

ALTER TABLE commande AUTO_INCREMENT = 10000;

Sets or raises the next counter value; has no effect if used to lower it below the maximum already used.

RENAME#

ALTER TABLE commande RENAME TO commande_ancienne;
RENAME TABLE commande_ancienne TO commande_archivee, autre_table TO autre_nouveau;

RENAME TABLE (several pairs in one statement) checks all pairs before performing any renaming (errors 1146, 1050, 1103 depending on the case), then applies them one after the other. Renaming to another database is rejected (error 1235); renaming a table to its own name returns error 1050.

IF [NOT] EXISTS clauses: behavior when not applied#

ALTER TABLE commande
    ADD COLUMN IF NOT EXISTS x INT,
    DROP COLUMN IF EXISTS x_ancien;

Each conditional clause (ADD [COLUMN], DROP [COLUMN], ADD / DROP {INDEX | KEY}, ADD UNIQUE [KEY | INDEX], ADD PRIMARY KEY, ADD / DROP FOREIGN KEY, CHANGE, MODIFY) is evaluated on the state left by the preceding clauses of the same statement. A clause whose condition is not met (column already present for an ADD ... IF NOT EXISTS, column absent for a DROP ... IF EXISTS, etc.) is ignored and yields a note carrying the code of the underlying conflict (1060 duplicate column, 1061 duplicate index name, 1091 missing object, 1054 unknown column); the other clauses of the statement apply normally. If all clauses are thus ignored, the entire statement returns OK without error.

Options with no effect#

ENGINE, [DEFAULT] CHARSET / CHARACTER SET, COLLATE, COMMENT, CONVERT TO CHARACTER SET, ALGORITHM = ..., LOCK = ... are accepted but have no effect on storage. The following, however, are errors (1235, unsupported): ORDER BY, RENAME INDEX, ADD FULLTEXT / ADD SPATIAL. The INVISIBLE (or VISIBLE) keyword in a column definition is accepted but has no effect: the column stays visible (SELECT * returns it).

CREATE [UNIQUE] INDEX and DROP INDEX#

CREATE INDEX idx_nom ON client (nom);
CREATE UNIQUE INDEX uq_code ON client (code);
DROP INDEX idx_nom ON client;

CREATE INDEX without UNIQUE creates a non-unique secondary index (see the dedicated section above); CREATE UNIQUE INDEX is equivalent to ALTER TABLE ... ADD UNIQUE. Both forms accept the ALGORITHM = ... and LOCK = ... options with no effect. DROP INDEX nom ON table is equivalent to ALTER TABLE table DROP INDEX nom. CREATE INDEX IF NOT EXISTS does nothing (note 1061) if an index of that name already exists; USING BTREE / USING HASH are accepted with no effect before or after the table name.

Vector indexes (CREATE VECTOR INDEX, VECTOR INDEX in CREATE TABLE, and the form CREATE INDEX … USING hnsw (column class)) are described in chapter 19. Vector search.

Views#

CREATE [OR REPLACE] [CACHED] VIEW#

CREATE [OR REPLACE]
    [ALGORITHM = ...]
    [DEFINER = compte]
    [SQL SECURITY {DEFINER | INVOKER}]
    [CACHED] VIEW [IF NOT EXISTS] [base.]nom [(colonne [, ...])]
    AS requête
    [WITH [CASCADED | LOCAL] CHECK OPTION];

-- Ordinary view: always reflects the current state of the tables
CREATE VIEW v_client_actif AS
SELECT id, nom, email FROM client WHERE actif = 1;

-- Cached view: frozen until the next REFRESH VIEW
CREATE CACHED VIEW v_bilan_mensuel AS
SELECT DATE_FORMAT(date_mvt, '%Y-%m') AS mois, SUM(montant) AS total
FROM mouvement
GROUP BY DATE_FORMAT(date_mvt, '%Y-%m');

OR REPLACE and IF NOT EXISTS are mutually exclusive (error 1221). ALGORITHM and WITH CHECK OPTION are accepted by the parser but have no effect. Definitions are kept in <database>/views.mrv, durably written before the statement returns control.

Ordinary view or CACHED view#

A view is always read-only:

A view can be built on another view (up to 32 levels of nesting; a cycle returns error 1462).

REFRESH VIEW#

REFRESH VIEW v_bilan_mensuel;
REFRESH VIEW v_bilan_mensuel, v_autre_vue;

Applies only to a CACHED view: recomputes its result immediately and replaces the frozen version. Has no particular effect on an ordinary view (whose result, if kept, is already updated on its own).

ALTER VIEW#

ALTER VIEW v_client_actif AS
SELECT id, nom, email, telephone FROM client WHERE actif = 1;

Same syntax as CREATE VIEW (except IF NOT EXISTS): fully redefines the view.

DROP VIEW#

DROP VIEW [IF EXISTS] v_client_actif [, v_autre_vue ...] [RESTRICT | CASCADE];

SHOW CREATE VIEW#

SHOW CREATE VIEW v_client_actif;

Returns the canonical definition of the view, as recorded (including CACHED where applicable). information_schema.VIEWS lists the views of the current database; information_schema.COLUMNS describes only the views whose result is currently in memory.

Syntax supplements: CREATE TABLE#

This section supplements the previous ones: column attributes, table options, named constraints, temporary tables. Every statement has been verified against the engine.

Column attributes: what counts, what is ignored#

An unknown attribute keyword is a syntax error (1064). A column definition that repeats GENERATED is rejected.

Table options#

All options placed after the closing parenthesis are accepted by the parser (ENGINE, [DEFAULT] CHARSET, COLLATE, COMMENT, ROW_FORMAT, etc., with or without =) and ignored, with two exceptions:

• AUTO_INCREMENT = n sets the first number assigned (0 is ignored);
• PARTITION BY ... defines partitioning (see below).

SHOW CREATE TABLE therefore never returns the table's ENGINE, CHARSET, or COMMENT.

Named constraints#

In front of PRIMARY KEY, UNIQUE, FOREIGN KEY, and CHECK, CONSTRAINT name gives a name:

CREATE TABLE y (
    a INT, b INT,
    CONSTRAINT pk_y PRIMARY KEY (a),
    CONSTRAINT uq_b UNIQUE (b),
    CONSTRAINT ck_a CHECK (a > 0)
);

The primary key is always named PRIMARY (pk_y is ignored). The name of uq_b becomes that of the index (UNIQUE KEY uq_b). An unnamed CHECK constraint gets CONSTRAINT_<n>; CHECK names are unique across the whole database (error 3822 on a duplicate).

A CHECK violation returns error 4025:

INSERT INTO y VALUES (0, 1);
-- ERROR 4025 (23000): CONSTRAINT `ck_a` failed for `d`.`y`

A deviation to be aware of: a primary key declared both on a column (a INT PRIMARY KEY) and by a table-level PRIMARY KEY (b) clause is merged into a composite key (a, b) instead of returning error 1068; two table-level PRIMARY KEY clauses, or two PRIMARY KEY columns, do return 1068.

Temporary tables#

CREATE TEMPORARY TABLE [IF NOT EXISTS] [database.]t (...);
CREATE GLOBAL TEMPORARY TABLE [database.]t (...);      -- MIRAJ extension
DROP TEMPORARY TABLE [IF EXISTS] t;

• In memory only: no data file, no journal. Never listed by SHOW TABLES or by information_schema.
• A session temporary table hides a database table of the same name as long as it exists; DROP TABLE drops the temporary table first, then (failing that) the database table.
• CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE do not commit the current transaction; the rows, for their part, remain transactional.
• Privilege: CREATE TEMPORARY TABLES on the database (no further check for the session form); the GLOBAL form follows table privileges.
• The LIKE and [AS] SELECT forms are accepted as for an ordinary table.
• Refused: foreign key on or to a temporary table (error 1215), trigger (1361), partitioning (1562), system database.

Syntax supplements: ALTER TABLE#

Additional forms#

-- Several columns in parentheses
ALTER TABLE p ADD COLUMN (a INT, b INT DEFAULT 7);

-- Position
ALTER TABLE p ADD COLUMN z INT AFTER nom, ADD COLUMN y INT FIRST;

-- CHECK constraints
ALTER TABLE p ADD CONSTRAINT ck_q CHECK (qte >= 0);
ALTER TABLE p ALTER CHECK ck_q NOT ENFORCED;          -- MIRAJ extension
ALTER TABLE p ALTER CONSTRAINT ck_q ENFORCED;         -- synonym, MIRAJ extension
ALTER TABLE p DROP CONSTRAINT ck_q;                   -- or DROP CHECK ck_q

-- Uniqueness constraints and named keys
ALTER TABLE p ADD CONSTRAINT uq_nom UNIQUE (nom);
ALTER TABLE p DROP INDEX uq_nom;

-- Default value set to NULL
ALTER TABLE p ALTER COLUMN qte SET DEFAULT NULL;

Notes:

• DROP COLUMN col accepts a trailing RESTRICT or CASCADE, with no effect.
• ADD CHECK and any rebuild of the table re-read all rows to verify the constraint. A NOT ENFORCED constraint is kept but never evaluated.
• ADD / DROP VECTOR INDEX: see chapter 19. Vector search.
• The ENGINE = ..., ALGORITHM = ..., LOCK = ... options can be combined with other clauses: ALTER TABLE p ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE succeeds without changing anything.
• A table is rebuilt in memory and its file rewritten in full: allow for two in-memory copies for the modified columns.

Errors specific to ALTER TABLE#

Runnable example#

CREATE TABLE p (id INT PRIMARY KEY AUTO_INCREMENT, nom VARCHAR(10) NOT NULL, qte INT DEFAULT 5);
INSERT INTO p (nom) VALUES ('ab'), ('cd');

ALTER TABLE p ADD COLUMN z INT AFTER nom, ADD COLUMN y INT FIRST;
DESCRIBE p;

+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| y     | int(11)     | YES  |     | NULL    |                |
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| nom   | varchar(10) | NO   |     | NULL    |                |
| z     | int(11)     | YES  |     | NULL    |                |
| qte   | int(11)     | YES  |     | 5       |                |
+-------+-------------+------+-----+---------+----------------+

ALTER TABLE p ADD COLUMN z INT;
-- ERROR 1060 (42S21): Duplicate column name 'z'
ALTER TABLE p ADD COLUMN IF NOT EXISTS z INT;
-- OK, 0 row(s) affected; Warning: Duplicate column name 'z'
ALTER TABLE p MODIFY nom VARCHAR(1) NOT NULL;
-- ERROR 1265 (01000): Data truncated for column 'nom' at row 1
ALTER TABLE p DROP PRIMARY KEY;
-- ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

Partitioning#

Syntax#

CREATE TABLE table (...)
    PARTITION BY {
        [LINEAR] HASH (expression)
      | [LINEAR] KEY [ALGORITHM = {1 | 2}] ([column [, ...]])
      | RANGE (expression)
      | RANGE COLUMNS (column [, ...])
      | LIST (expression)
      | LIST COLUMNS (column [, ...])
    }
    [PARTITIONS n]
    [SUBPARTITION BY {[LINEAR] HASH (expression) | [LINEAR] KEY (...)} [SUBPARTITIONS n]]
    [(partition_definition [, ...])];

partition_definition:
    PARTITION name
        [VALUES {LESS THAN {(value [, ...]) | MAXVALUE} | IN (value | (value, ...) [, ...])}]
        [COMMENT [=] 'text'] [MAX_ROWS [=] n] [MIN_ROWS [=] n]
        [{DATA | INDEX} DIRECTORY [=] 'path'] [TABLESPACE [=] name] [[STORAGE] ENGINE [=] engine]
        [(SUBPARTITION name [options] [, ...])]

The clause is written after the table options, or in a versioned comment /*!50100 ... */ (this is how SHOW CREATE TABLE and miraj-dump return it). CREATE TABLE t PARTITION BY ... SELECT ... partitions a table filled by a query. CREATE TABLE ... LIKE copies the partitioning.

PARTITION BY SYSTEM_TIME (versioned tables) is not supported (error 1235).

Runnable example#

CREATE TABLE ventes (
    id INT NOT NULL, annee INT NOT NULL, montant DECIMAL(10,2),
    PRIMARY KEY (id, annee)
) PARTITION BY RANGE (annee) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION pmax  VALUES LESS THAN MAXVALUE
);
INSERT INTO ventes VALUES (1, 2023, 10), (2, 2024, 20), (3, 2030, 30);

SELECT PARTITION_NAME, PARTITION_METHOD, PARTITION_DESCRIPTION
FROM information_schema.PARTITIONS WHERE TABLE_NAME = 'ventes';

+----------------+------------------+-----------------------+
| PARTITION_NAME | PARTITION_METHOD | PARTITION_DESCRIPTION |
+----------------+------------------+-----------------------+
| p2023          | RANGE            | 2024                  |
| p2024          | RANGE            | 2025                  |
| pmax           | RANGE            | MAXVALUE              |
+----------------+------------------+-----------------------+

ALTER TABLE ventes DROP PARTITION p2023;      -- also deletes the partition's rows
SELECT id, annee FROM ventes ORDER BY id;     -- (2, 2024) and (3, 2030)
ALTER TABLE ventes TRUNCATE PARTITION pmax;   -- empties the partition

Partition management (ALTER TABLE)#

A partition management clause cannot be combined with other clauses in the same statement. On a non-partitioned table, these clauses return error 1505.

Rules and errors#

What depends on the edition#

• Express and Enterprise: the definition is checked, kept, and returned, but rows remain in the table's single file (no pruning or per-partition read). SELECT ... FROM t PARTITION (p), INSERT INTO t PARTITION (p), UPDATE / DELETE / LOAD DATA with PARTITION (p), EXCHANGE PARTITION, and TRUNCATE PARTITION of a HASH / KEY partition (or of a subpartition) return error 9002 (Cluster edition required); TRUNCATE PARTITION ALL, or listing all the partitions, remains allowed.
• Cluster: each partition (or subpartition) is a separate file segment (#p#<partition>.mrj), with read pruning and locks limited to the segments concerned. A table stored in segments is unreadable from another edition (error 1194). See Cluster and Replication.

TRUNCATE: details#

TRUNCATE [TABLE] [database.]table;

• The AUTO_INCREMENT counter restarts at 1:

  CREATE TABLE t (id INT PRIMARY KEY AUTO_INCREMENT, n INT);
  INSERT INTO t (n) VALUES (1), (2), (3);
  DELETE FROM t;                 -- keeps the counter
  INSERT INTO t (n) VALUES (9);  -- id = 4
  TRUNCATE TABLE t;              -- resets the counter to 1
  INSERT INTO t (n) VALUES (9);  -- id = 1

• TRUNCATE fires no DELETE trigger.
• A table referenced by another table's foreign key cannot be emptied by TRUNCATE, even if no row is referenced: error 1701. Use DELETE FROM or remove the foreign key first.
• TRUNCATE on a view: error 1347 ('database.view' is not BASE TABLE). Unknown table: 1146.
• On a partitioned table, TRUNCATE takes all segments (see the section "Partitioning").

Foreign keys: example and verified behaviors#

CREATE TABLE parent (id INT PRIMARY KEY AUTO_INCREMENT, nom VARCHAR(10));
CREATE TABLE enfant (
    id  INT PRIMARY KEY AUTO_INCREMENT,
    pid INT,
    FOREIGN KEY (pid) REFERENCES parent (id) ON DELETE CASCADE ON UPDATE CASCADE
);
INSERT INTO parent (nom) VALUES ('a'), ('b'), ('c');
INSERT INTO enfant (pid) VALUES (1), (1), (2);

INSERT INTO enfant (pid) VALUES (9);
-- ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
--   (`d`.`enfant`, CONSTRAINT `enfant_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `parent` (`id`)
--   ON DELETE CASCADE ON UPDATE CASCADE)

DELETE FROM parent WHERE id = 1;          -- also deletes the two child rows with pid = 1
UPDATE parent SET id = 20 WHERE id = 2;   -- the child row follows: pid = 20

DROP TABLE parent;
-- ERROR 3730 (HY000): Cannot drop table 'parent' referenced by a foreign key constraint
--   'enfant_ibfk_1' on table 'enfant'.
TRUNCATE parent;
-- ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (...)

ALTER TABLE enfant DROP FOREIGN KEY enfant_ibfk_1;
DROP TABLE parent;                        -- now allowed

• A NULL value in the foreign key column does not need a parent.
• The referenced table must have an index (primary key or UNIQUE) on the listed columns; otherwise creation fails (error 1822 "Missing index for constraint"). A referenced table that cannot be found returns 1824.
• A foreign key to a table in another database: error 1235.
• Child rows modified by a cascade fire no trigger, no ON UPDATE CURRENT_TIMESTAMP timestamp, and no CHECK check.

Unsupported syntaxes (DDL)#

These forms are recognized by the parser but refused with error 1235 ("This version of Miraj doesn't yet support ..."), unless otherwise stated:

See also#

• Data Types: details of the types, generated columns, AUTO_INCREMENT, CHECK constraints.