5. Langage de définition des données (DDL)

Ce chapitre décrit les instructions qui créent, modifient et suppriment les bases, tables, index et vues de MIRAJ. Pour la liste et les plages des types de données, voir Types de données.

CREATE DATABASE#

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

SCHEMA est un synonyme de DATABASE. Les éventuelles options de jeu de caractères (CHARACTER SET, COLLATE) sont acceptées à l'analyse mais sans effet.

CREATE DATABASE IF NOT EXISTS gestium;

DROP DATABASE#

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

CREATE TABLE#

Définition complète#

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 = '...'];

Où définition_colonne s'écrit :

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]]

Exemple réaliste :

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
);

Clauses de table#

Index secondaires non uniques#

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);

Le fichier readme.txt livré avec des versions antérieures indique encore les index secondaires non uniques comme non disponibles. C'est désormais inexact : KEY / INDEX dans CREATE TABLE, CREATE INDEX et ALTER TABLE ... ADD INDEX créent bien un index secondaire, nombre illimité par table.

Ces index sont des index par hachage (comme les index de clé primaire et UNIQUE), reconstruits à l'ouverture de la base. Ils ne servent qu'une égalité portant sur la totalité de leurs colonnes (entiers, décimaux, chaînes, dates ; jamais les flottants) ; ils ne sont jamais utilisés pour IN, un intervalle, un préfixe de leurs colonnes, ni pour un ORDER BY. Une colonne générée VIRTUAL peut y figurer (voir Types de données). Les options USING, COMMENT, VISIBLE / INVISIBLE sont acceptées sans effet. Un index UNIQUE identique à un index d'unicité déjà présent est ignoré.

Clés étrangères#

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
);

Actions référentielles reconnues à l'analyse : RESTRICT, CASCADE, SET NULL, NO ACTION, SET DEFAULT. Seules les quatre premières sont réellement appliquées : SET DEFAULT est analysée mais refusée à la création de la clé avec l'erreur 1215 (ER_CANNOT_ADD_FOREIGN).

La table référencée doit se trouver dans la même base (sinon : fonctionnalité non prise en charge, erreur 1235).

CREATE TABLE ... LIKE#

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

Recopie de la table source : colonnes (types, valeurs par défaut, expressions générées), clé primaire, contraintes UNIQUE, index secondaires, contraintes CHECK et partitionnement éventuel. Ne sont pas recopiés : le compteur AUTO_INCREMENT (repart de zéro) et les clés étrangères. La nouvelle table est vide.

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;

Le mot AS est facultatif ; la requête peut être écrite entre parenthèses ((SELECT ...)) ou non, et porter sur n'importe quelle source (table, vue, jointure, UNION, sous-requête, agrégat, LIMIT).

Ce qui est repris de la source :

• le nom (ou l'alias) de chaque colonne du résultat ;
• le type de l'expression : le type déclaré si la colonne du résultat vient telle quelle d'une table, sinon le type qui porte les valeurs (une chaîne calculée, ou venue d'un UNION ou d'une vue qui en fait une, devient LONGTEXT ; un entier devient INT ou BIGINT selon sa largeur) ;
• la nullabilité du résultat.

Ce qui n'est jamais repris : clé primaire, index, contrainte UNIQUE, AUTO_INCREMENT, DEFAULT, colonne générée (qui devient une colonne ordinaire portant la valeur déjà calculée).

L'instruction rend le nombre de lignes insérées (comme un INSERT), pas un jeu de résultat. La requête source est lue en entier (tables sources verrouillées en lecture) avant que la table ne soit créée : elle n'existe pas encore pendant la lecture, et un échec du remplissage laisse la table inexistante.

CREATE OR REPLACE [TEMPORARY] TABLE#

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

Équivaut à DROP TABLE IF EXISTS table suivi du CREATE TABLE correspondant : lignes, index, clés, contraintes, compteur AUTO_INCREMENT et déclencheurs de l'ancienne table sont perdus.

• IF NOT EXISTS est interdit avec OR REPLACE (erreur 1221, ER_WRONG_USAGE), comme pour une vue.
• La nouvelle définition est entièrement vérifiée avant la suppression de l'ancienne table (colonnes, clés, index, contraintes, valeurs par défaut — la définition est montée en mémoire puis jetée pour ce contrôle) : une définition invalide laisse l'ancienne table intacte.
• Ce qui ne peut être vérifié qu'à l'écriture effective (fichier non écrit, lignes du ... [AS] SELECT refusées) survient après la suppression : l'ancienne table est alors perdue.
• Une table référencée par la clé étrangère d'une autre table reste refusée (erreur 3730), de même que le nom d'une base système ou un nom de table invalide (erreur 1103).
• Un nom déjà porté par une vue n'est jamais remplacé par une table (erreur 1050).
• CREATE OR REPLACE TEMPORARY TABLE ne remplace que la table temporaire de la session, jamais une table de la base de même nom qu'elle masque.
• Droits requis : CREATE et DROP sur la table.

DROP TABLE#

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

RESTRICT et CASCADE sont acceptés à l'analyse sans changer le comportement (il n'y a pas de suppression en cascade des tables qui la référencent : voir l'erreur 3730 ci-dessus). DROP TEMPORARY TABLE ne supprime que les tables temporaires de la session (erreur 1051 si elle n'existe pas) ; un DROP TABLE ordinaire supprime d'abord une table temporaire de la session de même nom, puis à défaut une table de la base.

TRUNCATE TABLE#

TRUNCATE [TABLE] table;

Vide la table (lignes, magasin de LOB associé) sans passer par le journal ligne à ligne comme un DELETE ; le compteur AUTO_INCREMENT n'est pas réinitialisé par cette instruction. Une table tenue en lecture par une transaction ouverte d'une autre session, ou dont des écritures ne sont pas validées, entre en conflit avec TRUNCATE (erreur 1213, sans attente).

ALTER TABLE#

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

Plusieurs clauses séparées par des virgules s'appliquent tout ou rien dans la même instruction, chacune sur l'état laissé par les clauses précédentes (supprimer puis recréer une colonne ou un index dans une seule instruction fait bien les deux).

Colonnes#

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 et CHANGE COLUMN redéfinissent la colonne entière : une clause ON UPDATE CURRENT_TIMESTAMP existante est donc perdue si elle n'est pas réécrite dans la nouvelle définition.

Conversions de type sous MODIFY / CHANGE : une valeur existante qui ne tient plus dans le nouveau type rend, en mode strict, l'erreur générique de troncature 1265 (« Data truncated for column... at row... »), et non le code propre à un INSERT / UPDATE (ni 1406, ni 1138). Ajouter une colonne date/heure NOT NULL sans DEFAULT à une table non vide rend l'erreur 1366 (pas de date zéro).

Index et clés#

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;

L'ordre des colonnes d'une clé primaire ajoutée suit toujours l'ordre des colonnes de la table (ADD PRIMARY KEY (b, a) produit PRIMARY KEY (a, b), pas l'ordre écrit dans la clause) ; un UNIQUE sur une seule colonne d'une clé primaire composée est ignoré, comme dans CREATE TABLE.

Une clé étrangère sans nom explicite reçoit le nom généré <table>_ibfk_<n> (visible par SHOW CREATE TABLE et dans les messages d'erreur qui la citent, comme DROP FOREIGN KEY).

AUTO_INCREMENT#

ALTER TABLE commande AUTO_INCREMENT = 10000;

Fixe ou relève le prochain compteur ; sans effet pour le baisser en dessous du maximum déjà utilisé.

RENAME#

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

RENAME TABLE (plusieurs paires en une instruction) vérifie toutes les paires avant d'effectuer le moindre renommage (erreurs 1146, 1050, 1103 selon le cas), puis les applique l'une après l'autre. Renommer vers une autre base est refusé (erreur 1235) ; renommer une table vers son propre nom rend l'erreur 1050.

Clauses IF [NOT] EXISTS : comportement en cas de non-application#

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

Chaque clause conditionnelle (ADD [COLUMN], DROP [COLUMN], ADD / DROP {INDEX | KEY}, ADD UNIQUE [KEY | INDEX], ADD PRIMARY KEY, ADD / DROP FOREIGN KEY, CHANGE, MODIFY) est jugée sur l'état laissé par les clauses précédentes de la même instruction. Une clause dont la condition n'est pas remplie (colonne déjà présente pour un ADD ... IF NOT EXISTS, colonne absente pour un DROP ... IF EXISTS, etc.) est ignorée et donne une note portant le code du conflit sous-jacent (1060 doublon de colonne, 1061 doublon de nom d'index, 1091 objet absent, 1054 colonne inconnue) ; les autres clauses de l'instruction s'appliquent normalement. Si toutes les clauses sont ainsi ignorées, l'instruction entière rend OK sans erreur.

Options sans effet#

ENGINE, [DEFAULT] CHARSET / CHARACTER SET, COLLATE, COMMENT, CONVERT TO CHARACTER SET, ALGORITHM = ..., LOCK = ... sont acceptées mais n'ont aucun effet sur le stockage. Sont en revanche des erreurs (1235, non pris en charge) : ORDER BY, RENAME INDEX, colonnes INVISIBLE.

CREATE [UNIQUE] INDEX et 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 sans UNIQUE crée un index secondaire non unique (voir la section dédiée plus haut) ; CREATE UNIQUE INDEX équivaut à ALTER TABLE ... ADD UNIQUE. Les deux formes acceptent les options ALGORITHM = ... et LOCK = ... sans effet. DROP INDEX nom ON table équivaut à ALTER TABLE table DROP INDEX nom.

Vues#

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];

-- Vue ordinaire : reflète toujours l'état courant des tables
CREATE VIEW v_client_actif AS
SELECT id, nom, email FROM client WHERE actif = 1;

-- Vue en cache : figée jusqu'au prochain 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 et IF NOT EXISTS sont mutuellement exclusifs (erreur 1221). ALGORITHM et WITH CHECK OPTION sont acceptés à l'analyse mais sans effet. Les définitions sont conservées dans <base>/views.mrv, écrit de façon durable avant que l'instruction ne rende la main.

Vue ordinaire ou vue CACHED#

Une vue est toujours en lecture seule :

Une vue peut être construite sur une autre vue (jusqu'à 32 niveaux d'imbrication ; un cycle rend l'erreur 1462).

REFRESH VIEW#

REFRESH VIEW v_bilan_mensuel;
REFRESH VIEW v_bilan_mensuel, v_autre_vue;

Ne s'applique qu'à une vue CACHED : recalcule son résultat immédiatement et remplace la version figée. Sans effet particulier sur une vue ordinaire (dont le résultat, s'il est gardé, se met déjà à jour de lui-même).

ALTER VIEW#

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

Même syntaxe que CREATE VIEW (hors IF NOT EXISTS) : redéfinit entièrement la vue.

DROP VIEW#

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

SHOW CREATE VIEW#

SHOW CREATE VIEW v_client_actif;

Rend la définition canonique de la vue, telle qu'enregistrée (y compris CACHED s'il y a lieu). information_schema.VIEWS liste les vues de la base courante ; information_schema.COLUMNS ne décrit que les vues dont le résultat est actuellement en mémoire.

Voir aussi#

• Types de données : détail des types, colonnes générées, AUTO_INCREMENT, contraintes CHECK.