Foreign key SQL : guide complet des clés étrangères

1. Foreign key : définition simple

Une clé étrangère (ou foreign key) est une contrainte qui établit un lien entre deux tables. Elle garantit que les valeurs d'une colonne (ou d'un groupe de colonnes) dans une table enfant correspondent à des valeurs existantes dans une table parent.

Diagramme des relations entre tables parent et enfant (assisté par Nano Banana 2)

2. Pourquoi utiliser les clés étrangères ?

Avantage n°1 : Intégrité référentielle

La foreign key empêche les enregistrements orphelins (enfants sans parent). C'est la base des bases de données relationnelles.

Avantage n°2 : Automatisation des actions

Avec ON DELETE CASCADE ou ON UPDATE CASCADE, les modifications se propagent automatiquement.

Avantage n°3 : Documentation du schéma

Les foreign keys documentent directement dans la base les relations entre tables. Tout développeur qui arrive sur le projet comprend immédiatement les liens.

Avantage n°4 : Performance des requêtes

Les SGBD créent automatiquement des index sur les colonnes de foreign key, accélérant les jointures (JOIN).

3. Syntaxe de base (générique)

Syntaxe 1 : Directement dans la colonne

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

Syntaxe 2 : Contrainte nommée (recommandée)

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

Syntaxe 3 : Clé étrangère composite (plusieurs colonnes)

CREATE TABLE lignes_commande (
    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

Infographie des options avec cas d'usage (assisté par Nano Banana 2)

5. Exemples MySQL (InnoDB)

-- Table parent
CREATE TABLE clients (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nom VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE
) ENGINE=InnoDB;

-- Table enfant avec foreign key
CREATE TABLE commandes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    client_id INT,
    montant DECIMAL(10,2),
    date_commande DATE,
    CONSTRAINT fk_commandes_client
        FOREIGN KEY (client_id)
        REFERENCES clients(id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
) ENGINE=InnoDB;

-- Vérifier les foreign keys existantes
SHOW CREATE TABLE commandes;
SELECT * FROM information_schema.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME = 'clients';

6. Exemples PostgreSQL

-- Table parent
CREATE TABLE clients (
    id SERIAL PRIMARY KEY,
    nom VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE
);

-- Table enfant (syntaxe simplifiée)
CREATE TABLE commandes (
    id SERIAL PRIMARY KEY,
    client_id INT REFERENCES clients(id) ON DELETE SET NULL,
    montant DECIMAL(10,2),
    date_commande DATE
);

-- Alternative avec contrainte nommée
CREATE TABLE commandes (
    id SERIAL PRIMARY KEY,
    client_id INT,
    montant DECIMAL(10,2),
    date_commande DATE,
    CONSTRAINT fk_commandes_client
        FOREIGN KEY (client_id)
        REFERENCES clients(id)
        ON DELETE SET NULL
);

-- Lister les foreign keys
SELECT conname, conrelid::regclass AS table_name
FROM pg_constraint
WHERE contype = 'f';

7. Exemples SQL Server

-- Table parent
CREATE TABLE clients (
    id INT IDENTITY PRIMARY KEY,
    nom NVARCHAR(100) NOT NULL,
    email NVARCHAR(100) UNIQUE
);

-- Table enfant avec foreign key
CREATE TABLE commandes (
    id INT IDENTITY PRIMARY KEY,
    client_id INT,
    montant DECIMAL(10,2),
    date_commande DATE,
    CONSTRAINT fk_commandes_client
        FOREIGN KEY (client_id)
        REFERENCES clients(id)
        ON DELETE NO ACTION  -- équivalent à RESTRICT
);

-- Désactiver temporairement une foreign key (rare !)
ALTER TABLE commandes NOCHECK CONSTRAINT fk_commandes_client;

-- Réactiver
ALTER TABLE commandes CHECK CONSTRAINT fk_commandes_client;

8. Modifier / supprimer une foreign key

Supprimer une contrainte

-- MySQL
ALTER TABLE commandes DROP FOREIGN KEY fk_commandes_client;

-- PostgreSQL / SQL Server
ALTER TABLE commandes DROP CONSTRAINT fk_commandes_client;

Ajouter une foreign key après création

-- Tous SGBD
ALTER TABLE commandes
ADD CONSTRAINT fk_commandes_client
FOREIGN KEY (client_id)
REFERENCES clients(id)
ON DELETE CASCADE;

9. Bonnes pratiques pour les foreign keys

• Nommez toujours vos contraintes – fk_table_parent_table_enfant facilite la maintenance.
• Indexez les colonnes de foreign key – La plupart des SGBD le font automatiquement, vérifiez.
• Choisissez ON DELETE avec soin – CASCADE est pratique mais dangereux, RESTRICT est plus sûr par défaut.
• Évitez les chaînes trop longues – 3-4 niveaux de CASCADE maximum.
• Documentez vos choix – Pourquoi CASCADE ici et RESTRICT ailleurs ?
• Testez en développement – Simulez des suppressions massives avant la mise en production.
• Utilisez des transactions – BEGIN; DELETE ...; ROLLBACK; pour tester sans risque.

10. Erreurs fréquentes et solutions

Erreur 1 : "Cannot add foreign key constraint" (MySQL)

Cause : Moteur MyISAM (non supporté) ou types de colonnes incompatibles.

Solution : Utilisez ENGINE=InnoDB et vérifiez que les types correspondent (ex: INT vs BIGINT).

Erreur 2 : "Foreign key constraint fails" lors d'un DELETE

Cause : ON DELETE RESTRICT et des enfants existent.

Solution : Supprimez d'abord les enfants ou modifiez la contrainte en CASCADE.

Erreur 3 : Impossibilité d'ajouter une FK à cause d'orphelins

Cause : Des lignes enfants pointent vers des parents inexistants.

Solution : Nettoyez d'abord : DELETE FROM enfants WHERE parent_id NOT IN (SELECT id FROM parents);

Erreur 4 : Performance dégradée sur les INSERT

Cause : Chaque insertion vérifie la présence du parent (coût normal).

Solution : Acceptez ce coût (c'est le prix de l'intégrité) ou désactivez temporairement les FK pour des imports massifs (dangereux).

11. FAQ — Foreign keys SQL

12. Conclusion

Les clés étrangères sont un pilier des bases de données relationnelles. Elles garantissent l'intégrité des données, documentent le schéma et peuvent automatiser certaines opérations via CASCADE.

13. Articles connexes