Clé primaire et clé étrangère en SQL : le guide

Dès qu’une base de données contient plus d’une table, une question se pose : comment relier les lignes entre elles sans se tromper ? La réponse tient en deux concepts que tout schéma relationnel utilise en permanence : la clé primaire et la clé étrangère. La première identifie chaque ligne de façon unique, la seconde crée un lien fiable vers cette identité depuis une autre table.

Ce guide part des définitions, montre la syntaxe exacte en SQL, puis déroule les trois grands types de relations (1-1, 1-N, N-N) et le mécanisme d’intégrité référentielle qui empêche votre base de se contredire. Tous les exemples sont exécutables tels quels sur MySQL ou PostgreSQL.

La clé primaire : identifier chaque ligne

Définition

Une clé primaire (PRIMARY KEY) est une colonne, ou un groupe de colonnes, dont la valeur identifie de façon unique chaque ligne d’une table. Elle respecte deux règles absolues : elle ne peut jamais être NULL, et deux lignes ne peuvent jamais partager la même valeur. C’est la carte d’identité de la ligne.

CREATE TABLE clients (
    id        INT PRIMARY KEY,
    nom       VARCHAR(100) NOT NULL,
    email     VARCHAR(255) NOT NULL
);

Ici, id sert de clé primaire. Deux clients ne pourront jamais avoir le même id, et aucun client ne pourra exister sans id.

La clé auto-incrémentée

En pratique, on laisse rarement l’application choisir l’identifiant : on demande à la base de le générer automatiquement. La syntaxe diffère légèrement selon le moteur.

-- MySQL
CREATE TABLE clients (
    id    INT AUTO_INCREMENT PRIMARY KEY,
    nom   VARCHAR(100) NOT NULL
);

-- PostgreSQL
CREATE TABLE clients (
    id    SERIAL PRIMARY KEY,
    nom   VARCHAR(100) NOT NULL
);

À chaque insertion, la base attribue le numéro suivant. Vous insérez simplement le nom, l’id se remplit seul.

La clé primaire composite

Rien n’oblige une clé primaire à tenir sur une seule colonne. Quand l’unicité découle de la combinaison de plusieurs champs, on déclare une clé composite. C’est fréquent pour les tables qui enregistrent une association.

CREATE TABLE inscriptions (
    etudiant_id INT,
    cours_id    INT,
    date_inscr  DATE,
    PRIMARY KEY (etudiant_id, cours_id)
);

Un même étudiant ne peut donc pas s’inscrire deux fois au même cours, mais il peut s’inscrire à plusieurs cours, et un cours peut accueillir plusieurs étudiants.

La clé étrangère : relier les tables

Définition

Une clé étrangère (FOREIGN KEY) est une colonne dont la valeur doit correspondre à une clé primaire existante dans une autre table. Elle matérialise le lien entre deux entités et garantit qu’on ne pointe jamais vers du vide.

CREATE TABLE commandes (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    client_id   INT NOT NULL,
    montant     DECIMAL(10,2),
    FOREIGN KEY (client_id) REFERENCES clients(id)
);

La colonne client_id de commandes est une clé étrangère qui pointe vers clients(id). Concrètement, la base refusera d’enregistrer une commande dont le client_id ne correspond à aucun client réel.

Le vocabulaire des deux côtés

On parle de table parent (celle qui détient la clé primaire, ici clients) et de table enfant (celle qui porte la clé étrangère, ici commandes). L’enfant dépend du parent : on ne peut pas rattacher une commande à un client inexistant.

Les trois types de relations

Relation un-à-plusieurs (1-N)

C’est la relation la plus courante. Un client passe plusieurs commandes, mais chaque commande appartient à un seul client. La clé étrangère se place toujours du côté « plusieurs ».

CREATE TABLE auteurs (
    id   INT AUTO_INCREMENT PRIMARY KEY,
    nom  VARCHAR(100)
);

CREATE TABLE livres (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    titre      VARCHAR(200),
    auteur_id  INT,
    FOREIGN KEY (auteur_id) REFERENCES auteurs(id)
);

Un auteur (auteurs) écrit plusieurs livres (livres), et chaque livre renvoie à un seul auteur via auteur_id.

Relation un-à-un (1-1)

Plus rare, elle relie deux tables où chaque ligne de l’une correspond à au plus une ligne de l’autre. On s’en sert souvent pour isoler des informations optionnelles ou sensibles. La clé étrangère porte alors une contrainte UNIQUE.

CREATE TABLE utilisateurs (
    id     INT AUTO_INCREMENT PRIMARY KEY,
    login  VARCHAR(50)
);

CREATE TABLE profils (
    id             INT AUTO_INCREMENT PRIMARY KEY,
    utilisateur_id INT UNIQUE,
    biographie     TEXT,
    FOREIGN KEY (utilisateur_id) REFERENCES utilisateurs(id)
);

Le UNIQUE sur utilisateur_id garantit qu’un utilisateur ne possède qu’un seul profil.

Relation plusieurs-à-plusieurs (N-N)

Quand chaque côté peut être lié à plusieurs éléments de l’autre — des étudiants suivant plusieurs cours, chaque cours ayant plusieurs étudiants —, on ne peut pas se contenter d’une clé étrangère. On introduit une table de liaison (ou table d’association) qui porte deux clés étrangères.

CREATE TABLE etudiants (
    id  INT AUTO_INCREMENT PRIMARY KEY,
    nom VARCHAR(100)
);

CREATE TABLE cours (
    id      INT AUTO_INCREMENT PRIMARY KEY,
    intitule VARCHAR(150)
);

CREATE TABLE etudiants_cours (
    etudiant_id INT,
    cours_id    INT,
    PRIMARY KEY (etudiant_id, cours_id),
    FOREIGN KEY (etudiant_id) REFERENCES etudiants(id),
    FOREIGN KEY (cours_id)    REFERENCES cours(id)
);

La table etudiants_cours transforme une relation N-N en deux relations 1-N. Sa clé primaire composite empêche les doublons d’inscription. Pour interroger ce genre de structure, vous vous appuierez ensuite sur les jointures SQL, qui recombinent ces tables à la lecture.

L’intégrité référentielle

Ce que la base garantit

L’intégrité référentielle est la promesse tenue par les clés étrangères : toute valeur de clé étrangère renvoie toujours vers une ligne parent existante. Sans elle, vous pourriez avoir des commandes rattachées à des clients supprimés, autrement dit des données orphelines et incohérentes.

Concrètement, deux opérations sont refusées par défaut :

-- Refusé : le client 999 n'existe pas
INSERT INTO commandes (client_id, montant) VALUES (999, 42.00);

-- Refusé : le client 1 a encore des commandes
DELETE FROM clients WHERE id = 1;

La base bloque ces actions parce qu’elles casseraient les liens.

Piloter le comportement avec ON DELETE et ON UPDATE

Vous pouvez décider de ce qui arrive aux enfants quand le parent est supprimé ou modifié, grâce aux clauses ON DELETE et ON UPDATE. Les options les plus utiles :

RESTRICT (ou NO ACTION) : bloque l’opération tant que des enfants existent. C’est le comportement par défaut, le plus sûr.
CASCADE : répercute l’opération. Supprimer un client supprime ses commandes ; modifier son id met à jour les commandes.
SET NULL : met la clé étrangère à NULL (la colonne doit être nullable).

CREATE TABLE commandes (
    id        INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT,
    montant   DECIMAL(10,2),
    FOREIGN KEY (client_id) REFERENCES clients(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

Avec ce schéma, supprimer un client efface automatiquement toutes ses commandes.

Choisir la bonne stratégie

CASCADE est pratique mais dangereux : une suppression innocente peut vider des tables entières en chaîne. Réservez-le aux cas où l’enfant n’a aucun sens sans son parent (les lignes d’une facture, par exemple). Pour des données que vous voulez conserver malgré la disparition du parent, SET NULL est plus prudent. En cas de doute, gardez RESTRICT : mieux vaut une erreur explicite qu’une perte de données silencieuse.

Un schéma complet en exemple

Rassemblons ces notions dans une mini-base de commerce en ligne, où l’on retrouve les trois types de relations.

CREATE TABLE clients (
    id    INT AUTO_INCREMENT PRIMARY KEY,
    nom   VARCHAR(100) NOT NULL
);

CREATE TABLE produits (
    id      INT AUTO_INCREMENT PRIMARY KEY,
    libelle VARCHAR(150),
    prix    DECIMAL(10,2)
);

CREATE TABLE commandes (
    id        INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    date_cmd  DATE,
    FOREIGN KEY (client_id) REFERENCES clients(id)
        ON DELETE RESTRICT
);

CREATE TABLE lignes_commande (
    commande_id INT,
    produit_id  INT,
    quantite    INT DEFAULT 1,
    PRIMARY KEY (commande_id, produit_id),
    FOREIGN KEY (commande_id) REFERENCES commandes(id) ON DELETE CASCADE,
    FOREIGN KEY (produit_id)  REFERENCES produits(id)
);

Lecture du schéma : un client passe plusieurs commandes (1-N), une commande contient plusieurs produits et un produit apparaît dans plusieurs commandes (N-N via lignes_commande). Supprimer une commande efface ses lignes (CASCADE), mais supprimer un client reste bloqué s’il a des commandes (RESTRICT).

En résumé

La clé primaire répond à « qui suis-je ? », la clé étrangère à « à qui suis-je rattaché ? ». À partir de ces deux briques, toute la modélisation relationnelle se construit : une clé étrangère du côté « plusieurs » pour une relation 1-N, une contrainte UNIQUE pour une relation 1-1, et une table de liaison pour une relation N-N. L’intégrité référentielle, réglée finement avec ON DELETE et ON UPDATE, garantit ensuite que ces liens restent cohérents dans le temps. Prenez le temps de dessiner ces relations avant d’écrire la moindre requête : un schéma propre vous épargne des heures de débogage.

Clé primaire et clé étrangère en SQL : le guide

La clé primaire : identifier chaque ligne

Définition

La clé auto-incrémentée

La clé primaire composite

La clé étrangère : relier les tables

Définition

Le vocabulaire des deux côtés

Les trois types de relations

Relation un-à-plusieurs (1-N)

Relation un-à-un (1-1)

Relation plusieurs-à-plusieurs (N-N)

L’intégrité référentielle

Ce que la base garantit

Piloter le comportement avec ON DELETE et ON UPDATE

Choisir la bonne stratégie

Un schéma complet en exemple

En résumé

À lire ensuite