Foreign keys et intégrité référentielle : guide complet SQL

1. Définition : qu’est-ce qu’une clé étrangère ?

Une clé étrangère (foreign key) est une contrainte d’intégrité qui lie deux tables.

Figure 1 — Relation entre table clients (parent) et commandes (enfant) via une clé étrangère.

• La table parent (référencée) contient une clé primaire (ou unique).
• La table enfant (référençante) contient une colonre qui référence la clé primaire du parent.
• La contrainte garantit que chaque valeur de la clé étrangère existe bien dans la table parent (ou soit NULL si autorisé).

Exemple : une table commandes a une colonne client_id qui doit correspondre à un id existant dans la table clients. On ne peut pas créer une commande pour un client inexistant.

2. Pourquoi l’intégrité référentielle est cruciale

Sans clé étrangère, on peut insérer des « orphelins » : une commande dont le client a été supprimé, ou un client qui n’existe pas. La cohérence des données est alors brisée.

Problèmes courants sans foreign keys

• Données orphelines : des lignes qui référencent des lignes parent disparues.
• Incohérence des rapports : des sommes qui ne correspondent plus.
• Suppression accidentelle : on peut supprimer un client sans supprimer ses commandes (impossible avec CASCADE ou RESTRICT).

L’intégrité référentielle protège contre ces anomalies.

3. Syntaxe SQL standard

Création d’une table avec foreign key

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

CREATE TABLE commandes (
    id INT PRIMARY KEY,
    client_id INT,
    date_commande DATE,
    FOREIGN KEY (client_id) REFERENCES clients(id)
);

Ajout d’une clé étrangère sur une table existante

ALTER TABLE commandes
ADD CONSTRAINT fk_commandes_client
FOREIGN KEY (client_id) REFERENCES clients(id);

Suppression d’une clé étrangère

ALTER TABLE commandes DROP CONSTRAINT fk_commandes_client;

Contraintes sur plusieurs colonnes (clé composite)

CREATE TABLE details (
    commande_id INT,
    produit_id INT,
    quantite INT,
    PRIMARY KEY (commande_id, produit_id),
    FOREIGN KEY (commande_id) REFERENCES commandes(id),
    FOREIGN KEY (produit_id) REFERENCES produits(id)
);

4. Options ON DELETE et ON UPDATE

Ces options définissent le comportement quand une ligne parent est supprimée (DELETE) ou que sa clé primaire est mise à jour (UPDATE).

Exemple avec CASCADE :

CREATE TABLE commandes (
    id INT PRIMARY KEY,
    client_id INT,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
);

Si on supprime un client, toutes ses commandes sont automatiquement supprimées.

Figure 2 — Comportement de ON DELETE CASCADE : la suppression d’un client entraîne la suppression des commandes et des détails.

Recommandations

• CASCADE : utile pour les relations de composition (ex: commandes dépendent du client). À utiliser avec précaution.
• RESTRICT / NO ACTION : protège contre les suppressions accidentelles (par défaut le plus sûr).
• SET NULL : quand la relation est optionnelle (par exemple, un « ancien client » conserve ses commandes mais plus de lien actif).

5. Implémentations spécifiques

PostgreSQL

• Supporte toutes les options standard.
• Permet les foreign keys vers des colonnes UNIQUE, pas seulement PRIMARY KEY.
• Les contraintes peuvent être désactivées temporairement avec SET CONSTRAINTS ... DEFERRED.

-- PostgreSQL
ALTER TABLE commandes
ADD CONSTRAINT fk_client
FOREIGN KEY (client_id) REFERENCES clients(id)
ON DELETE CASCADE;

MySQL (InnoDB uniquement)

• Seul le moteur InnoDB gère les foreign keys (MyISAM ignore la syntaxe sans erreur).
• MySQL accepte ON DELETE SET DEFAULT mais uniquement avec certains moteurs.
• Les foreign keys nécessitent que les colonnes soient indexées (création automatique de l’index si absent).

-- MySQL / InnoDB
CREATE TABLE commandes (
    id INT PRIMARY KEY,
    client_id INT,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

SQLite

• Gère les foreign keys, mais désactivées par défaut (il faut activer PRAGMA foreign_keys = ON;).
• Supporte ON DELETE CASCADE, SET NULL, RESTRICT, NO ACTION.
• Ne supporte pas ON UPDATE SET DEFAULT.

-- SQLite
PRAGMA foreign_keys = ON;
CREATE TABLE commandes (
    id INTEGER PRIMARY KEY,
    client_id INTEGER,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE SET NULL
);

Comparaison rapide

6. Bonnes pratiques

Nommez explicitement vos contraintes

-- Mauvais
FOREIGN KEY (client_id) REFERENCES clients(id)

-- Bon
CONSTRAINT fk_commandes_client_id FOREIGN KEY (client_id) REFERENCES clients(id)

Un nom clair facilite la lecture et le DROP.

Indexez les colonnes foreign key

Dans PostgreSQL et SQLite, les foreign keys ne créent pas automatiquement d’index. Une requête de jointure sera plus lente sans index. Ajoutez un index manuellement :

CREATE INDEX idx_commandes_client_id ON commandes(client_id);

Choisissez l’option ON DELETE adaptée

• RESTRICT pour éviter les suppressions accidentelles (le plus courant).
• CASCADE pour les relations de dépendance forte (commande-lignes de commande).
• SET NULL pour les relations optionnelles.

Évitez les chaînes de CASCADE trop longues

Une cascade sur 5-6 niveaux peut entraîner des suppressions massives non intentionnelles.

Testez avec des données de développement

Avant d’appliquer une contrainte en production, testez son comportement sur un environnement de développement. Les violations d’intégrité peuvent faire échouer des migrations.

Articles connexes