data / sql

Contraintes : clés primaires, étrangères, UNIQUE, CHECK

Leçon 81 exercice

Explication

Ce que vous allez apprendre

Définir une clé primaire, simple ou composite, pour identifier chaque ligne
Garantir l'intégrité référentielle entre deux tables avec une clé étrangère
Choisir un comportement de suppression en cascade (ON DELETE CASCADE/SET NULL/RESTRICT)
Interdire les doublons avec UNIQUE et valider une règle métier avec CHECK
Comprendre pourquoi ces règles doivent vivre en base, pas seulement dans le code applicatif

Dans quel contexte ?

Une application de réservation de salles stocke ses données dans les tables reservations et salles. Un bug dans le code front-end laisse passer une réservation avec une date_depart postérieure à la date_retour. Sans contrainte CHECK au niveau de la base, cette incohérence est enregistrée telle quelle et corrompt silencieusement les rapports d'occupation - c'est précisément ce que cette leçon apprend à empêcher.

D'abord, le problème que résolvent les contraintes

Une application peut avoir des bugs, un développeur peut oublier une vérification. Si rien ne protège les données côté base, une simple erreur de code peut y enregistrer n'importe quoi. Il faut un filet de sécurité qui ne dépend pas du code applicatif.

La première contrainte : la clé primaire

Une clé primaire identifie une ligne de façon unique et ne peut jamais être NULL, comme un numéro de sécurité sociale identifie une personne unique dans un pays. Sans elle, impossible de désigner une ligne précise de façon fiable pour la modifier ou la supprimer.

Une fois qu'on a des tables séparées, un nouveau risque apparaît

Rappelle-toi que les données sont réparties en plusieurs tables ("clients", "commandes"). Rien n'empêche, a priori, qu'une commande référence un client_id qui n'existe pas, ou plus, dans la table "clients".

La clé étrangère comble ce trou

Une clé étrangère garantit qu'une valeur censée référencer une autre table pointe bien vers une ligne qui existe réellement. Sans elle, une commande "orpheline" pourrait référencer un client supprimé depuis longtemps, une incohérence silencieuse et difficile à repérer.

Prérequis

Cette leçon suppose que tu es à l'aise avec les jointures (leçon 5) : les clés étrangères sont justement les colonnes utilisées dans la plupart des ON de jointure.

Deux règles plus fines pour aller encore plus loin

UNIQUE interdit les doublons sur une colonne, comme deux comptes avec le même email. CHECK va plus loin encore en validant une règle métier arbitraire, comme "le prix ne peut pas être négatif".

Contrainte	Empêche	Exemple
`PRIMARY KEY`	Doublons + NULL sur l'identifiant	`id INTEGER PRIMARY KEY`
`FOREIGN KEY`	Référence vers une ligne inexistante	`client_id` doit exister dans `clients`
`UNIQUE`	Doublons sur une colonne	deux comptes avec le même `email`
`CHECK`	Valeurs métier invalides	`prix >= 0`, `date_depart < date_retour`
`NOT NULL`	Valeur manquante	`titre` obligatoire sur un article

Piège fréquent

Ajouter une contrainte FOREIGN KEY avec ALTER TABLE sur une table qui contient déjà des données incohérentes échoue immédiatement : il faut d'abord nettoyer ou corriger les lignes orphelines existantes avant que la contrainte puisse être créée.

Le résultat : un rempart indépendant du code

Ensemble, ces quatre contraintes protègent l'intégrité des données quoi qu'il arrive côté application. C'est un prérequis essentiel avant d'aborder les transactions, qui garantissent elles aussi la cohérence, mais à un niveau différent : celui de plusieurs opérations exécutées ensemble, sujet de la prochaine leçon.

Commandes & code

Contraintes d'intégrité

-- Clé primaire : identifie une ligne de manière unique, jamais NULL
CREATE TABLE clients (
    id   INTEGER PRIMARY KEY,       -- clé primaire simple
    email VARCHAR(255) NOT NULL
);

-- Clé primaire composite (utile pour les tables de liaison many-to-many)
CREATE TABLE inscriptions (
    etudiant_id INTEGER,
    cours_id    INTEGER,
    date_inscription DATE DEFAULT CURRENT_DATE,
    PRIMARY KEY (etudiant_id, cours_id)
);

-- Clé étrangère : garantit qu'une valeur référence bien une ligne existante
CREATE TABLE commandes (
    id         INTEGER PRIMARY KEY,
    client_id  INTEGER NOT NULL,
    montant    DECIMAL(10, 2),
    FOREIGN KEY (client_id) REFERENCES clients(id)
);

-- Comportements en cascade
CREATE TABLE commentaires (
    id          INTEGER PRIMARY KEY,
    article_id  INTEGER,
    FOREIGN KEY (article_id) REFERENCES articles(id)
        ON DELETE CASCADE       -- supprimer les commentaires si l'article est supprimé
        ON UPDATE CASCADE       -- répercuter le changement d'id
);
-- Autres options : ON DELETE SET NULL, ON DELETE RESTRICT (bloque la suppression), ON DELETE NO ACTION

-- UNIQUE : interdit les doublons sur une colonne (ou une combinaison)
CREATE TABLE utilisateurs (
    id    INTEGER PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL
);

ALTER TABLE inscriptions ADD CONSTRAINT uq_etudiant_cours UNIQUE (etudiant_id, cours_id);

-- CHECK : valide une condition métier au niveau base
CREATE TABLE produits (
    id     INTEGER PRIMARY KEY,
    prix   DECIMAL(10, 2) CHECK (prix >= 0),
    stock  INTEGER CHECK (stock >= 0),
    remise DECIMAL(4, 2) CHECK (remise BETWEEN 0 AND 100)
);

-- CHECK multi-colonnes
ALTER TABLE reservations
ADD CONSTRAINT chk_dates CHECK (date_depart < date_retour);

-- NOT NULL et DEFAULT
CREATE TABLE articles (
    id       INTEGER PRIMARY KEY,
    titre    VARCHAR(200) NOT NULL,
    vues     INTEGER NOT NULL DEFAULT 0,
    publie   BOOLEAN NOT NULL DEFAULT FALSE
);

-- Ajouter une contrainte après coup (échoue si des données existantes la violent)
ALTER TABLE produits ADD CONSTRAINT fk_categorie
    FOREIGN KEY (categorie_id) REFERENCES categories(id);

-- Nommer explicitement ses contraintes facilite le débogage des erreurs
ALTER TABLE commandes
ADD CONSTRAINT fk_commandes_client
    FOREIGN KEY (client_id) REFERENCES clients(id);

Résumé

Clé primaire = unicité + non-NULL garantis, souvent indexée automatiquement.
Clé étrangère = intégrité référentielle ; ON DELETE CASCADE/SET NULL/RESTRICT définit le comportement en cascade.
UNIQUE autorise plusieurs NULL (selon le SGBD) contrairement à PRIMARY KEY.
CHECK encode des règles métier directement en base, indépendamment de l'application.
Nommer ses contraintes (CONSTRAINT nom ...) simplifie la lecture des erreurs en production.

Exercices pratiques

1 disponible

Mission : blinder le schéma d'une application de réservation de salles

Objectif : Ajouter les contraintes qui empêchent des réservations incohérentes, et diagnostiquer un échec d'ajout de clé étrangère.

Contexte

L'application de réservation de salles a laissé passer une réservation avec une date_depart postérieure à sa date_retour, à cause d'un bug front-end. Tu dois ajouter les contraintes manquantes sur les tables reservations et salles pour qu'un bug applicatif similaire ne puisse plus jamais corrompre les données.

Résoudre l’exercice →