Cours MariaDB / Relier les tables avec des clés primaires et étrangères

Pour relier des tables dans MariaDB, donnez à chaque entité une clé primaire et placez une clé étrangère dans la table qui la référence. Dans une boutique, un client peut passer plusieurs commandes et chaque commande contient des lignes de produits : ces liens permettent de retrouver les informations sans recopier les noms partout, tout en refusant les références inexistantes.

Définir ce que représente une ligne

Avant d’écrire les contraintes, décidez de la signification d’une ligne. Une commande entière et un produit commandé ne sont pas la même chose : une commande peut contenir plusieurs produits, avec des quantités et des prix différents.

Table Une ligne représente Identification et liens
c25_customers Un client Clé primaire : customer_id
c25_products Un produit du catalogue Clé primaire : product_id
c25_orders Une commande, avec sa date et son statut Clé primaire : order_id ; clé étrangère : customer_id
c25_items Un produit dans une commande, avec quantité et prix d’achat Clé primaire composée : (order_id, product_id) ; deux clés étrangères vers commande et produit

Une clé primaire identifie une ligne sans doublon et sans valeur NULL. Une clé étrangère est une contrainte qui vérifie un lien vers une autre table. La table contenant cette référence est dite « enfant » ; la table référencée est dite « parente ». Ces rôles dépendent du lien : c25_orders est enfant des clients et parente des lignes de commande. Les clés et contraintes de MariaDB assurent ainsi des fonctions complémentaires.

Un client peut avoir zéro, une ou plusieurs commandes. Une commande peut être reliée à plusieurs lignes ; chaque ligne référence exactement une commande et un produit dans ce modèle. Un produit peut figurer dans plusieurs commandes. La table c25_items sert donc d’association entre commandes et produits.

Un client peut avoir plusieurs commandes ; chaque ligne de commande référence une commande et un produit.

Stocker « produit 1, produit 2, produit 3 » dans une seule colonne compliquerait la recherche, les quantités et les contrôles. Les lignes séparées permettent à MariaDB de vérifier chaque référence et de réunir les informations avec une jointure. De même, customer_id reste un identifiant même si le nom du client change ; un nom n’est pas un identifiant fiable, car plusieurs personnes peuvent porter le même.

Le champ c25_products.price indique le prix du catalogue, tandis que c25_items.unit_price conserve le prix unitaire au moment de l’achat. Modifier le catalogue ne doit pas recalculer les anciennes commandes. Cette copie a donc un rôle historique précis. En revanche, la clé composée interdit deux lignes portant la même paire commande-produit : pour gérer plusieurs variantes ou plusieurs lignes distinctes du même produit, il faudrait adapter le modèle, par exemple avec un identifiant de ligne et une référence de variante.

Créer les quatre tables et leurs données d’essai

Utilisez une base d’apprentissage shopcourse et le compte course_user préparés dans le cours. Le compte doit pouvoir créer, remplir, interroger et supprimer les tables de cette base. La connexion au client MariaDB et l’exécution de SQL permettent de vérifier la connexion avant de commencer. Ne lancez pas ce jeu d’essai dans une base de production.

  1. Vérifiez que la base courante est shopcourse et que les quatre noms c25_customers, c25_products, c25_orders et c25_items ne sont pas déjà utilisés par des données à conserver. Si une table existe, inspectez-la plutôt que de la supprimer pour faire disparaître l’erreur.
  2. Copiez le bloc suivant dans un fichier UTF-8, par exemple c25-relations.sql, puis exécutez-le dans le client MariaDB avec SOURCE suivi du chemin complet du fichier et d’un point-virgule. Sur Windows, utilisez des barres obliques dans ce chemin. Vous pouvez aussi exécuter tout le bloc dans un éditeur SQL connecté à la base.
  3. Créez et remplissez les tables dans l’ordre indiqué : clients et produits d’abord, commandes ensuite, lignes de commande en dernier. Les lignes parentes doivent exister avant les insertions qui les référencent.
USE shopcourse;
CREATE TABLE c25_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c25_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c25_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c25_order_customer FOREIGN KEY (customer_id)
  REFERENCES c25_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c25_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c25_item_order FOREIGN KEY (order_id)
  REFERENCES c25_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c25_item_product FOREIGN KEY (product_id)
  REFERENCES c25_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c25_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c25_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c25_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c25_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

Les noms et catégories en coréen sont des données du jeu d’essai commun aux traductions ; conservez-les pour reproduire les résultats. Les catégories 문구 et 생활 désignent respectivement la papeterie et les articles du quotidien. Les quatre clients et quatre produits constituent le catalogue de départ ; les quatre commandes contiennent six lignes au total. Les montants sont des nombres d’exercice, sans conversion monétaire.

Chaque table utilise ENGINE=InnoDB, qui prend en charge ces clés étrangères. Les colonnes liées sont toutes des INT signés et les colonnes référencées sont des clés primaires. Les contraintes nommées fk_c25_order_customer, fk_c25_item_order et fk_c25_item_product facilitent l’identification d’un lien dans la définition de table ou un message d’erreur.

Les conditions CHECK portent sur les valeurs : quantité strictement positive, prix et stock non négatifs. Les clés étrangères portent sur l’existence des références. Une quantité positive ne prouve donc pas qu’un produit existe ; inversement, une référence valide ne garantit pas que le stock suffit pour la vente.

Lire les contraintes et vérifier les relations

Commencez par la définition réellement enregistrée, puis observez les données. Le bloc suivant affiche les contraintes des lignes de commande, rapproche clients et commandes, et compte les enregistrements.

SHOW CREATE TABLE c25_items;
SELECT c.customer_id,c.customer_name,o.order_id
FROM c25_customers AS c JOIN c25_orders AS o
 ON o.customer_id=c.customer_id ORDER BY c.customer_id,o.order_id;
SELECT COUNT(*) AS customers FROM c25_customers;
SELECT COUNT(*) AS orders_count FROM c25_orders;
SELECT COUNT(*) AS detail_rows FROM c25_items;

Dans le résultat de SHOW CREATE TABLE c25_items, recherchez la clé primaire composée et les deux contraintes FOREIGN KEY, avec leurs tables référencées et ON DELETE RESTRICT. MariaDB peut ajouter des index nécessaires aux clés étrangères et présenter la définition dans un ordre ou avec une mise en forme différents du texte saisi.

La jointure utilise o.customer_id=c.customer_id : elle associe les identifiants, et non les noms affichés. Elle renvoie les quatre lignes suivantes, même si la table des clients en contient elle aussi quatre.

customer_id customer_name order_id
1 김민수 101
1 김민수 102
2 이서연 103
3 박지우 104

Le client 1 apparaît deux fois parce qu’il a deux commandes. Le client 4 n’apparaît pas : il n’a encore aucune commande et cette jointure interne ne conserve que les correspondances. Ce résultat ne signifie pas que le client 4 a été supprimé. Les trois comptages doivent donner respectivement 4 clients, 4 commandes et 6 lignes de commande.

Pour suivre les liens dans les données initiales, la commande 101 contient les produits 1 et 2, tandis que le produit 1 est présent dans les commandes 101 et 103. C’est la paire (order_id, product_id) qui identifie chaque ligne ; ni l’un ni l’autre de ces deux champs ne serait unique à lui seul dans c25_items.

Provoquer deux erreurs pour vérifier la protection

Une contrainte doit aussi refuser une opération incohérente. Exécutez chacune des deux instructions suivantes séparément : certains outils s’arrêtent à la première erreur et n’exécuteraient donc pas la seconde.

INSERT INTO c25_orders(order_id,customer_id,ordered_at,status)
VALUES(999,999,'2026-03-01 10:00:00','paid');

DELETE FROM c25_customers WHERE customer_id=1;

La première instruction tente d’insérer la commande 999 pour le client 999, absent de c25_customers. MariaDB la refuse avec une erreur de clé étrangère, normalement 1452. La commande 999 ne doit pas être enregistrée ; la valeur par défaut des frais de livraison ne change rien à cette référence invalide.

La seconde instruction tente de supprimer le client 1 alors que les commandes 101 et 102 le référencent. Le choix ON DELETE RESTRICT bloque cette suppression, normalement avec l’erreur 1451. Le client 1 et ses commandes restent présents. Relancez les comptages et la jointure précédents : ils doivent conserver les mêmes résultats après ces deux échecs.

Le nom de contrainte affiché aide à identifier la relation en cause. En cas d’échec inattendu dans vos propres données, vérifiez la base sélectionnée, l’identifiant parent, les types de colonnes et la définition obtenue avec SHOW CREATE TABLE. Ne désactivez pas foreign_key_checks pour faire passer les insertions de l’exercice : cela retirerait précisément la protection que vous cherchez à comprendre.

Choisir la règle de suppression et ses limites

Dans ce modèle, RESTRICT évite de supprimer un client encore utilisé par une commande, ou un produit encore utilisé par une ligne. Il faut définir une politique de conservation avant de modifier cette règle. L’annulation d’une commande, par exemple le statut canceled de la commande 104, est un état métier : elle ne supprime automatiquement ni la commande ni ses lignes.

  • RESTRICT refuse la suppression d’une ligne parente encore référencée. Il convient ici pour conserver l’historique des commandes.
  • CASCADE propage la suppression aux lignes enfants concernées. Cette propagation peut être utile pour des données strictement dépendantes, mais son effet doit être étudié sur toute la chaîne de relations avant utilisation.
  • SET NULL conserve les lignes enfants en mettant leur référence à NULL. Cette règle suppose que la colonne puisse être nulle ; elle ne convient pas telle quelle aux références déclarées NOT NULL dans cet exercice.

Les détails et conditions de ces règles figurent dans la documentation MariaDB sur les clés étrangères. Pour reproduire ce modèle, conservez le moteur InnoDB et des types compatibles ; pour les entiers, la taille et le caractère signé ou non signé doivent correspondre. Une colonne simplement appelée customer_id ne crée aucune contrainte à elle seule : vérifiez la définition enregistrée et le refus effectif d’une référence invalide.

Ces contraintes ne remplacent pas toute la logique de l’application. Elles ne vérifient ni qu’une commande payée possède au moins une ligne, ni que le paiement est confirmé, ni que la quantité commandée a été réservée dans le stock. Une clé étrangère impose un parent à une ligne enfant ; elle n’impose pas qu’un parent possède déjà des enfants.

Représenter l’absence de relation et nettoyer l’exercice

Le client 4 peut exister avant sa première commande et le produit 4 avant sa première vente. Il suffit qu’aucune ligne enfant ne les référence encore. En revanche, une commande de cet exemple doit indiquer un client existant, car customer_id est à la fois une clé étrangère et NOT NULL. Sans cette dernière condition, une référence nulle pourrait représenter une absence de parent ; elle ne serait pas équivalente à un identifiant inexistant.

Après avoir relevé les résultats, vous pouvez supprimer uniquement les tables d’apprentissage avec le bloc suivant. Vérifiez d’abord leurs noms et leur contenu, et exportez toute donnée à conserver. DROP TABLE supprime la structure et les données ; un ROLLBACK ordinaire ne permet pas d’annuler cette suppression.

DROP TABLE shopcourse.c25_items;
DROP TABLE shopcourse.c25_orders;
DROP TABLE shopcourse.c25_products;
DROP TABLE shopcourse.c25_customers;

L’ordre inverse de la création respecte les dépendances : les lignes de commande disparaissent avant les commandes et les produits, puis les clients peuvent être supprimés. Une tentative de suppression d’une table parente encore référencée peut être refusée, même si vous pensez ne plus avoir besoin de ses données. Le nettoyage est limité aux quatre noms c25_ et ne vise pas les autres tables du cours. Pour retrouver l’état initial après ce nettoyage, réexécutez le premier bloc complet, puis vérifiez à nouveau les nombres 4, 4 et 6.