Définir les clés et les contraintes d’intégrité

Un schéma normalisé sépare les faits, mais cette séparation ne suffit pas à empêcher toutes les incohérences. Par exemple, un emprunt pourrait désigner un adhérent inexistant, ou une même carte pourrait être attribuée à deux personnes. Vous allez traduire les règles du modèle en contraintes que MariaDB vérifie à chaque écriture.

Les entités et leurs associations ont été définies dans « Décomposer les règles métier en relations », puis séparées selon la troisième forme normale dans « Normaliser les tables sans perdre d’information ». Ici, vous passez du modèle à des définitions SQL persistantes, sans reprendre ces étapes de conception.

Distinguer identifiants et contraintes

Chaque table reçoit une clé primaire, ici un identifiant technique distinct. Elle identifie une ligne sans dépendre d’une propriété descriptive susceptible de changer. Une clé candidate est une autre valeur, ou combinaison de valeurs, qui pourrait identifier une ligne de façon unique ; dans ce schéma, le numéro de carte d’un adhérent et la cote d’un exemplaire jouent ce rôle et sont déclarés UNIQUE.

Une clé étrangère relie une colonne enfant à une clé d’une table parent et interdit de référencer une ligne absente. Vous nommez explicitement chaque clé primaire et étrangère pour faciliter la lecture des erreurs et la maintenance du schéma. NOT NULL exige une valeur ; UNIQUE interdit les doublons ; CHECK vérifie une condition sur les valeurs de la ligne.

Créer les tables et protéger les associations

Le script suivant constitue un schéma complet pour cette étape, dans le fichier sql/schema.sql. L’ordre de création respecte les dépendances : les tables référencées sont créées avant leurs tables enfants. Les noms de contraintes sont explicites et les tables utilisent InnoDB afin de prendre en charge les clés étrangères.

-- sql/schema.sql
-- Schéma relationnel de la médiathèque — MariaDB 11

CREATE TABLE adherents (
    adherent_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    numero_carte VARCHAR(30) NOT NULL,
    nom VARCHAR(120) NOT NULL,
    PRIMARY KEY (adherent_id),
    CONSTRAINT uq_adherents_numero_carte UNIQUE (numero_carte)
) ENGINE = InnoDB;

CREATE TABLE oeuvres (
    oeuvre_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    titre VARCHAR(200) NOT NULL,
    PRIMARY KEY (oeuvre_id)
) ENGINE = InnoDB;

CREATE TABLE exemplaires (
    exemplaire_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    oeuvre_id BIGINT UNSIGNED NOT NULL,
    cote VARCHAR(40) NOT NULL,
    PRIMARY KEY (exemplaire_id),
    CONSTRAINT uq_exemplaires_cote UNIQUE (cote),
    CONSTRAINT fk_exemplaires_oeuvres
        FOREIGN KEY (oeuvre_id)
        REFERENCES oeuvres (oeuvre_id)
        ON UPDATE RESTRICT
        ON DELETE RESTRICT
) ENGINE = InnoDB;

CREATE TABLE emprunts (
    emprunt_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    adherent_id BIGINT UNSIGNED NOT NULL,
    exemplaire_id BIGINT UNSIGNED NOT NULL,
    date_emprunt DATE NOT NULL,
    date_retour_prevue DATE NOT NULL,
    date_retour DATE NULL,
    PRIMARY KEY (emprunt_id),
    CONSTRAINT chk_emprunts_dates CHECK (
        date_retour_prevue >= date_emprunt
        AND (date_retour IS NULL OR date_retour >= date_emprunt)
    ),
    CONSTRAINT fk_emprunts_adherents
        FOREIGN KEY (adherent_id)
        REFERENCES adherents (adherent_id)
        ON UPDATE RESTRICT
        ON DELETE RESTRICT,
    CONSTRAINT fk_emprunts_exemplaires
        FOREIGN KEY (exemplaire_id)
        REFERENCES exemplaires (exemplaire_id)
        ON UPDATE RESTRICT
        ON DELETE RESTRICT
) ENGINE = InnoDB;

CREATE TABLE retards (
    retard_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    emprunt_id BIGINT UNSIGNED NOT NULL,
    date_constat DATE NOT NULL,
    jours_retard INT UNSIGNED NOT NULL,
    PRIMARY KEY (retard_id),
    CONSTRAINT uq_retards_emprunt_date UNIQUE (emprunt_id, date_constat),
    CONSTRAINT chk_retards_jours CHECK (jours_retard > 0),
    CONSTRAINT fk_retards_emprunts
        FOREIGN KEY (emprunt_id)
        REFERENCES emprunts (emprunt_id)
        ON UPDATE RESTRICT
        ON DELETE RESTRICT
) ENGINE = InnoDB;

Les colonnes qui définissent une association sont NOT NULL : un emprunt ne peut donc pas exister sans adhérent ni exemplaire. À l’inverse, date_retour est nullable, car un emprunt en cours n’a pas encore de date de retour. Ce choix représente un état métier ; les autres règles concernant les valeurs nulles et les valeurs par défaut seront détaillées dans la leçon consacrée aux types.

Choisir les règles de suppression et de modification

Les règles ON DELETE RESTRICT empêchent de supprimer une œuvre encore liée à un exemplaire, un adhérent ou un exemplaire ayant un emprunt, ou un emprunt ayant un retard. Ce comportement préserve l’historique au lieu d’effacer silencieusement des données liées. ON UPDATE RESTRICT empêche également de modifier une clé référencée tant que des lignes enfants la désignent ; les identifiants techniques n’ont normalement pas à changer.

Les contraintes CHECK ci-dessus valident les lignes indépendamment des autres tables : les dates de retour ne précèdent pas la date d’emprunt, et un retard enregistré comporte un nombre positif de jours. Une contrainte de ligne ne peut pas vérifier, par exemple, si l’exemplaire est déjà prêté dans une autre ligne d’emprunt ; cette règle concerne les opérations de prêt et sera traitée dans les leçons sur les transactions et les verrous.

À retenir

• Déclarez une clé primaire par table et utilisez des contraintes UNIQUE pour les autres valeurs réellement candidates à l’unicité.
• Rendez obligatoires les associations nécessaires avec NOT NULL et des clés étrangères nommées explicitement.
• Utilisez CHECK pour les règles vérifiables à partir des colonnes de la ligne.
• Choisissez RESTRICT ou CASCADE selon la règle métier, en protégeant particulièrement l’historique des emprunts.

Dans la prochaine leçon, vous choisirez les types de colonnes et préciserez la représentation des retards et des valeurs absentes.