Retour à tous les guides
SQL··5 min de lecture

Clés primaires et étrangères : les fondements des relations entre tables

Comprenez les clés primaires par rapport aux clés étrangères en SQL. Apprenez comment elles garantissent l'intégrité des données, créent des relations et fonctionnent avec les actions en cascade.

Clé primaire client 42 référencée par des clés étrangères de commande, avec une contrainte SQL concrète
Dans ce guide
  1. Comprendre les clés primaires
  2. Comprendre les clés étrangères
  3. Fonctionnement conjoint des clés primaires et étrangères
  4. Clés composites
  5. Actions en cascade
  6. Bonnes pratiques
  7. Références et lecture complémentaire

Les exemples SQL utilisent PostgreSQL à moins qu'un autre dialecte ne soit nommé. Les extraits courts supposent que leurs tables référencées existent déjà. Validez le DDL dans votre base de données cible avant de l'appliquer.

Comprendre les clés primaires

A clé primaire est une colonne — ou un ensemble de colonnes — qui identifie de manière unique chaque ligne d'une table de base de données. Aucune ligne ne peut partager la même valeur de clé primaire, et la valeur ne peut jamais être NULL. Une table peut déclarer au plus une clé primaire, et en choisir une est une bonne pratique pour les tables d'application.

Clés naturelles ou clés de substitution

A clé naturelle utilise des données qui existent déjà dans le domaine du monde réel. Par exemple, le code ISO d'un pays (US, FR) ou l'ISBN d'un livre. Les clés naturelles ont du sens, mais elles peuvent changer au fil du temps et poser des problèmes de confidentialité (pensez aux numéros de sécurité sociale).

A clé de substitution est un identifiant artificiel sans signification commerciale — généralement un entier à incrémentation automatique ou un UUID. Les clés de substitution sont stables, compactes et faciles à indexer, c'est pourquoi la plupart des schémas modernes les préfèrent.

-- Natural key example
CREATE TABLE countries (
    iso_code  CHAR(2)      PRIMARY KEY,
    name      VARCHAR(100) NOT NULL
);

-- Surrogate key example
CREATE TABLE users (
    id         SERIAL       PRIMARY KEY,
    email      VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMPTZ  DEFAULT now()
);

Comprendre les clés étrangères

A clé étrangère est une colonne (ou un groupe de colonnes) dans une table qui fait référence à une clé primaire ou à une autre clé unique éligible, y compris une clé dans la même table. Elle applique intégrité référentielle: une valeur de clé étrangère non nulle doit posséder une ligne référencée correspondante. Les clés étrangères acceptant les valeurs NULL et composites nécessitent une acceptation explicite des valeurs NULL et des règles de correspondance.

CREATE TABLE posts (
    id        SERIAL       PRIMARY KEY,
    title     VARCHAR(200) NOT NULL,
    author_id INT          NOT NULL,
    CONSTRAINT fk_posts_author
        FOREIGN KEY (author_id) REFERENCES users (id)
);

Avec cette contrainte en place, la base de données rejettera tout INSERT en posts où author_id ne correspond pas à un users.id.

Contrainte de clé étrangère affichée sous forme de ligne de relation dans un ERD
Les clés primaires identifient les lignes ; les clés étrangères transforment ces identifiants en relations visibles dans le diagramme.

Fonctionnement conjoint des clés primaires et étrangères

Les clés primaires et étrangères sont l'épine dorsale des bases de données relationnelles. La clé primaire d'une table devient l'ancre ; une clé étrangère dans une table associée pointe vers cette ancre. Ce lien applique une relation déclarée. Vous pouvez également JOIN tables et modéliser des relations du monde réel telles que "un utilisateur a plusieurs articles" ou "une commande appartient à un client."

-- Retrieve every post alongside the author's email
SELECT p.title, u.email
FROM   posts p
JOIN   users u ON u.id = p.author_id;

Pour esquisser des entités avant d'écrire du SQL, ouvrez le Éditeur ERD. Pour inspecter les définitions de table et de clé étrangère ci-dessus, collez-les dans le Éditeur SQL vers ERD.

Clés composites

Parfois, une seule colonne ne suffit pas pour garantir l'unicité. Un clé composite (également appelé clé composée) combine deux colonnes ou plus en une seule clé primaire. Ce modèle est courant dans tables de jonction (aussi connues sous le nom de tables de jonction ou tables associatives) utilisées pour les relations plusieurs-à-plusieurs.

-- Junction table for a many-to-many relationship
CREATE TABLE student_courses (
    student_id INT NOT NULL,
    course_id  INT NOT NULL,
    enrolled_at TIMESTAMPTZ DEFAULT now(),
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students (id),
    FOREIGN KEY (course_id)  REFERENCES courses (id)
);

La clé primaire composite (student_id, course_id) garantit qu'un même étudiant ne peut pas s'inscrire deux fois au même cours tout en permettant à chaque étudiant de suivre de nombreux cours et à chaque cours d'avoir de nombreux étudiants.

Actions en cascade

Lorsque vous définissez une clé étrangère, vous pouvez également spécifier ce qui se passe lorsque la ligne référencée est mise à jour ou supprimée. Celles-ci actions référentielles gardez vos données cohérentes sans nécessiter de nettoyage manuel.

  • CASCADE — supprimer ou mettre à jour automatiquement les lignes enfants lorsque la ligne parente change.
  • SET NULL — définir la colonne de clé étrangère sur NULL (la colonne doit accepter les valeurs NULL).
  • SET DEFAULT — réinitialiser la colonne à sa valeur par défaut, qui doit toujours satisfaire la clé étrangère. La prise en charge varie selon le moteur ; MySQL InnoDB rejette cette action.
  • RESTRICT / NO ACTION — rejeter une modification qui laisse des références invalides. Dans PostgreSQL, NO ACTION peut être différé lorsque la contrainte est différable ; RESTRICT est immédiat.
CREATE TABLE comments (
    id        SERIAL       PRIMARY KEY,
    body      TEXT         NOT NULL,
    post_id   INT          NOT NULL,
    CONSTRAINT fk_comments_post
        FOREIGN KEY (post_id)
        REFERENCES posts (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

-- Deleting a post now automatically removes all its comments.

Utiliser CASCADE avec précaution — la suppression d'une ligne parente effacera silencieusement chaque ligne enfant associée. Dans les systèmes soumis à de nombreux audits, envisagez RESTRICT combiné avec un indicateur de suppression logique à la place.

Bonnes pratiques

  • Préférer clés de substitution (SERIAL, BIGSERIAL, ou UUID) comme clés primaires pour les tables d'application. Utilisez une clé naturelle lorsque l'identifiant du domaine est suffisamment stable pour vos besoins, et planifiez l'impact des modifications sur les références.
  • Toujours nommez vos contraintes (par exemple, fk_posts_author) afin que les messages d'erreur soient explicites.
  • Indexer les colonnes de clés étrangères. De nombreuses bases de données ne créent pas automatiquement d'index sur une clé étrangère, et l'absence d'index peut considérablement ralentir les jointures et les suppressions en cascade.
  • Pensez au action en cascade pour chaque clé étrangère. Par défaut à NO ACTION est le plus sûr ; passez à CASCADE uniquement lorsque les lignes enfants perdent réellement leur sens sans leur parent.
  • Choisissez des clés composites à partir des règles d'identité du modèle. Une clé de substitution peut simplifier les références, mais conservez une contrainte UNIQUE sur la clé métier d'origine lorsque les doublons doivent être évités.
  • Modélisez votre schéma visuellement en premier. Le Éditeur ERD vous permet d'esquisser un modèle conceptuel et de revoir sa conversion SQL — détectant les erreurs de conception avant qu'elles n'atteignent la production.

Références et lecture complémentaire

Plus d'articles