Ch. 5 — Le DDL

Le DDL (Data Description Language) définit la structure : on y crée les tables, on y déclare les types et surtout les contraintes — c'est le SGBD qui protégera la donnée, pas le programme applicatif.

Les types de données

Les types sont dépendants des machines, donc des SGBD — mais une famille commune existe :

• alphanumérique : CHAR(n) longueur fixe, VARCHAR(n) longueur variable bornée à n, TEXT (ou CLOB) variable non bornée ;
• numérique : NUMERIC(n, d) — n chiffres dont d après la virgule (parfois écrit NUMBER ou DECIMAL), SMALLINT (2 octets), INT (4 octets), BIGINT (8 octets), REAL flottant ;
• temps : DATE, TIME et TIMESTAMP (avec ou sans fuseau), INTERVAL pour une durée ;
• binaires : BIT(n) (B'10111'), VARBIT(n), BOOLEAN, BLOB (bytea en Postgres).

Chaque SGBD ajoute ses propres types (xml, json, array…). Pour les clés auto-incrémentées, Postgres propose SERIAL, MySQL AUTO_INCREMENT, et la norme GENERATED {ALWAYS | BY DEFAULT} AS IDENTITY — Postgres accepte les deux notations.

Les conversions passent par la fonction CAST ou l'opérateur Postgres :: :

SELECT '2023-01-25' + 1;                          -- ERROR: invalid input syntax
SELECT '2023-01-25'::DATE + 1;                    -- 2023-01-26
SELECT '2023-01-25'::DATE + '1 month'::interval;  -- 2023-02-25

CREATE TABLE et les contraintes de colonne

La forme générale est CREATE TABLE nom (colonne type contrainte, …, contraintes de table). Tous les types de contraintes — clés, domaines, intégrité — sont exprimables dès le CREATE. Sur une colonne simple : NOT NULL, UNIQUE, CHECK (cond) (vérifiée à chaque mise à jour), DEFAULT valeur, et CONSTRAINT nom pour la nommer.

CREATE TABLE fournisseurs (
    fno     INTEGER NOT NULL
            CONSTRAINT c1 CHECK (fno > 100),
    nom     CHAR(20) UNIQUE NOT NULL,
    adresse CHAR(50),
    ville   CHAR(10) DEFAULT 'Lille'
);

Remarque : un CHECK n'est déclenché que quand la zone est différente de NULL.

Les contraintes de table

Elles portent en général sur plusieurs colonnes : PRIMARY KEY (liste), FOREIGN KEY (liste) REFERENCES …, CHECK (cond) multi-colonnes. Les règles à mémoriser :

• il n'y a qu'une seule clé primaire par table, et ses colonnes sont automatiquement UNIQUE NOT NULL ;
• il peut y avoir plusieurs clés étrangères dans une même table ; une clé étrangère peut être NULL voire non unique — l'intégrité n'est vérifiée que si aucun de ses champs n'est nul ;
• la valeur insérée doit exister en clé primaire dans la table visée.

La convention admise nomme les contraintes avec un préfixe pk_ pour la clé primaire et fk_ pour les clés étrangères :

CREATE TABLE cmds (
    fno  INTEGER,
    pno  INTEGER,
    qute INTEGER,
    CONSTRAINT pk_cmds PRIMARY KEY (fno, pno),
    CONSTRAINT fk_fournisseur FOREIGN KEY (fno) REFERENCES fournisseurs(fno),
    CONSTRAINT fk_produits    FOREIGN KEY (pno) REFERENCES produits(pno)
);

Les déclencheurs référentiels : ON DELETE / ON UPDATE

Que faire des clés étrangères quand la clé primaire associée change ? On le déclare :

ON DELETE { RESTRICT | CASCADE | SET NULL | SET DEFAULT }
ON UPDATE { RESTRICT | CASCADE | SET NULL | SET DEFAULT }

RESTRICT est la valeur par défaut si rien n'est précisé. Avec CASCADE, la modification (suppression) de la clé primaire est répercutée en cascade sur les tables qui y référencent ; avec SET NULL, les références sont vidées. Attention : ces déclencheurs n'agissent qu'en cas d'opération sur la clé primaire associée.

Le TD 2.5 les met en pratique sur le trafic aérien — un pno en SERIAL, un rno en CHAR(3), un lno en INTEGER, ON UPDATE CASCADE partout et ON DELETE SET NULL uniquement pour les aéroports d'une ligne :

CREATE TABLE ligne (
    lno     INTEGER PRIMARY KEY,
    depart  CHAR(3) REFERENCES aeroport(rno)
            ON UPDATE CASCADE ON DELETE SET NULL,
    arrivee CHAR(3) REFERENCES aeroport(rno)
            ON UPDATE CASCADE ON DELETE SET NULL
);
CREATE TABLE vol (
    ano  INT REFERENCES avion(ano) ON UPDATE CASCADE ON DELETE CASCADE,
    pno  INT REFERENCES pilote(pno) ON UPDATE CASCADE ON DELETE CASCADE,
    lno  INT REFERENCES ligne(lno) ON UPDATE CASCADE ON DELETE CASCADE,
    hdep TIME, harr TIME
);

Le DESCRIBE table de la norme est d'ailleurs peu respecté : \d en Postgres, .schema en SQLite, show columns from en H2.

Modifier et détruire

ALTER TABLE accepte ADD, ALTER (MODIFY), DROP et RENAME — ajouter une colonne, changer son type, la supprimer. La suppression d'une table s'écrit DROP TABLE [IF EXISTS] nom [, …] [CASCADE | RESTRICT] : elle emporte tout index, règle et séquence associés, et CASCADE supprime en plus les vues dépendantes et les clés étrangères des autres tables.

ALTER TABLE fournisseurs ADD prenom CHAR(15);
DROP TABLE IF EXISTS cmds CASCADE;

CREATE TABLE … AS SELECT … crée une table à partir d'une sélection — mais seuls les types et les données sont copiés, pas les contraintes. Pratique pour une table de travail, piégeux comme cible de production.

Les quatre règles du créateur de table

1. toujours réfléchir à l'identifiant unique ;
2. standardiser les noms de colonnes — pas de blancs, de caractères spéciaux ni de mots clés ;
3. ordonner les colonnes par ordre d'importance ;
4. choisir le format de données le plus adapté.

Et pour les scripts : un fichier .sql de création bien écrit est idempotent — on détruit d'abord tous les objets (DROP … IF EXISTS), dans l'ordre inverse des dépendances, puis on les crée dans l'ordre des dépendances, car on ne peut créer un objet qui référence un autre que si ce dernier existe déjà.

A retenir

• la contrainte protège la donnée au niveau du SGBD : mieux vaut un CHECK déclaré qu'une vérification applicative ;
• PRIMARY KEY (unique, non nulle, une par table) ; FOREIGN KEY (plusieurs possibles, nullable) avec ses déclencheurs ON DELETE/UPDATE : RESTRICT (défaut), CASCADE, SET NULL, SET DEFAULT ;
• nommer ses contraintes (pk_, fk_) rend les messages d'erreur lisibles ;
• CREATE TABLE AS SELECT copie types et données, jamais les contraintes ;
• les INSERT à accepter ou refuser du TD sont l'exercice qui apprend vraiment les contraintes : dire pourquoi chaque ligne passe ou casse.