Séance 2 — Le modèle relationnel côté pratique : types, contraintes, création du schéma

Objectifs d’apprentissage — à l’issue de la séance, vous serez capable de :

1. choisir un type de données adapté à une colonne et en expliquer les conséquences (taille, comparaisons, NULL) ;
2. lire et écrire les contraintes déclaratives (PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, DEFAULT) ;
3. recréer une base à partir d’un modèle déclaratif fourni (schema.sql = Data Definition Language), puis la modifier (ajout de colonne, de contrainte) sans perdre les données ;
4. expliquer le rôle de NULL et les pièges qu’il introduit dans les comparaisons.

Prérequis mobilisés : séance 1 (vocabulaire, lecture du schéma bibliothèque), programmation objet (notion d’attribut typé, d’identité d’objet).

Matériel : la base bibliotheque importée en séance 1, un client mysql, et un second script à fournir : schema.sql pour recréer la structure sans données.

Partie cours

1. Retour sur la séance 1 et ouverture

En TP lors de la séance 1 trois requêtes ont été rejetées : insertion d’un livre avec auteur inexistant, suppression d’un auteur référencé, insertion d’un auteur sans nom. Ces refus étaient particulièrement pertinents car ils ont assuré l’intégrité des données de la base. « Mais où sont donc écrites les règles à l’origine de ces refus ? » : dans le Data Definition Language (DDL)), c’est-à-dire dans le schéma de la base de données. C’est le fil de cette séance : le schéma est du code déclaratif qui protège les données mieux que n’importe quel code applicatif, parce qu’il s’applique à tous les programmes qui utilisent la base.

Plan de la séance: (1) les types, (2) NULL, (3) les contraintes, (4) modifier un schéma.

2. Les types de données

On ne cherche pas l’exhaustivité : on couvre les types réellement utiles et on renvoie à la documentation pour le reste. On reste sur MariaDB/MySQL puisque c’est le SGBD du cours, en signalant les équivalents PostgreSQL entre parenthèses.

Types numériques.

Point à marteler : DECIMAL pour l’argent, jamais FLOAT — 0.1 + 0.2 ne vaut pas 0.3 en flottant. On peut le démontrer en direct :

SELECT 0.1 + 0.2 = 0.3;                          -- 0
SELECT CAST(0.1 AS DECIMAL(5,2)) + CAST(0.2 AS DECIMAL(5,2)) = 0.3;  -- 1

Types chaînes.

• VARCHAR(n) : longueur variable, jusqu’à n. À privilégier pour les textes courts (noms, titres).
• CHAR(n) : longueur fixe, complétée par des espaces. Réservé aux codes de longueur constante (code postal, code INSEE) — rarement utile.
• TEXT : texte long sans limite pratique raisonnable (résumés, descriptions). À éviter pour les colonnes indexées ou triées souvent.

Règle pratique : VARCHAR pour tout ce qui est « un nom », TEXT pour « un paragraphe ».

Types date/heure.

On mentionne CURRENT_DATE, CURRENT_TIMESTAMP comme valeurs par défaut fréquentes (on les verra en séance 5 pour l’insertion).

Types énumérés. ENUM('disponible','emprunté','perdu') : pratique mais rigide (ajouter une valeur = ALTER TABLE). On le signale sans en faire la promotion — préférer une table de référence quand le domaine peut évoluer.

Choix de type = choix de contrat. Question à poser à la classe : pourquoi titre est-il VARCHAR(200) et pas TEXT ? Réponses attendues : taille bornée, indexable efficacement, comparable rapidement. Le type n’est pas qu’une question de place, c’est une contrainte implicite (longueur maximale) qui protège aussi les données.

3. NULL : le piège à connaître (15 min)

On commence par une démonstration qui surprend toujours :

SELECT NULL = NULL;        -- NULL, pas 1 !
SELECT NULL <> NULL;       -- NULL
SELECT 1 = NULL;           -- NULL
SELECT NULL IS NULL;       -- 1

Message : NULL signifie « valeur inconnue », pas « zéro » ni « chaîne vide ». Toute comparaison avec NULL renvoie NULL, qui n’est ni vrai ni faux — donc la ligne n’est pas retenue par un WHERE. Conséquence pratique :

-- Ne renvoie PAS les livres dont disponible est NULL (s'il y en avait)
SELECT * FROM livre WHERE disponible <> 1;

-- Renvoie aussi les NULL
SELECT * FROM livre WHERE disponible IS NULL OR disponible <> 1;

Trois règles à retenir : 1. NOT NULL doit être le défaut sur les colonnes où une valeur est toujours attendue (nom d’auteur, titre de livre). 2. NULL est légitime quand l’information n’existe vraiment pas (une date_retour non renseignée = emprunt en cours). 3. Dans les requêtes, toujours se demander : cette colonne peut-elle être NULL ? Si oui, IS NULL / IS NOT NULL sont nécessaires.

Mini-exercice oral (2 min) : dans livre, quelle colonne est nullable ? Quelle colonne devrait l’être mais ne l’est pas (selon vous) ? Réponse attendue : aucune colonne de livre n’est nullable ; et pour le projet, emprunt.date_retour devra l’être.

4. Les contraintes déclaratives (20 min)

On reprend le DDL de la séance 1 et on explicite chaque contrainte, en montrant ce qui se passe si on l’enlève.

NOT NULL — déjà vu. On illustre : titre VARCHAR(200) NOT NULL → insertion sans titre refusée.

DEFAULT — valeur utilisée si l’INSERT ne précise pas la colonne.

-- Sans DEFAULT : insertion refusée si on ne donne pas disponible
-- Avec DEFAULT 1 : insertion réussie, disponible = 1
INSERT INTO livre (titre, auteur_id) VALUES ('Test', 1);
SELECT disponible FROM livre WHERE titre = 'Test';  -- 1

PRIMARY KEY — identifiant unique et non nul. On précise : une table a exactement une clé primaire (éventuellement composite). Elle crée aussi un index.

AUTO_INCREMENT — le SGBD attribue l’id. On anticipe la séance 5 : LAST_INSERT_ID() pour récupérer l’id généré.

UNIQUE — unicité sans être clé primaire. Exemple à donner : dans auteur, on pourrait vouloir UNIQUE(prenom, nom) pour interdire les doublons. On le fait en direct :

ALTER TABLE auteur ADD CONSTRAINT uq_auteur_nom UNIQUE (prenom, nom);
-- Tente d'insérer un doublon
INSERT INTO auteur (prenom, nom) VALUES ('George', 'Orwell');  -- refusé

CHECK — règle logique sur une ligne. Attention : MariaDB 10.11 supporte CHECK (contrairement aux anciennes versions MySQL où il était ignoré). On le vérifie :

ALTER TABLE livre ADD CONSTRAINT ck_livre_titre CHECK (CHAR_LENGTH(titre) > 0);
INSERT INTO livre (titre, auteur_id) VALUES ('', 1);  -- refusé

FOREIGN KEY — le cœur de la séance. On reprend les clauses du schéma :

CONSTRAINT fk_livre_auteur
  FOREIGN KEY (auteur_id) REFERENCES auteur (id)
  ON UPDATE CASCADE
  ON DELETE RESTRICT

• ON DELETE RESTRICT : suppression d’un auteur référencé → refus (vu en TP1).
• ON DELETE CASCADE : suppression d’un auteur → suppression en cascade de ses livres. Dangereux, à utiliser en connaissance de cause.
• ON DELETE SET NULL : les livre.auteur_id deviennent NULL. Nécessite que la colonne soit nullable — elle ne l’est pas ici, donc cette option serait refusée à la création.
• ON UPDATE CASCADE : si on change auteur.id, la référence suit. Rare mais utile en cas de fusion d’identifiants.

On fait le tour des trois comportements en modifiant une copie de la base (voir TP), pour ne pas casser le fil rouge. Message : RESTRICT est le défaut raisonnable ; CASCADE est un choix explicite qui doit être justifié par le métier.

Synthèse :

5. Modifier un schéma sans perdre les données (10 min)

On présente ALTER TABLE comme l’outil de toutes les évolutions. Trois exemples, dans l’ordre de difficulté :

-- 1. Ajouter une colonne (avec valeur par défaut pour les lignes existantes)
ALTER TABLE livre ADD COLUMN annee_publication INT NULL;

-- 2. Ajouter une contrainte
ALTER TABLE auteur ADD CONSTRAINT uq_auteur_nom UNIQUE (prenom, nom);

-- 3. Modifier le type d'une colonne (attention aux données existantes)
ALTER TABLE livre MODIFY COLUMN titre VARCHAR(300) NOT NULL;

Message : toute modification de schéma est un acte à impact, pas une simple édition. Ajouter une colonne NOT NULL sans DEFAULT sur une table non vide échoue — on le montre en direct, puis on corrige avec DEFAULT.

A partir de maintenant, le schéma est stable, on va interroger les données. Ce sera naturellement le but de la séance 3.

TP (1 h 30) — Recréer, contraindre, modifier

Livrable : un fichier compte_rendu_seance2.txt contenant les réponses numérotées, les commandes exécutées et les sorties (ou captures). Toutes les manipulations se font dans une base dédiée bibliotheque_tp pour ne pas casser le fil rouge.

Étape 0 — Préparer une base de travail (10 min)

sudo mariadb --default-character-set=utf8mb4 -u root -p < schema_vide.sql

Le script schema_vide.sql doit contenir :

DROP DATABASE IF EXISTS bibliotheque_tp;
CREATE DATABASE bibliotheque_tp
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_unicode_ci;
USE bibliotheque_tp;
SET NAMES utf8mb4;
SET sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

CREATE TABLE auteur (
  id     INT          NOT NULL AUTO_INCREMENT,
  prenom VARCHAR(100) NOT NULL,
  nom    VARCHAR(100) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE livre (
  id         INT          NOT NULL AUTO_INCREMENT,
  titre      VARCHAR(200) NOT NULL,
  auteur_id  INT          NOT NULL,
  disponible TINYINT(1)   NOT NULL DEFAULT 1,
  PRIMARY KEY (id),
  KEY idx_livre_auteur (auteur_id),
  CONSTRAINT fk_livre_auteur
    FOREIGN KEY (auteur_id) REFERENCES auteur (id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT
) ENGINE=InnoDB;

Puis, dans le client :

USE bibliotheque_tp;
SHOW TABLES;

Q0 : combien de tables contient bibliotheque_tp ? Pourquoi est-elle vide contrairement à bibliotheque ?

Étape 1 — Types de données : observer les différences (20 min)

a) Précision numérique.

SELECT 0.1 + 0.2 = 0.3;                        -- ?
SELECT CAST(0.1 AS DECIMAL(5,2)) + CAST(0.2 AS DECIMAL(5,2)) = 0.3;  -- ?

Q1 : recopiez les deux résultats. Expliquez en 2–3 lignes pourquoi ils diffèrent et quelle conclusion vous en tirez pour le choix d’un type monétaire.

b) Chaînes tronquées.

CREATE TABLE test_types (
  id  INT PRIMARY KEY AUTO_INCREMENT,
  c   CHAR(3),
  vc  VARCHAR(3),
  t   TEXT
);
INSERT INTO test_types (c, vc, t) VALUES ('abcd', 'abcd', 'abcd');

Q2 : que renvoie le SELECT * FROM test_types; ? Commentez le comportement de CHAR(3) et de VARCHAR(3) face à une chaîne trop longue. (Indice : le sql_mode est en STRICT_TRANS_TABLES — que se serait-il passé sans lui ?)

c) Dates.

CREATE TABLE test_dates (d DATE, dt DATETIME, ts TIMESTAMP);
INSERT INTO test_dates VALUES ('2026-11-09', '2026-11-09 14:30:00', '2026-11-09 14:30:00');
SELECT * FROM test_dates;

Q3 : que contient chaque colonne après insertion ? Quelle est la différence de comportement entre DATETIME et TIMESTAMP vis-à-vis du fuseau horaire ? (Aide : SET time_zone = '+02:00'; puis re-SELECT.)

d) Bilan. Q4 : pour chacune des colonnes suivantes du projet bibliothèque, proposez un type et justifiez en une phrase : livre.titre, livre.disponible, livre.annee_publication (à venir), emprunt.date_emprunt, emprunt.date_retour, un futur emprunt.amende (montant en euros).

Étape 2 — NULL en pratique (15 min)

a) Démonstration.

SELECT NULL = NULL, NULL <> NULL, NULL IS NULL, NULL IS NOT NULL;

Q5 : recopiez les quatre résultats. En une phrase, que signifie « NULL n’est ni vrai ni faux » ?

b) Impact sur les filtres.

-- On rend temporairement une colonne nullable pour l'exercice
ALTER TABLE livre MODIFY COLUMN disponible TINYINT(1) NULL;
INSERT INTO auteur (prenom, nom) VALUES ('A', 'Inconnu');
INSERT INTO livre (titre, auteur_id, disponible) VALUES ('Test NULL', 1, NULL);

-- Requêtes à comparer
SELECT titre FROM livre WHERE disponible <> 1;      -- Combien de lignes ?
SELECT titre FROM livre WHERE disponible IS NULL;   -- Combien de lignes ?

Q6 : combien de lignes renvoie chaque requête ? Pourquoi Test NULL n’apparaît-il pas dans la première ? Que faut-il écrire pour le récupérer ?

c) Nettoyage.

DELETE FROM livre WHERE titre = 'Test NULL';
ALTER TABLE livre MODIFY COLUMN disponible TINYINT(1) NOT NULL DEFAULT 1;

Q7 : pourquoi la dernière commande ALTER échouerait-elle si on avait laissé une ligne avec disponible = NULL ? (Faites le test dans une transaction que vous annulez, ou expliquez sans exécuter.)

Étape 3 — Contraintes en action (25 min)

a) DEFAULT.

INSERT INTO auteur (prenom, nom) VALUES ('George', 'Orwell');
INSERT INTO livre (titre, auteur_id) VALUES ('1984', LAST_INSERT_ID());
SELECT titre, disponible FROM livre;

Q8 : quelle valeur a disponible pour ce livre ? Quelle ligne du DDL l’explique ?

b) UNIQUE.

ALTER TABLE auteur ADD CONSTRAINT uq_auteur_nom UNIQUE (prenom, nom);
INSERT INTO auteur (prenom, nom) VALUES ('George', 'Orwell');

Q9 : recopiez le message d’erreur. Quelle contrainte l’a provoqué ? Que se passe-t-il si vous insérez ('George', 'Orwell Jr') ?

c) CHECK.

ALTER TABLE livre ADD CONSTRAINT ck_titre_non_vide CHECK (CHAR_LENGTH(titre) > 0);
INSERT INTO livre (titre, auteur_id) VALUES ('', 1);

Q10 : recopiez le message. La contrainte CHECK est-elle bien appliquée par votre version de MariaDB ? (À vérifier aussi avec SHOW CREATE TABLE livre;.)

d) FOREIGN KEY ... RESTRICT.

DELETE FROM auteur WHERE prenom = 'George' AND nom = 'Orwell';

Q11 : recopiez l’erreur. Pourquoi le SGBD refuse-t-il alors qu’il n’existe qu’un seul auteur concerné ?

e) ON DELETE CASCADE — variante, sur une copie.

CREATE TABLE auteur_bis LIKE auteur;
CREATE TABLE livre_bis  LIKE livre;
-- On recrée la FK avec CASCADE sur livre_bis
ALTER TABLE livre_bis DROP FOREIGN KEY fk_livre_auteur;
ALTER TABLE livre_bis
  ADD CONSTRAINT fk_livre_bis_auteur
  FOREIGN KEY (auteur_id) REFERENCES auteur_bis (id)
  ON DELETE CASCADE;

INSERT INTO auteur_bis (prenom, nom) VALUES ('Jules', 'Verne');
INSERT INTO livre_bis (titre, auteur_id) VALUES ('Vingt mille lieues', LAST_INSERT_ID());
DELETE FROM auteur_bis WHERE nom = 'Verne';
SELECT * FROM livre_bis;

Q12 : que contient livre_bis après le DELETE ? Expliquez en une phrase le comportement de CASCADE et pourquoi il est dangereux par défaut dans une application métier.

f) Bilan. Q13 : pour le futur projet, vous devez créer la table emprunt(livre_id → livre.id, nom_emprunteur, date_emprunt, date_retour NULL). Quel comportement (RESTRICT, CASCADE, SET NULL) choisissez-vous pour ON DELETE sur livre_id ? Justifiez en 3 lignes (pensez à l’historique des emprunts).

Étape 4 — ALTER TABLE sur des données existantes (15 min)

a) Ajouter une colonne sans casser l’existant.

ALTER TABLE livre ADD COLUMN annee_publication INT NULL;
SELECT titre, annee_publication FROM livre;

Q14 : quelle valeur prennent les lignes existantes ? Pourquoi NULL ici et pas 0 ?

b) Ajouter une colonne NOT NULL sans DEFAULT — l’échec.

ALTER TABLE livre ADD COLUMN editeur VARCHAR(100) NOT NULL;

Q15 : recopiez l’erreur. Proposez deux corrections possibles (l’une avec DEFAULT, l’autre en deux temps : ajout nullable, remplissage, passage en NOT NULL). Écrivez-les.

c) Renommer / modifier.

ALTER TABLE livre CHANGE annee_publication annee INT NULL;
ALTER TABLE livre MODIFY COLUMN titre VARCHAR(300) NOT NULL;
SHOW CREATE TABLE livre;

Q16 : recopiez le SHOW CREATE TABLE livre final. Listez toutes les contraintes présentes à ce stade.

Étape 5 — Synthèse (5 min)

Q17 : en 6 à 8 lignes, répondez à : « pourquoi dit-on que le schéma est du code déclaratif qui protège les données ? » Appuyez-vous sur au moins trois contraintes testées pendant ce TP et sur le fait qu’elles s’appliquent indépendamment du langage ou du programme qui écrit dans la base.

Barème indicatif du TP (sur 20)

À préparer pour la séance 3

• Relire schema.sql et repérer toutes les occurrences de WHERE dans les scripts fournis (data.sql n’en a pas, mais le TP1 en contenait).
• Réfléchir à : « comment demander au SGBD “tous les livres de Jules Verne” ? » — sans écrire la requête, juste formuler le besoin en français. Amorce de la séance 3 (SELECT ... WHERE).

Questionnaire d’auto-évaluation