CREATE TABLE, clés et contraintes (LDD)

CREATE TABLE crée une table avec ses colonnes, leurs types et leurs contraintes : clé primaire, clé étrangère, NOT NULL, UNIQUE, CHECK. Avec ALTER TABLE et DROP, ces instructions forment le LDD, le langage de définition des données. Les exemples modifient la base d'exemple parc, comme ceux du chapitre INSERT, UPDATE et DELETE. Pour retrouver exactement les résultats du cours, rejoue son script avant chaque série d'exercices : il remet la base dans son état initial.

CREATE DATABASE, jeu de caractères et interclassement

-- La base parc existe déjà (script de la base d'exemple) : IF NOT EXISTS évite l'erreur 1007
CREATE DATABASE IF NOT EXISTS parc CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE parc;

Le jeu de caractères (CHARACTER SET) dit comment les caractères sont codés en octets. Choisis toujours utf8mb4 : c'est le vrai UTF-8, qui code chaque caractère sur un à quatre octets et accepte toutes les langues et les émojis. L'ancien utf8 de MySQL (renommé utf8mb3) s'arrête à trois octets et il est obsolète.

L'interclassement (COLLATE) dit comment comparer et trier les chaînes. utf8mb4_0900_ai_ci, l'interclassement par défaut de MySQL 8, est insensible aux accents (ai, accent insensitive) et à la casse (ci, case insensitive). Conséquence directe :

SELECT id_employe, nom FROM employe WHERE nom = 'LEFEVRE';

Pour une comparaison stricte, il existe utf8mb4_0900_as_cs (sensible aux accents et à la casse) et utf8mb4_bin (comparaison octet par octet).

CREATE DATABASE IF NOT EXISTS parc ne fait rien si la base existe déjà ; DROP DATABASE parc la supprime avec toutes ses tables. Pour MySQL, DATABASE et SCHEMA sont synonymes.

La syntaxe de CREATE TABLE

-- Syntaxe générale (les crochets marquent les parties facultatives)
CREATE TABLE [IF NOT EXISTS] nom_table (
  colonne TYPE [NOT NULL] [DEFAULT valeur] [AUTO_INCREMENT] [UNIQUE] [PRIMARY KEY],
  ...,
  [CONSTRAINT nom] PRIMARY KEY (colonne, ...),
  [CONSTRAINT nom] UNIQUE (colonne, ...),
  [CONSTRAINT nom] CHECK (condition),
  [CONSTRAINT nom] FOREIGN KEY (colonne) REFERENCES table_parent (colonne)
    [ON DELETE action] [ON UPDATE action]
);

Une contrainte s'écrit à deux endroits. En ligne, à la suite de la colonne (email VARCHAR(100) NOT NULL UNIQUE) : c'est court, mais seulement pour une contrainte sur une seule colonne. En fin de table, après les colonnes (PRIMARY KEY (id_poste, id_logiciel)) : obligatoire pour une contrainte sur plusieurs colonnes, et c'est là qu'on lui donne un nom avec CONSTRAINT.

Nomme tes contraintes. Sans nom, MySQL en invente un (employe_ibfk_1, poste_chk_2) et tes messages d'erreur deviennent illisibles. La base parc suit une convention simple : fk_table_cible pour une clé étrangère, ck_table_sujet pour un CHECK.

Le service informatique veut maintenant suivre ses fournisseurs de matériel. Voici la table, commentée :

CREATE TABLE fournisseur (
  id_fournisseur INT AUTO_INCREMENT PRIMARY KEY,   -- clé artificielle
  raison_sociale VARCHAR(100) NOT NULL,
  siret          CHAR(14)     NOT NULL,            -- toujours 14 chiffres
  email          VARCHAR(100),                     -- facultatif
  delai_jours    TINYINT      NOT NULL DEFAULT 5,  -- délai de livraison habituel
  CONSTRAINT uq_fournisseur_siret UNIQUE (siret),
  CONSTRAINT ck_fournisseur_siret CHECK (siret REGEXP '^[0-9]{14}$'),
  CONSTRAINT ck_fournisseur_delai CHECK (delai_jours BETWEEN 1 AND 60)
);

Choisir les types

Un type fixe ce qu'une colonne accepte et la place qu'elle occupe. Le bon type est la première contrainte d'intégrité.

Nombres entiers

INT(11) ne limite rien : le nombre entre parenthèses était une largeur d'affichage, obsolète depuis MySQL 8.0.17. Écris INT.

Nombres décimaux

SELECT 0.1e0 + 0.2e0 AS en_double, 0.1 + 0.2 AS en_decimal;

Écrit avec un exposant, 0.1e0 est un DOUBLE : la somme n'est pas exactement 0,3. Écrit sans exposant, 0.1 est un DECIMAL exact.

Chaînes

Dans VARCHAR(50), 50 est un nombre de caractères, pas d'octets. CHAR(n) fixe la place réservée, pas la longueur saisie : 'PC-1' entre dans le CHAR(7) de num_inventaire, complété par des espaces. Pour imposer une longueur exacte, il faut un CHECK, comme celui du SIRET de la table fournisseur.

Dates et heures

Les dates s'écrivent toujours 'AAAA-MM-JJ', entre apostrophes, quel que soit l'affichage de ton client.

Autres types utiles

• BOOLEAN : synonyme de TINYINT(1). TRUE vaut 1 et FALSE vaut 0.
• ENUM('basse', 'normale', 'haute') : une valeur parmi une liste, propre à MySQL et MariaDB. Le tri suit l'ordre de déclaration, pas l'ordre alphabétique. Ajouter une valeur impose un ALTER TABLE : la base parc préfère VARCHAR avec un CHECK, une écriture portable.
• JSON : un document JSON validé à l'insertion (les colonnes JSON dans MySQL).
• BINARY(n), VARBINARY(n), BLOB : des octets bruts, par exemple une donnée chiffrée (chiffrer une colonne sensible).

Les types varient d'un SGBD à l'autre. PostgreSQL n'a ni TINYINT, ni UNSIGNED, ni ENUM en ligne : utilise SMALLINT, un CHECK, ou CREATE TYPE ... AS ENUM. BOOLEAN y est un vrai type, et l'auto-incrément s'écrit GENERATED BY DEFAULT AS IDENTITY (ou ALWAYS pour refuser les valeurs saisies) ; après des insertions avec identifiants explicites, comme dans le script de la base parc, recale la séquence avec setval. SQL Server utilise INT IDENTITY(1,1), BIT pour les booléens, NVARCHAR pour l'Unicode et DATETIME2 pour les dates.

Les contraintes

PRIMARY KEY. Unique et non nulle. Sur une colonne, en ligne ; sur plusieurs, en fin de table : PRIMARY KEY (id_poste, id_logiciel).

AUTO_INCREMENT. MySQL attribue la valeur suivante quand tu n'en fournis pas. Une valeur supprimée n'est pas réutilisée, et depuis MySQL 8.0 le compteur survit au redémarrage du serveur. Une seule colonne par table peut l'avoir, et elle doit être indexée (en pratique, c'est la clé primaire).

NOT NULL. La colonne est obligatoire. Sans NOT NULL, elle accepte NULL.

DEFAULT. La valeur utilisée quand l'INSERT ne cite pas la colonne : une constante (DEFAULT 'normale'), CURRENT_TIMESTAMP pour un DATETIME, ou depuis MySQL 8.0.13 une expression entre parenthèses (DEFAULT (CURRENT_DATE)).

UNIQUE. Interdit les doublons. Elle accepte plusieurs NULL, car deux valeurs absentes ne sont pas considérées comme égales. Une contrainte UNIQUE (nom, prenom) porte sur le couple.

CHECK. Une condition que chaque ligne doit respecter. MySQL l'applique depuis la version 8.0.16 ; avant, il l'acceptait dans la syntaxe et l'ignorait. MariaDB l'applique depuis la 10.2.1. La condition ne peut utiliser que des colonnes de la ligne et des fonctions déterministes : ni sous-requête, ni NOW(), ni CURRENT_DATE.

FOREIGN KEY. La colonne doit avoir un type compatible avec la clé référencée : même type entier et même signe (un INT ne peut pas référencer un INT UNSIGNED), même jeu de caractères et même interclassement pour une chaîne (la longueur, elle, peut différer). La colonne référencée doit être une clé primaire ou porter une contrainte UNIQUE. La table parente doit exister avant.

Clé étrangère : ON DELETE et ON UPDATE

Que faire des lignes enfants quand on supprime, ou qu'on modifie la clé de, la ligne parente qu'elles référencent ?

Chaque choix de la base fil rouge traduit une règle métier :

Essaie :

DELETE FROM ticket WHERE id_ticket = 9;   -- le ticket 9 avait 3 interventions
SELECT COUNT(*) FROM intervention;        -- 17 - 3

DELETE FROM service WHERE id_service = 5;

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
(`parc`.`employe`, CONSTRAINT `fk_employe_service` FOREIGN KEY (`id_service`) REFERENCES `service` (`id_service`))

Le message cite la table enfant (employe) et le nom de la contrainte : c'est pour ça qu'on les nomme. Le service 6, Juridique, n'a aucun employé : DELETE FROM service WHERE id_service = 6; fonctionne.

ON UPDATE CASCADE répercute un changement de valeur de la clé primaire. Avec des clés artificielles, qui ne changent jamais, il sert rarement.

Le moteur InnoDB

MySQL stocke chaque table avec un moteur de stockage. Le moteur par défaut, depuis MySQL 5.5, est InnoDB : c'est lui qui gère les transactions, les clés étrangères, les verrous par ligne et la reprise après une panne. L'ancien moteur MyISAM ne gère ni transactions ni clés étrangères.

SELECT table_name AS nom_table, engine AS moteur
FROM information_schema.tables
WHERE table_schema = 'parc';

Méfie-toi des vieux tutoriels qui écrivent ENGINE = MyISAM : tu perds les clés étrangères sans erreur, car MyISAM lit la clause FOREIGN KEY et l'ignore. Laisse le moteur par défaut, ou écris ENGINE = InnoDB.

ALTER TABLE

ALTER TABLE modifie une table existante sans perdre ses données.

-- Ajouter une colonne : elle vaut NULL (ou sa valeur DEFAULT) dans les lignes existantes
ALTER TABLE poste ADD COLUMN fin_garantie DATE;

-- Ajouter une contrainte
ALTER TABLE poste ADD CONSTRAINT ck_poste_garantie CHECK (fin_garantie >= date_achat);

-- Changer le type ou les options d'une colonne : redonne la définition complète
ALTER TABLE poste MODIFY COLUMN marque VARCHAR(40) NOT NULL;

-- Renommer une colonne (MySQL 8.0 et MariaDB 10.5 et plus)
ALTER TABLE poste RENAME COLUMN modele TO modele_constructeur;

-- Changer la valeur par défaut seulement
ALTER TABLE ticket ALTER COLUMN priorite SET DEFAULT 'basse';

-- Supprimer une contrainte CHECK, puis la colonne
ALTER TABLE poste DROP CHECK ck_poste_garantie;
ALTER TABLE poste DROP COLUMN fin_garantie;

Pour relier les postes à la table fournisseur créée avec CREATE TABLE, ajoute la colonne et la clé étrangère en une seule instruction :

ALTER TABLE poste
  ADD COLUMN id_fournisseur INT,
  ADD CONSTRAINT fk_poste_fournisseur FOREIGN KEY (id_fournisseur)
    REFERENCES fournisseur (id_fournisseur);

Pour supprimer une clé étrangère, MySQL demande son nom : ALTER TABLE poste DROP FOREIGN KEY fk_poste_fournisseur;. L'index créé avec elle reste en place ; ALTER TABLE poste DROP INDEX fk_poste_fournisseur; le supprime. Pour renommer une table : RENAME TABLE fournisseur TO prestataire;.

Ailleurs, la syntaxe change. MariaDB supprime une contrainte CHECK avec DROP CONSTRAINT nom, que MySQL accepte aussi depuis la 8.0.19. PostgreSQL n'a pas MODIFY : il écrit ALTER TABLE poste ALTER COLUMN marque TYPE VARCHAR(40) puis ALTER COLUMN marque SET NOT NULL. SQL Server écrit ALTER COLUMN et renomme avec la procédure sp_rename.

DROP, TRUNCATE et DELETE

Les tables liées se créent des parents vers les enfants et se suppriment dans l'ordre inverse. Pour la base parc :

DROP TABLE IF EXISTS intervention;
DROP TABLE IF EXISTS ticket;
DROP TABLE IF EXISTS installation;
DROP TABLE IF EXISTS poste;
DROP TABLE IF EXISTS logiciel;
DROP TABLE IF EXISTS employe;
DROP TABLE IF EXISTS service;

Tu verras parfois SET FOREIGN_KEY_CHECKS = 0; avant une suppression ou un import. Cette commande coupe la vérification des clés étrangères pour ta session : tout ce que tu insères pendant ce temps n'est pas contrôlé, et MySQL ne revérifie rien quand tu la remets à 1. Réserve-la aux restaurations de sauvegardes, jamais pour contourner une erreur que tu ne comprends pas.

Repérer une table mal conçue

Avant d'écrire un CREATE TABLE, vérifie que la table respecte les trois premières formes normales. Le cours Merise les présente en détail ; voici comment les repérer.

1re forme normale : des valeurs atomiques. Chaque cellule contient une seule valeur. Une colonne logiciels qui contient 'Firefox, Odoo, Sage' viole la 1FN : impossible de compter les installations d'Odoo ou d'interdire une faute de frappe. La solution est la table installation, une ligne par couple poste-logiciel.

2e forme normale : toute la clé. Dans une table à clé composée, chaque colonne non clé dépend de la clé entière. Une table installation (id_poste, id_logiciel, date_installation, nom_logiciel) viole la 2FN : nom_logiciel ne dépend que de id_logiciel. Sa place est dans logiciel.

3e forme normale : rien que la clé. Une colonne non clé ne dépend pas d'une autre colonne non clé. Une table employe (..., id_service, libelle_service) viole la 3FN : libelle_service dépend de id_service, pas de l'employé. Si le service change de nom, toutes ses lignes sont à modifier.

Exercices

CREATE TABLE contrat_maintenance (
  id_contrat     INT AUTO_INCREMENT PRIMARY KEY,
  reference      CHAR(12)     NOT NULL,
  id_fournisseur INT          NOT NULL,
  date_debut     DATE         NOT NULL,
  date_fin       DATE         NOT NULL,
  montant_annuel DECIMAL(7,2) NOT NULL,
  CONSTRAINT uq_contrat_reference UNIQUE (reference),
  CONSTRAINT ck_contrat_reference CHECK (CHAR_LENGTH(reference) = 12),
  CONSTRAINT ck_contrat_dates CHECK (date_fin > date_debut),
  CONSTRAINT ck_contrat_montant CHECK (montant_annuel >= 0),
  CONSTRAINT fk_contrat_fournisseur FOREIGN KEY (id_fournisseur)
    REFERENCES fournisseur (id_fournisseur)
);

ALTER TABLE logiciel ADD COLUMN fin_support DATE;

ALTER TABLE logiciel
  ADD COLUMN cout_annuel DECIMAL(9,2) NOT NULL DEFAULT 0,
  ADD CONSTRAINT ck_logiciel_cout CHECK (cout_annuel >= 0);

SELECT nom, fin_support, cout_annuel FROM logiciel WHERE id_logiciel = 1;

CREATE TABLE pret (
  id_pret            INT AUTO_INCREMENT PRIMARY KEY,
  id_poste           INT  NOT NULL,
  id_employe         INT  NOT NULL,
  date_debut         DATE NOT NULL,
  date_retour_prevue DATE NOT NULL,
  date_retour        DATE,
  CONSTRAINT ck_pret_dates CHECK (date_retour_prevue >= date_debut
                                  AND (date_retour IS NULL OR date_retour >= date_debut)),
  CONSTRAINT fk_pret_poste FOREIGN KEY (id_poste)
    REFERENCES poste (id_poste) ON DELETE RESTRICT,
  CONSTRAINT fk_pret_employe FOREIGN KEY (id_employe)
    REFERENCES employe (id_employe) ON DELETE RESTRICT
);