Docs SQLite exécutables : Clés étrangères

Q: Comment créer une clé étrangère en SQLite ?

Il suffit d'ajouter une clause REFERENCES autretable(colonne) à la définition de la colonne dans le CREATE TABLE. Par exemple, authorid INTEGER REFERENCES authors(id) fait pointer author_id vers une ligne de la table authors. Attention : la colonne référencée doit être déclarée PRIMARY KEY ou avoir une contrainte UNIQUE.

Q: À quoi sert ON DELETE CASCADE en SQLite ?

ON DELETE CASCADE indique à SQLite de supprimer automatiquement les lignes enfants lorsque leur parent est supprimé. Les autres options sont RESTRICT (bloque la suppression), SET NULL (met la colonne FK à NULL), SET DEFAULT et NO ACTION (la valeur par défaut, qui revient en pratique à RESTRICT). Le choix dépend de la question suivante : est-ce que les enfants ont encore un sens sans leur parent ?

Une clé étrangère, c'est un pointeur entre deux tables

Une clé étrangère SQLite, c'est tout simplement une colonne d'une table dont la valeur doit correspondre à une ligne existante dans une autre table. C'est la façon qu'ont les bases de données relationnelles de dire « cette ligne de posts appartient à cette ligne de authors », sans avoir à recopier le nom et l'e-mail de l'auteur dans chaque article.

Voici l'exemple le plus simple possible : une table parent et une table enfant reliées par une clé étrangère.

PRAGMA foreign_keys = ON;

CREATE TABLE authors (
    id   INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT NOT NULL,
    author_id INTEGER REFERENCES authors(id)
);

INSERT INTO authors (name) VALUES ('Ada'), ('Boris');
INSERT INTO posts (title, author_id) VALUES ('Bonjour', 1), ('Notes', 2);

SELECT p.title, a.name
FROM posts p JOIN authors a ON a.id = p.author_id;

author_id INTEGER REFERENCES authors(id) : voilà la déclaration complète de la clé étrangère SQLite. Elle indique que cette colonne contient un id provenant de la table authors. Désormais, la base sait que les deux tables sont liées et — si la vérification est activée — elle refusera toute insertion pointant vers un auteur qui n'existe pas.

Les clés étrangères sont désactivées par défaut

C'est le point le plus important à retenir sur les clés étrangères dans SQLite, et il prend tout le monde de court : SQLite reconnaît bien la clause REFERENCES, mais ne l'applique pas tant que vous ne le demandez pas explicitement. La raison est historique : il fallait préserver la compatibilité avec les anciennes bases créées avant l'arrivée de cette fonctionnalité.

Regardez ce qui se passe sans la vérification active :

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT,
    author_id INTEGER REFERENCES authors(id)
);

-- l'auteur 999 n'existe pas, mais SQLite accepte volontiers ceci :
INSERT INTO posts (title, author_id) VALUES ('Orphelin', 999);

SELECT * FROM posts;

La ligne orpheline est passée comme une lettre à la poste. Pour obtenir la protection que vous attendez vraiment, exécutez PRAGMA foreign_keys = ON; au début de chaque connexion :

PRAGMA foreign_keys = ON;

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT,
    author_id INTEGER REFERENCES authors(id)
);

INSERT INTO posts (title, author_id) VALUES ('Orphelin', 999);

Maintenant, l'insertion échoue avec FOREIGN KEY constraint failed. Le pragma s'applique par connexion, pas par base de données — le réglage n'est pas conservé dans le fichier. Chaque application, chaque session CLI, chaque fixture de test doit le définir. La plupart des projets en production exécutent PRAGMA foreign_keys = ON; juste après l'ouverture de la connexion.

Ce que la clause REFERENCES exige réellement

La colonne référencée doit être une PRIMARY KEY ou porter une contrainte UNIQUE. C'est la seule façon pour SQLite de garantir qu'une recherche soit non ambiguë. Les types doivent aussi être compatibles — SQLite reste permissif sur les types, mais les mélanger, c'est s'exposer à de mauvaises surprises.

On peut écrire la clé étrangère SQLite de deux manières. En ligne, directement sur la colonne :

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);

CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    author_id INTEGER REFERENCES authors(id),
    title     TEXT
);

SELECT sql FROM sqlite_master WHERE name = 'posts';

Ou en tant que contrainte définie au niveau de la table, ce qui devient indispensable lorsque la clé étrangère porte sur plusieurs colonnes :

CREATE TABLE posts (id INTEGER PRIMARY KEY, title TEXT);
CREATE TABLE tags  (id INTEGER PRIMARY KEY, name TEXT);

CREATE TABLE post_tags (
    post_id INTEGER NOT NULL,
    tag_id  INTEGER NOT NULL,
    PRIMARY KEY (post_id, tag_id),
    FOREIGN KEY (post_id) REFERENCES posts(id),
    FOREIGN KEY (tag_id)  REFERENCES tags(id)
);

SELECT sql FROM sqlite_master WHERE name = 'post_tags';

Les deux formes produisent exactement la même contrainte. Choisissez celle qui se lit le mieux selon le contexte de la table.

ON DELETE : que deviennent les lignes enfants ?

Quand vous supprimez une ligne parente, SQLite doit bien décider quoi faire des lignes enfants qui la référencent. C'est ON DELETE qui détermine ce comportement :

PRAGMA foreign_keys = ON;

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);

CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT,
    author_id INTEGER REFERENCES authors(id) ON DELETE CASCADE
);

INSERT INTO authors (id, name) VALUES (1, 'Ada');
INSERT INTO posts (title, author_id) VALUES ('A'), ('B') ON CONFLICT DO NOTHING;
INSERT INTO posts (title, author_id) VALUES ('A', 1), ('B', 1);

DELETE FROM authors WHERE id = 1;
SELECT * FROM posts;

La suppression d'Ada a aussi entraîné celle de ses deux articles. Les options possibles :

CASCADE — supprimez aussi les enregistrements enfants. Pratique pour des données « possédées », comme les articles d'un auteur ou les lignes d'une commande.
SET NULL — met la colonne FK à NULL. Utile quand les enfants doivent survivre sans parent (par exemple, les commentaires d'un utilisateur supprimé deviennent anonymes).
SET DEFAULT — affecte à la colonne FK la valeur par défaut déclarée.
RESTRICT — bloque la suppression dès qu'un enfant existe. L'échec est immédiat, au moment de l'instruction.
NO ACTION — le comportement par défaut. En pratique très proche de RESTRICT (la vérification est repoussée au COMMIT, mais le résultat est le même : impossible de laisser des enfants orphelins).

ON UPDATE fonctionne de la même façon lorsqu'on modifie la clé du parent, même si en pratique on met rarement à jour une clé primaire.

Foreign key constraint failed SQLite : que signifie cette erreur ?

Cette erreur apparaît dans deux cas de figure. D'abord, lors de l'insertion ou la mise à jour d'un enfant avec une valeur qui ne correspond à aucun parent :

sqlite> INSERT INTO posts (title, author_id) VALUES ('Stray', 999);
Runtime error: FOREIGN KEY constraint failed

Soit l'auteur 999 n'existe pas, soit vous avez inversé les types de colonnes. Insère d'abord le parent, ou corrige la valeur.

Deuxième cas classique : vous supprimez (ou vous modifiez) un parent qui a encore des enfants, alors que la clé étrangère est définie avec RESTRICT ou NO ACTION :

sqlite> DELETE FROM authors WHERE id = 1;
Runtime error: FOREIGN KEY constraint failed

Soit vous supprimez d'abord les enregistrements enfants, soit vous modifiez la clé étrangère en ON DELETE CASCADE ou SET NULL si la propagation correspond vraiment à ce que vous voulez.

Il existe aussi un cousin plus rare : FOREIGN KEY mismatch. Celui-ci se déclenche quand la colonne référencée n'est ni clé primaire ni unique, ou quand le nombre de colonnes ne correspond pas. C'est une erreur de schéma, pas de données.

Ajouter une clé étrangère SQLite à une table existante

L'ALTER TABLE de SQLite reste limité : vous pouvez ajouter une nouvelle colonne avec une clé étrangère, mais impossible de greffer une clé étrangère sur une colonne qui existe déjà. La parade classique, c'est la petite chorégraphie « renommer et reconstruire » :

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT NOT NULL,
    author_id INTEGER
);

INSERT INTO authors (id, name) VALUES (1, 'Ada');
INSERT INTO posts (title, author_id) VALUES ('Bonjour', 1), ('Monde', 1);

PRAGMA foreign_keys = OFF;
BEGIN;

CREATE TABLE posts_new (
    id        INTEGER PRIMARY KEY,
    title     TEXT NOT NULL,
    author_id INTEGER REFERENCES authors(id) ON DELETE CASCADE
);

INSERT INTO posts_new SELECT id, title, author_id FROM posts;

DROP TABLE posts;
ALTER TABLE posts_new RENAME TO posts;

COMMIT;
PRAGMA foreign_keys = ON;

SELECT sql FROM sqlite_master WHERE name = 'posts';

Le principe : on désactive le contrôle des clés, on recrée la table avec les contraintes voulues, on copie les données, on supprime l'ancienne table, puis on la renomme. Le BEGIN/COMMIT garantit l'atomicité. À la fin, on réactive le contrôle et SQLite revérifie toutes les lignes existantes par rapport aux nouvelles contraintes — si jamais des données ne sont pas conformes, la transaction est déjà validée, donc autant vérifier en amont si ça vous inquiète.

Lancez PRAGMA foreign_key_check; après la migration pour vous assurer qu'aucune ligne orpheline ne traîne.

Un schéma plus réaliste

Mettons tout ça en pratique avec un mini-schéma de blog : des parents, des enfants, et une table de liaison pour gérer la relation plusieurs-à-plusieurs avec les tags :

PRAGMA foreign_keys = ON;

CREATE TABLE authors (
    id    INTEGER PRIMARY KEY,
    email TEXT UNIQUE NOT NULL
);

CREATE TABLE posts (
    id         INTEGER PRIMARY KEY,
    author_id  INTEGER NOT NULL REFERENCES authors(id) ON DELETE CASCADE,
    title      TEXT NOT NULL,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE tags (
    id   INTEGER PRIMARY KEY,
    name TEXT UNIQUE NOT NULL
);

CREATE TABLE post_tags (
    post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    tag_id  INTEGER NOT NULL REFERENCES tags(id)  ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

Trois points à retenir. author_id est NOT NULL — chaque post doit avoir un auteur. La clé étrangère posts → authors est en cascade : supprimer un auteur efface aussi tous ses posts. Quant à la table de liaison post_tags, elle cascade des deux côtés, donc supprimer un post ou un tag nettoie automatiquement les lignes de liaison.

Bonnes habitudes pour s'épargner des galères

Activez PRAGMA foreign_keys = ON; sur chaque connexion. Intègre-le à la routine d'ouverture de votre base, plutôt que de compter sur votre mémoire.
Ajoutez un index sur la colonne de clé étrangère. SQLite indexe automatiquement la clé du parent, mais pas celle de l'enfant — or ON DELETE CASCADE fait une recherche côté enfant à chaque suppression côté parent.
Choisissez votre ON DELETE en conscience. La valeur par défaut (NO ACTION) est sûre, mais vous allez te heurter à des "constraint failed" dès que vous voudrez faire le ménage. Décide du comportement attendu et déclare-le explicitement.
Lancez PRAGMA foreign_key_check; après les migrations ou les imports en masse pour repérer les orphelins avant qu'ils ne deviennent des bugs.

La suite : INNER JOIN

Les clés étrangères décrivent la relation ; les jointures, elles, te permettent de faire des requêtes à travers cette relation. La prochaine page traite de INNER JOIN — comment combiner des lignes de tables liées et récupérer exactement les colonnes voulues de chaque côté.

Questions fréquentes

Comment créer une clé étrangère en SQLite ?

Il suffit d'ajouter une clause REFERENCES autre_table(colonne) à la définition de la colonne dans le CREATE TABLE. Par exemple, author_id INTEGER REFERENCES authors(id) fait pointer author_id vers une ligne de la table authors. Attention : la colonne référencée doit être déclarée PRIMARY KEY ou avoir une contrainte UNIQUE.

Pourquoi mes clés étrangères ne sont-elles pas appliquées dans SQLite ?

SQLite analyse bien les déclarations de clés étrangères, mais il ne les applique pas tant que vous n'activez pas explicitement leur vérification. Il faut exécuter PRAGMA foreign_keys = ON; au début de chaque connexion. Ce réglage est défini par connexion et n'est pas stocké dans la base, donc vos bibliothèques et la CLI doivent le repositionner à chaque ouverture.

À quoi sert ON DELETE CASCADE en SQLite ?

ON DELETE CASCADE indique à SQLite de supprimer automatiquement les lignes enfants lorsque leur parent est supprimé. Les autres options sont RESTRICT (bloque la suppression), SET NULL (met la colonne FK à NULL), SET DEFAULT et NO ACTION (la valeur par défaut, qui revient en pratique à RESTRICT). Le choix dépend de la question suivante : est-ce que les enfants ont encore un sens sans leur parent ?

Comment corriger l'erreur 'foreign key constraint failed' en SQLite ?

Cette erreur signifie que vous tentez d'insérer ou de mettre à jour une ligne dont la valeur de clé étrangère ne correspond à aucune ligne de la table référencée, ou que vous essayez de supprimer un parent qui possède encore des enfants. Vérifiez d'abord que la ligne référencée existe, ou mettez en place ON DELETE CASCADE si vous voulez que les enfants soient supprimés automatiquement.

Concepts liés

Clés étrangères SQLite : REFERENCES, ON DELETE, PRAGMA