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 is not reset by this statement. A table held for reading by an open transaction of another session, or with uncommitted writes, conflicts with TRUNCATE (error 1213, without waiting).

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, INVISIBLE columns.

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.

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 <base>/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.

See also#

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