Modélisation SQL : clés primaires et clés étrangères

Une base mal conçue se venge longtemps : données en double, incohérences silencieuses, commandes orphelines sans client, requêtes impossibles à écrire. À l inverse, un schéma bien modélisé rend les erreurs structurellement impossibles et les requêtes naturelles. La modélisation, ce sont les décisions de structure : quelles tables, quelles colonnes, quelles clés, quelles contraintes. C est l investissement qui rapporte le plus sur la durée de vie d une application.

Ce tutoriel vous apprend à concevoir un schéma sain : clés primaires, clés étrangères, intégrité référentielle, contraintes et normalisation. Nous reconstruisons proprement la base TechStock pour comprendre chaque décision.

🎯 Ce que vous allez apprendre

• Identifier les entités et leurs relations à partir d un besoin.
• Définir une clé primaire et choisir entre clé naturelle et artificielle.
• Déclarer des clés étrangères qui garantissent l intégrité référentielle.
• Maîtriser les actions ON DELETE et ON UPDATE.
• Poser les contraintes NOT NULL, UNIQUE, CHECK et DEFAULT.
• Modéliser les relations un-à-plusieurs et plusieurs-à-plusieurs, et comprendre la normalisation.

🛠️ Ce que vous allez construire

Vous allez reconstruire le schéma complet de la boutique, cette fois en justifiant chaque clé et chaque contrainte. À la fin, votre base refusera d elle-même les données incohérentes : un prix négatif, une commande sans client, une catégorie en double. C est la fondation sur laquelle reposent toutes les requêtes précédentes.

Prérequis

• Comprendre les jointures (les jointures) pour saisir le rôle des clés.
• Un moteur PostgreSQL (les contraintes y sont strictement appliquées).
• ⏱️ Temps estimé : environ 55 minutes.

Étape 1 — Des entités aux tables

La modélisation commence loin du clavier, par une question simple : de quoi parle mon application ? Pour une boutique, les noms qui reviennent sont client, produit, catégorie, commande. Chacun de ces concepts devient une entité, donc une table. Les verbes qui les relient (un client passe une commande, une commande contient des produits) deviennent des relations, matérialisées par des clés étrangères.

La règle d or à ce stade est la séparation des préoccupations : une information ne doit être stockée qu à un seul endroit. Mettre le nom du client dans chaque commande serait une faute : s il change de nom, il faudrait corriger partout, avec le risque d oublis. On stocke le client une fois, et les commandes le référencent par son identifiant. Cette discipline, c est le cœur de la normalisation.

✅ Point d étape — Listez sur papier les entités de votre projet et tracez les relations entre elles. Ce schéma conceptuel guide tout le reste.

Étape 2 — La clé primaire

Chaque table a besoin d un moyen d identifier sans ambiguïté chacune de ses lignes : c est la clé primaire. Elle est unique et jamais nulle. On distingue les clés naturelles, tirées des données mêmes (un numéro de série), et les clés artificielles, générées par la base. En pratique, on privilégie presque toujours une clé artificielle auto-incrémentée, stable et neutre.

CREATE TABLE categories (
  id  INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  nom VARCHAR(60) NOT NULL UNIQUE
);

La clause GENERATED ALWAYS AS IDENTITY, conforme à la norme SQL, demande au moteur d attribuer automatiquement un identifiant croissant à chaque insertion. PostgreSQL propose aussi le type historique SERIAL qui produit un résultat équivalent. La contrainte UNIQUE sur nom garantit qu on ne créera pas deux catégories identiques.

✅ Point d étape — Insérez deux catégories puis tentez d en insérer une troisième avec un nom déjà pris : le moteur doit refuser, preuve que la contrainte UNIQUE veille.

Étape 3 — La clé étrangère et l intégrité référentielle

La clé étrangère est ce qui relie deux tables. Une colonne de la table enfant pointe vers la clé primaire de la table parent. Le moteur garantit alors l intégrité référentielle : il devient impossible d insérer un produit référençant une catégorie inexistante.

CREATE TABLE produits (
  id           INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  nom          VARCHAR(120) NOT NULL,
  prix         NUMERIC(10,2) NOT NULL CHECK (prix >= 0),
  stock        INTEGER NOT NULL DEFAULT 0,
  categorie_id INTEGER NOT NULL
               REFERENCES categories(id)
);

La ligne REFERENCES categories(id) crée la clé étrangère. Désormais, toute tentative d insérer un produit avec un categorie_id absent de la table categories est rejetée. C est le moteur, et non votre code applicatif, qui protège la cohérence : la garantie est absolue, même en cas de bug ailleurs.

✅ Point d étape — Essayez d insérer un produit avec un categorie_id farfelu comme 999 : le rejet du moteur confirme que la clé étrangère fonctionne.

Étape 4 — Maîtriser ON DELETE et ON UPDATE

Que se passe-t-il si l on supprime une catégorie alors que des produits y réfèrent ? Par défaut, le moteur refuse, pour ne pas créer d orphelins. Mais on peut choisir un autre comportement avec les clauses ON DELETE et ON UPDATE. Les options les plus utiles sont NO ACTION (refuser, le comportement par défaut), RESTRICT (refuser également, mais sans report possible du contrôle au sein d une transaction), CASCADE (propager la suppression aux enfants) et SET NULL (vider la référence). Une précision d ordre pratique : une clé étrangère exige que la table cible existe déjà. La table commandes ci-dessous référence clients, que nous détaillons à l étape suivante ; si vous reconstruisez la base depuis zéro, créez donc clients avant commandes (l ordre du schéma complet est celui du guide : categories, produits, clients, commandes, lignes_commande).

CREATE TABLE commandes (
  id          INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  client_id   INTEGER NOT NULL
              REFERENCES clients(id) ON DELETE RESTRICT,
  date_commande TIMESTAMP NOT NULL DEFAULT now(),
  statut      VARCHAR(20) NOT NULL DEFAULT 'en_attente'
);
CREATE TABLE lignes_commande (
  id           INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  commande_id  INTEGER NOT NULL
               REFERENCES commandes(id) ON DELETE CASCADE,
  produit_id   INTEGER NOT NULL REFERENCES produits(id),
  quantite     INTEGER NOT NULL CHECK (quantite > 0),
  prix_unitaire NUMERIC(10,2) NOT NULL
);

Ici, on interdit de supprimer un client qui a des commandes (RESTRICT), pour préserver l historique. En revanche, supprimer une commande efface automatiquement ses lignes (CASCADE), car une ligne n a aucun sens sans sa commande. Choisir la bonne action par relation est une décision de conception lourde de conséquences : réfléchissez à ce qui doit arriver aux enfants quand le parent disparaît.

✅ Point d étape — Créez une commande avec deux lignes, supprimez la commande, puis vérifiez que les lignes ont disparu d elles-mêmes. Le CASCADE a joué son rôle.

Étape 5 — Les contraintes qui protègent les données

Au-delà des clés, plusieurs contraintes verrouillent la qualité des données dès l écriture. NOT NULL impose une valeur. UNIQUE interdit les doublons. CHECK impose une règle métier, comme un prix positif. DEFAULT fournit une valeur automatique. Vous les avez déjà vues à l œuvre ci-dessus ; réunissons-les sur la table clients.

CREATE TABLE clients (
  id              INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  nom             VARCHAR(120) NOT NULL,
  email           VARCHAR(160) UNIQUE,
  ville           VARCHAR(80),
  date_inscription DATE NOT NULL DEFAULT CURRENT_DATE
);

Cette table refuse un client sans nom, deux clients partageant le même e-mail, et remplit seule la date d inscription. Chaque contrainte est une règle métier gravée dans la structure : elle s applique quoi qu il arrive, ce qu aucune validation côté application ne peut garantir aussi sûrement. C est l un des plus grands atouts du relationnel.

✅ Point d étape — Insérez un client sans préciser la date : elle doit se remplir automatiquement à la date du jour, grâce au DEFAULT.

Étape 6 — Un-à-plusieurs et plusieurs-à-plusieurs

La relation la plus courante est un-à-plusieurs : un client a plusieurs commandes, mais une commande appartient à un seul client. On la modélise en plaçant la clé étrangère du côté « plusieurs », ici client_id dans commandes. C est exactement ce que nous avons fait.

La relation plusieurs-à-plusieurs demande une table intermédiaire. Une commande contient plusieurs produits, et un produit apparaît dans plusieurs commandes : impossible de placer une simple clé étrangère d un côté. La solution est une table de liaison, et c est précisément le rôle de lignes_commande : chacune de ses lignes relie une commande à un produit, avec en prime des attributs propres à l association comme la quantité.

-- La table de liaison résout le plusieurs-à-plusieurs
-- commandes <--- lignes_commande ---> produits
SELECT cmd.id, p.nom, lc.quantite
FROM lignes_commande AS lc
JOIN commandes AS cmd ON cmd.id = lc.commande_id
JOIN produits AS p    ON p.id = lc.produit_id;

Reconnaître une relation plusieurs-à-plusieurs et la modéliser par une table de liaison est l un des réflexes les plus importants du concepteur. Toute tentative de l éviter (par exemple en stockant une liste de produits dans une colonne) mène droit aux ennuis.

✅ Point d étape — Vérifiez que vous pouvez associer le même produit à deux commandes différentes sans dupliquer le produit. C est la signature d une table de liaison réussie.

Étape 7 — Les trois premières formes normales

La normalisation est l ensemble des règles qui éliminent la redondance. Trois étapes suffisent dans l immense majorité des cas. La première forme normale exige des valeurs atomiques : pas de liste dans une colonne, une donnée par cellule. La deuxième demande que chaque colonne dépende de toute la clé primaire, pas d une partie. La troisième impose que les colonnes ne dépendent que de la clé, et non d une autre colonne ordinaire.

Concrètement, stocker le nom de la catégorie directement dans la table produits violerait la troisième forme normale, car ce nom dépend de la catégorie et non du produit. La bonne modélisation sépare donc les catégories dans leur propre table, référencée par une clé étrangère, ce que nous avons fait dès l étape deux. Respecter ces trois formes évite la quasi-totalité des anomalies de mise à jour.

Faire évoluer un schéma existant

Un schéma n est jamais figé. La commande ALTER TABLE ajoute une colonne, une contrainte ou une clé étrangère après coup, sans tout recréer. Par exemple, ajouter une clé étrangère oubliée :

ALTER TABLE produits
  ADD CONSTRAINT fk_produit_categorie
  FOREIGN KEY (categorie_id) REFERENCES categories(id);

Donner un nom explicite à la contrainte facilite sa gestion future. Sur une base de production, toute modification de structure se prépare avec soin et se teste d abord sur une copie, car certaines opérations peuvent verrouiller la table.

🐞 Pièges fréquents

🌍 Réalités du terrain

Un point de modélisation a des conséquences très concrètes : le stockage des montants. Pour tout ce qui touche à l argent, y compris les paiements par mobile money, utilisez impérativement le type NUMERIC à précision fixée, jamais un type à virgule flottante. La raison est mathématique : les flottants ne représentent pas exactement la plupart des décimaux, et les erreurs s accumulent sur les additions, produisant des centimes fantômes inacceptables sur une facture. Pensez aussi à stocker les montants de manière cohérente à travers toute la base. Enfin, modélisez dès le départ les contraintes métier dans la structure : une base qui refuse elle-même un stock négatif ou un e-mail en double vous épargnera d innombrables bugs, même quand l application évoluera ou sera réécrite.

✅ Récapitulatif

Vous savez désormais concevoir un schéma sain : identifier entités et relations, poser des clés primaires, relier les tables par des clés étrangères qui garantissent l intégrité, choisir les actions ON DELETE, verrouiller la qualité avec NOT NULL, UNIQUE, CHECK et DEFAULT, modéliser les relations plusieurs-à-plusieurs par une table de liaison, et appliquer les trois formes normales. Une base bien conçue rend les requêtes simples et les erreurs impossibles. La dernière étape du parcours montre comment la rendre rapide grâce aux index.

🧾 Aide-mémoire

💪 À vous de jouer

1. Ajoutez une contrainte CHECK garantissant qu un stock n est jamais négatif.
2. Modélisez une relation entre produits et fournisseurs (un produit peut avoir plusieurs fournisseurs).
3. Ajoutez une colonne email obligatoire et unique à une table existante.

-- 1
ALTER TABLE produits
  ADD CONSTRAINT chk_stock_positif CHECK (stock >= 0);

-- 2 : plusieurs-à-plusieurs via table de liaison
CREATE TABLE fournisseurs (
  id  INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  nom VARCHAR(120) NOT NULL
);
CREATE TABLE produit_fournisseur (
  produit_id    INTEGER REFERENCES produits(id) ON DELETE CASCADE,
  fournisseur_id INTEGER REFERENCES fournisseurs(id) ON DELETE CASCADE,
  PRIMARY KEY (produit_id, fournisseur_id)
);

-- 3
ALTER TABLE clients
  ADD COLUMN email VARCHAR(160) NOT NULL UNIQUE;

Tutoriels associés

• Décomposer un problème avec les sous-requêtes et les CTE
• Accélérer une requête avec les index et le plan d exécution

Pour aller plus loin

• 🔝 Retour au guide : Apprendre le SQL
• Contraintes PostgreSQL : postgresql.org
• Clés étrangères : tutoriel officiel

FAQ

Clé naturelle ou artificielle ? Une clé artificielle auto-incrémentée est presque toujours préférable : stable, neutre, jamais modifiée. Les clés naturelles changent parfois, ce qui propage des mises à jour partout.

Faut-il toujours tout normaliser ? Visez la troisième forme normale par défaut. Une dénormalisation ciblée peut se justifier pour la performance, mais seulement après mesure et en connaissance de cause.

CASCADE est-il dangereux ? Il est puissant et pratique pour les enfants dépendants, mais une suppression peut en entraîner beaucoup d autres. Réservez-le aux relations où l enfant n a aucun sens sans son parent.

Pourquoi mettre les contraintes dans la base plutôt que dans le code ? Parce que la base est la dernière ligne de défense : elle protège les données quel que soit le code qui y accède, aujourd hui ou demain.

Mots-clés : modélisation base de données, clé primaire, clé étrangère, intégrité référentielle, normalisation, contraintes SQL.