Tous les articles

2026-08-30

· Dylan Yu
SQLiteforeign keysdatabase designschema

Clés étrangères et relations dans SQLite : un guide pratique (avec les pièges)

SQLite n'active pas les clés étrangères par défaut, et la plupart des tutoriels passent sous silence les parties qui te font vraiment trébucher. Voici comment fonctionnent les clés étrangères dans SQLite, ce que fait le PRAGMA, comment modéliser des relations sans ALTER TABLE, et comment les visualiser dans un panneau d'administration.

SQLite est le moteur de base de données le plus déployé au monde. Il est dans chaque téléphone, chaque navigateur, chaque copie de macOS, et à peu près la moitié des apps sur ton bureau en ce moment. Et pourtant, son histoire de clés étrangères est franchement bizarre — assez pour que j'aie vu des ingénieurs expérimentés y perdre un après-midi entier.

Voici la version courte : les clés étrangères existent dans SQLite, mais elles sont désactivées par défaut. Tu dois les activer, sur chaque connexion, sinon elles ne font rien en silence. Et une fois que tu les actives, tu découvres que ALTER TABLE est si limité que corriger une relation mal définie devient un calvaire en plusieurs étapes que la plupart des tutoriels ne mentionnent même pas.

La plupart des articles sur les clés étrangères SQLite couvrent le chemin heureux : créer deux tables avec une clause REFERENCES, insérer une ligne, passer à autre chose. Ça représente environ 10 % de ce que tu as vraiment besoin de savoir. Cet article couvre les autres 90 % — le comportement du PRAGMA, la configuration de l'ORM, le problème d'ALTER TABLE, et quoi faire quand tu ne peux pas modifier un schéma mais dois quand même modéliser une relation.

C'est parti.

Comment fonctionnent les clés étrangères dans SQLite (le défaut qui surprend tout le monde)

SQLite supporte les clés étrangères depuis la version 3.6.19, sortie en 2009. Ce n'est pas une typo — les clés étrangères sont disponibles depuis plus de quinze ans. Le hic, c'est qu'elles sont désactivées par défaut pour des raisons de rétrocompatibilité, et la façon de les activer n'est ni une propriété du schéma ni un réglage de la base de données. C'est un flag runtime sur la connexion.

C'est ça qui piège les gens. Tu peux écrire une clause REFERENCES parfaitement correcte, créer tes tables, insérer une ligne qui viole la contrainte, et SQLite l'acceptera sans broncher. Pas d'erreur. Pas d'avertissement. La contrainte est juste... décorative.

Voilà à quoi ça ressemble. D'abord, le schéma :

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

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    total REAL NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

Ce FOREIGN KEY (user_id) REFERENCES users(id) est du SQL correct. Ça dit : chaque user_id dans orders doit pointer vers un id réel dans users. Maintenant regarde ce qui se passe quand on insère une ligne qui viole cette contrainte :

INSERT INTO users (id, email) VALUES (1, 'alice@example.com');

-- This references user_id 999, which does not exist.
INSERT INTO orders (id, user_id, total) VALUES (1, 999, 49.99);

-- Result: the insert succeeds. No error.

Si tu arrives de PostgreSQL ou MySQL, c'est le moment où tu fixes l'écran. La contrainte est là, dans le schéma. Pourquoi n'a-t-elle pas déclenché ?

Parce que l'application des clés étrangères est désactivée. Tu l'actives avec un PRAGMA :

PRAGMA foreign_keys = ON;

-- Now try the same insert:
INSERT INTO orders (id, user_id, total) VALUES (2, 999, 49.99);

-- Result: Error: FOREIGN KEY constraint failed

C'est tout le mécanisme. Une ligne, et tes contraintes se mettent à fonctionner. La question, c'est : où est-ce que tu mets cette ligne, et comment t'assures qu'elle est toujours là ? C'est là que ça devient pénible.

Comment vérifier si les clés étrangères sont activées

Avant de faire quoi que ce soit d'autre, apprends cette requête :

PRAGMA foreign_keys;
-- Returns 0 (off) or 1 (on)

Lance-la sur n'importe quelle connexion que tu débogues. Si tu vois des lignes orphelines et que tu te demandes pourquoi tes contraintes ne les ont pas interceptées, c'est presque toujours la réponse — la connexion qui a fait l'insertion avait les clés étrangères désactivées.

Le piège du PRAGMA

Voilà ce qui rend l'approche PRAGMA vraiment dangereuse : c'est par connexion, pas par base de données. Activer PRAGMA foreign_keys = ON sur une connexion n'affecte aucune autre connexion, et ça ne persiste pas. Ouvre une nouvelle connexion — même vers le même fichier — et les clés étrangères sont à nouveau désactivées.

Ça veut dire que le réglage doit être appliqué à chaque fois que tu ouvres une connexion, par le code qui l'ouvre. Si tu oublies, tu obtiens des violations de contraintes silencieuses. Pas d'entrée dans les logs, pas d'avertissement, rien. Les données entrent, point.

En pratique, ça signifie que la configuration vit dans ton code applicatif ou dans la configuration de ton ORM, pas dans le schéma. Et chaque ORM populaire le gère différemment, ce qui est en soi une source de bugs.

Prisma

Prisma active les clés étrangères par défaut quand il se connecte à SQLite. Tu n'as rien à faire — le moteur Prisma exécute PRAGMA foreign_keys = ON; dans le cadre de sa configuration de connexion. C'est le bon défaut, et c'est l'une des rares choses du support SQLite de Prisma qui marche tout seul, dès le départ.

Si tu utilises des requêtes brutes via $executeRaw / $queryRaw, le même pool de connexions s'applique, donc les clés étrangères restent activées. Bien.

SQLAlchemy

SQLAlchemy n'active pas les clés étrangères par défaut pour SQLite. Ça piège beaucoup de gens, parce que SQLAlchemy te laisse définir des colonnes ForeignKey dans tes modèles pour ensuite ne pas les appliquer, en silence.

Tu dois l'activer explicitement avec un event listener :

from sqlalchemy import event
from sqlalchemy.engine import Engine

@event.listens_for(Engine, "connect")
def _enable_sqlite_fk(dbapi_connection, connection_record):
    cursor = dbapi_connection.cursor()
    cursor.execute("PRAGMA foreign_keys=ON")
    cursor.close()

Ce listener se déclenche à chaque nouvelle connexion, ce qui est exactement ce que tu veux. Si tu oublies ça, tes déclarations ForeignKey sont de la documentation, pas des contraintes.

Drizzle

Le driver SQLite Node de Drizzle (better-sqlite3 en dessous) laisse aussi les clés étrangères désactivées par défaut. Tu les actives à la construction du client :

import { drizzle } from 'drizzle-orm/better-sqlite3';
import Database from 'better-sqlite3';

const sqlite = new Database('app.db');
sqlite.pragma('journal_mode = WAL');
sqlite.pragma('foreign_keys = ON');  // <-- this line

export const db = drizzle(sqlite);

Note que l'appel .pragma() de better-sqlite3 s'applique à cette connexion. Si tu utilises un pool (rare avec better-sqlite3, vu qu'il est synchrone et mono-connexion), tu devrais l'appliquer par connexion.

La règle générale

Quel que soit ta stack, la règle est la même : trouve l'endroit où les connexions sont créées, et configure le PRAGMA là. Si ton ORM ne documente pas ça clairement, cherche dans l'onglet issues — il y a presque toujours un fil de utilisateurs perplexes qui ont découvert que les clés étrangères étaient désactivées après la mise en production.

Encore un pli : le PRAGMA ne peut pas être modifié à l'intérieur d'une transaction. PRAGMA foreign_keys = ON est un no-op si tu es déjà dans une transaction. Il doit être défini avant tout BEGIN. La plupart des ORMs gèrent ça correctement parce qu'ils le définissent à l'ouverture de la connexion, avant l'exécution du code utilisateur, mais si tu gères les connexions manuellement, garde-le en tête.

Ce que SQLite supporte pour les clés étrangères (et ce qui manque)

Une fois les clés étrangères réellement activées, le support de SQLite est plus complet qu'on ne le pense. Les actions référentielles courantes fonctionnent toutes. Passons-les en revue avec des exemples.

ON DELETE CASCADE

Quand la ligne parente est supprimée, toutes les lignes enfants sont supprimées automatiquement.

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

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    total REAL NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

INSERT INTO users (id, email) VALUES (1, 'alice@example.com');
INSERT INTO orders (id, user_id, total) VALUES (1, 1, 49.99);
INSERT INTO orders (id, user_id, total) VALUES (2, 1, 12.50);

DELETE FROM users WHERE id = 1;

-- Both orders rows are now gone. No manual cleanup needed.

C'est celle que tu utiliseras le plus. C'est le bon défaut pour « les enregistrements enfants n'ont pas de sens sans le parent. »

ON UPDATE CASCADE

Quand la clé primaire du parent change, la colonne de clé étrangère de l'enfant est mise à jour pour correspondre.

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

CREATE TABLE transactions (
    id INTEGER PRIMARY KEY,
    account_id INTEGER NOT NULL,
    amount REAL NOT NULL,
    FOREIGN KEY (account_id) REFERENCES accounts(id) ON UPDATE CASCADE
);

INSERT INTO accounts (id, name) VALUES (1, 'Checking');
INSERT INTO transactions (id, account_id, amount) VALUES (1, 1, 100.00);

UPDATE accounts SET id = 100 WHERE id = 1;

-- transactions.account_id is now 100, automatically.

Utile si tu renumérotes tes IDs un jour. Moins souvent nécessaire, puisque la plupart des schémas utilisent des clés substituts immuables, mais c'est là.

SET NULL

Quand le parent est supprimé, la colonne de clé étrangère de l'enfant est mise à NULL (la colonne doit être nullable).

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

CREATE TABLE tasks (
    id INTEGER PRIMARY KEY,
    project_id INTEGER,  -- nullable
    title TEXT NOT NULL,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE SET NULL
);

INSERT INTO projects (id, name) VALUES (1, 'Migration');
INSERT INTO tasks (id, project_id, title) VALUES (1, 1, 'Write schema');

DELETE FROM projects WHERE id = 1;

-- tasks row still exists, project_id is now NULL.

C'est le bon choix quand l'enregistrement enfant reste pertinent sans le parent — une tâche sans projet, un commentaire sans son post parent.

RESTRICT et NO ACTION

RESTRICT empêche la suppression du parent si des enfants existent, et il le fait immédiatement — pas de déferral, même à l'intérieur d'une transaction.

CREATE TABLE invoices (
    id INTEGER PRIMARY KEY,
    total REAL NOT NULL
);

CREATE TABLE invoice_lines (
    id INTEGER PRIMARY KEY,
    invoice_id INTEGER NOT NULL,
    FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE RESTRICT
);

NO ACTION est le défaut. Dans SQLite, NO ACTION et RESTRICT diffèrent d'une façon subtile : NO ACTION vérifie la contrainte à la fin de l'instruction (tu peux donc réordonner des suppressions dans une instruction complexe), tandis que RESTRICT vérifie immédiatement. En pratique, tu ne remarques presque jamais la différence, mais si tu veux « échec rapide, pas d'exception », utilise RESTRICT.

Ce qui n'est PAS supporté

Quelques choses à savoir :

  • Pas de clés étrangères partielles. Tu ne peux pas avoir une clé étrangère qui ne s'applique que sous certaines conditions (par ex. « appliquer ça seulement quand status = 'active' »). La contrainte est tout-ou-rien sur la colonne.
  • Pas de clés étrangères basées sur des expressions. La clé étrangère doit référencer des colonnes réelles, pas des expressions.
  • Les contraintes différées sont supportées mais rarement utilisées. Tu peux déclarer une clé étrangère comme DEFERRABLE INITIALLY DEFERRED, ce qui signifie que la vérification est reportée jusqu'au COMMIT. C'est utile pour les références circulaires (A référence B, B référence A) où tu dois insérer les deux lignes dans une seule transaction. Ça marche, mais je n'en ai presque jamais eu besoin dans des schémas réels — tu peux généralement casser le cycle avec une colonne nullable.
  • La colonne parente doit être une clé primaire ou avoir un index UNIQUE. SQLite est plus strict que certaines bases ici — tu ne peux pas référencer une colonne arbitraire. Elle doit être unique.

Pour la grande majorité des schémas, le sous-ensemble supporté est largement suffisant. Ce qui mord les gens, ce n'est pas la liste des fonctionnalités — c'est le problème d'ALTER TABLE, qui arrive maintenant.

Le problème d'ALTER TABLE

C'est là que l'histoire des clés étrangères de SQLite devient franchement douloureuse.

Le ALTER TABLE de SQLite est connu pour ses limitations. La liste complète de ce que tu peux faire :

  • ALTER TABLE ... RENAME TO ... — renommer une table
  • ALTER TABLE ... RENAME COLUMN ... TO ... — renommer une colonne
  • ALTER TABLE ... ADD COLUMN ... — ajouter une colonne
  • ALTER TABLE ... DROP COLUMN ... — supprimer une colonne (ajouté dans 3.35.0)

C'est tout. Ce qui manque notablement de cette liste :

  • Tu ne peux pas ajouter une contrainte de clé étrangère à une table existante.
  • Tu ne peux pas modifier le type ou les contraintes d'une colonne existante.
  • Tu ne peux pas ajouter une contrainte NOT NULL à une colonne existante.
  • Tu ne peux pas changer la valeur par défaut d'une colonne.

Donc si tu crées une table sans clé étrangère et réalises plus tard que tu en as besoin, il n'existe pas d'instruction ALTER TABLE orders ADD FOREIGN KEY .... Ça n'existe pas. La documentation officielle de SQLite décrit la procédure de contournement, et c'est un processus en 12 étapes que je vais te montrer en entier pour que tu comprennes pourquoi les gens l'évitent.

La reconstruction en 12 étapes

Le pattern : créer une nouvelle table avec le schéma voulu, copier les données, supprimer l'ancienne table, renommer la nouvelle, et recréer les index, triggers ou vues qui dépendaient de l'ancienne table. Voici le SQL :

-- 1. Turn FK enforcement off during the migration (required, because
--    SQLite won't let you alter a table referenced by FKs while FKs are on).
PRAGMA foreign_keys = OFF;

-- 2. Start a transaction.
BEGIN TRANSACTION;

-- 3. Create the new table with the FK constraint you wanted.
CREATE TABLE orders_new (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    total REAL NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 4. Copy the data over, filtering out any orphaned rows first.
INSERT INTO orders_new (id, user_id, total)
SELECT id, user_id, total FROM orders
WHERE user_id IN (SELECT id FROM users);

-- 5. Drop the old table.
DROP TABLE orders;

-- 6. Rename the new table to the original name.
ALTER TABLE orders_new RENAME TO orders;

-- 7. Recreate any indexes that were on the old table.
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- 8. Recreate any triggers (if you had them).
-- CREATE TRIGGER ... (omitted for brevity)

-- 9. Recreate any views that referenced the table (if you had them).
-- CREATE VIEW ... (omitted)

-- 10. Run the foreign key check to confirm everything is consistent.
PRAGMA foreign_key_check;

-- 11. Commit.
COMMIT;

-- 12. Turn FK enforcement back on.
PRAGMA foreign_keys = ON;

C'est la procédure officielle. Ça marche. C'est aussi, objectivement, beaucoup — et c'est facile de rater une étape. Oublie de recréer un index et tes requêtes ralentissent. Oublie foreign_key_check et tu mets en prod des lignes orphelines. Oublie de réactiver les clés étrangères et tu reviens au problème des violations silencieuses.

C'est la vraie raison pour laquelle les gens se frustrent avec les relations SQLite : pas parce que la fonctionnalité manque, mais parce que corriger une relation a posteriori est un processus manuel et propice aux erreurs. Dans Postgres, tu lancerais ALTER TABLE orders ADD CONSTRAINT ... FOREIGN KEY ... et ce serait réglé en une ligne. Dans SQLite, tu reconstruis la table.

Alors que faire concrètement ? Il y a trois options réalistes, avec des compromis différents.

Modéliser des relations sans ALTER TABLE

Option 1 : la reconstruction en 12 étapes

C'est ce que j'ai montré au-dessus. C'est la réponse « correcte » au sens où elle produit un schéma avec une vraie contrainte de clé étrangère appliquée. Si tu te soucies de l'intégrité référentielle appliquée au niveau base de données — et tu devrais, en général — c'est le chemin.

Quand l'utiliser :

  • Tu possèdes le schéma et peux prendre un verrou d'écriture bref.
  • La table n'est pas énorme (copier des millions de lignes prend du temps, même si SQLite est rapide).
  • Tu veux la contrainte appliquée même par du SQL brut ou d'autres outils qui touchent la base.

Quand l'éviter :

  • Tu ne peux pas te permettre de downtime, même quelques secondes.
  • La table est énorme et la copie prendrait trop de temps.
  • Tu ne possèdes pas le schéma (c'est la base d'une app tierce).

Le conseil pratique : scripte tout ça, teste-le sur une copie de la base d'abord, et lance PRAGMA foreign_key_check avant de committer. Si ça retourne des lignes, tu as des orphelins et tu dois décider quoi en faire avant que la migration soit sûre.

Option 2 : laisser ton ORM gérer ça au niveau applicatif

La plupart des ORMs te permettent de déclarer des relations dans tes modèles même si la base n'a pas de contrainte de clé étrangère. L'ORM applique la relation dans le code applicatif — quand tu fais user.orders, il lance SELECT * FROM orders WHERE user_id = ?, et quand tu crées une commande, il s'assure que user_id est défini.

Dans Prisma :

model User {
  id     Int     @id @default(autoincrement())
  email  String  @unique
  orders Order[]
}

model Order {
  id      Int  @id @default(autoincrement())
  user_id Int
  user    User @relation(fields: [user_id], references: [id])
  total   Float
}

Dans SQLAlchemy :

class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)
    orders = relationship("Order", back_populates="user")

class Order(Base):
    __tablename__ = "orders"
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer)  # no ForeignKey() needed for ORM-level relations
    user = relationship("User", back_populates="orders")

Le compromis : la relation n'existe que quand tu passes par l'ORM. Si quelqu'un lance du SQL brut, ou qu'un autre outil se connecte à la base, ou qu'un job en arrière-plan insère des lignes directement, rien n'empêche les user_id orphelins. Tu fais confiance au fait que toutes les écritures passent par ton application. Pour plein de petits projets, ça suffit. Pour tout ce qui a plusieurs writers ou des outils externes qui touchent la base, c'est une garantie qui fuit.

Option 3 : définir la relation dans la couche UI de ton outil d'admin

C'est l'approche que j'ai fini par utiliser le plus, et elle mérite une explication parce qu'elle résout un problème spécifique : tu veux travailler avec une relation (parcourir des données jointes, naviguer d'un parent vers ses enfants, construire une interface d'admin utile) sans modifier le schéma sous-jacent.

L'idée, c'est que la relation est définie dans une couche au-dessus de la base — dans l'outil que tu utilises pour regarder les données — plutôt que dans le schéma lui-même. Ton code applicatif est complètement unaffected. Le schéma reste tel quel. Mais quand tu gères les données, tu obtiens les vues jointes et la navigation que tu attendrais d'une « vraie » relation.

C'est exactement ce que fait BaseVolt. C'est une app desktop local-first (macOS et Windows) qui sert de panneau d'administration pour SQLite, PostgreSQL, MySQL et Cloudflare D1. Tu la pointes vers une base, et pour n'importe quelles deux tables tu peux définir une relation dans l'UI — choisir la table parente, la table enfant, les colonnes de liaison — et BaseVolt les traite comme liées pour le parcours, les jointures et la navigation. Pas d'ALTER TABLE. Pas de migration de schéma. Aucun changement pour ton application.

C'est particulièrement utile pour SQLite, où l'alternative est la reconstruction en 12 étapes ci-dessus. Si tu veux juste utiliser une relation sans réécrire le schéma, la définir dans la couche UI demande infiniment moins de travail et présente zéro risque.

Il y a un tier gratuit (jusqu'à 2 sources de données) et Pro est à $99/an. Il y a une démo en direct sur demo.basevolt.app si tu veux voir l'UI des relations avant d'installer. Et comme BaseVolt intègre un serveur MCP, tu peux pointer Claude ou Cursor sur ta base et lui demander de t'aider avec le schéma — y compris suggérer où des relations devraient exister.

Visualiser les relations dans un panneau d'administration

Laisse-moi te montrer à quoi ressemble la définition d'une relation dans BaseVolt, parce que le workflow est ce qui la rend vraiment utile.

Étape 1 : connecter ta base de données

Ouvre BaseVolt, clique sur « Add Data Source », et pointe-la vers ton fichier .db SQLite. Elle lit le schéma directement — pas de chaîne de connexion, pas de migration, pas de config. Pour une base avec une douzaine de tables, ça prend quelques secondes.

Étape 2 : choisir les deux tables à lier

Dans la vue des relations, tu sélectionnes une table parente (disons users) et une table enfant (disons orders). BaseVolt t'affiche les colonnes de chacune. Tu choisis les champs de liaison — users.id et orders.user_id — et tu définis la relation. C'est tout. Aucun SQL n'est exécuté sur ta base pour créer ça. La définition vit dans la config de BaseVolt, pas dans ton schéma.

Étape 3 : parcourir la vue jointe

Une fois la relation définie, plusieurs choses se font automatiquement :

  • Quand tu regardes une ligne dans users, tu vois une section liée affichant toutes les lignes orders associées. Clique pour éditer n'importe laquelle.
  • Quand tu regardes une ligne dans orders, tu vois un lien vers le user parent.
  • Tu peux construire une vue filtrée sur les deux tables sans écrire de JOIN.

C'est la partie difficile à transmettre en texte mais évidente dès que tu l'utilises : tu arrêtes de penser en termes de « lancer une requête pour voir les données liées » et tu commences à penser en termes de « cliquer sur le truc. » Pour quiconque a passé du temps dans le CLI sqlite3 à faire SELECT * FROM orders WHERE user_id = 5 encore et encore, c'est un vrai gain de qualité de vie.

Étape 4 : laisser l'IA t'aider

Comme BaseVolt expose un serveur MCP, tu peux connecter Claude ou Cursor et poser des questions en langage naturel : « Quelles commandes n'ont pas d'utilisateur correspondant ? » ou « Suggère une relation entre ces tables. » L'IA peut inspecter le schéma et les données via le serveur MCP et proposer des relations que tu aurais pu manquer. Ce n'est pas magique — c'est juste que l'introspection du schéma est déjà là, et le serveur MCP la rend disponible au modèle.

Le point clé, c'est que rien de tout ça ne touche ton schéma. Si tu décides plus tard que la relation était mauvaise, tu la supprimes dans l'UI et tu la redéfinis. Pas de migration, pas de reconstruction, pas de risque pour tes données de production.

Patterns courants

Mettons du concret sur les trois patterns de relations que tu modéliseras encore et encore, avec le SQL pour chacun et comment ils apparaissent dans un outil d'admin.

One-to-many

Le pattern le plus courant. Un utilisateur a plusieurs commandes. Un projet a plusieurs tâches. Un auteur a plusieurs posts.

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

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    total REAL NOT NULL,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

La clé étrangère vit du côté « plusieurs. » Dans BaseVolt, ça se traduit par : voir un utilisateur affiche ses commandes en dessous ; voir une commande affiche un lien vers son utilisateur. Un clic dans chaque sens.

Many-to-many (table de jonction)

Plusieurs étudiants sont inscrits à plusieurs cours. Il te faut une troisième table — une table de jonction — qui contient une ligne par association.

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

CREATE TABLE courses (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL
);

CREATE TABLE enrollments (
    student_id INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    enrolled_at TEXT NOT NULL DEFAULT (datetime('now')),
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE
);

Quelques choses à remarquer :

  • La clé primaire de la table de jonction est un composite des deux colonnes de clé étrangère. Ça empêche les inscriptions en double.
  • Les deux clés étrangères utilisent ON DELETE CASCADE, donc supprimer un étudiant ou un cours nettoie les inscriptions automatiquement.
  • La table de jonction peut contenir des données supplémentaires (ici, enrolled_at).

Dans un outil d'admin, ce sont deux relations : students ↔ enrollments et courses ↔ enrollments. Pour voir les cours d'un étudiant, tu navigues étudiant → inscriptions → cours. BaseVolt gère ça en deux sauts, ce qui est la façon honnête de le modéliser — il n'existe pas de many-to-many « direct » dans une base relationnelle, seulement des tables de jonction.

Self-referential

Une catégorie a une catégorie parente. Un employé a un manager qui est aussi un employé. Un commentaire a un commentaire parent.

CREATE TABLE categories (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    parent_id INTEGER,
    FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL
);

La clé étrangère pointe de la table vers elle-même. parent_id est nullable pour qu'une catégorie racine puisse n'avoir aucun parent. ON DELETE SET NULL signifie que supprimer une catégorie ne supprime pas ses enfants — ça les rend juste orphelins au plus haut niveau. Si tu voulais que la suppression cascade dans l'arbre, tu utiliserais ON DELETE CASCADE à la place, mais attention : ça supprime tout un sous-arbre en une seule instruction.

Les relations self-referential sont le cas où les définir dans une couche UI est particulièrement agréable, parce que le « parent » et l'« enfant » sont la même table. Dans BaseVolt, tu définirais une relation de categories vers categories sur id ↔ parent_id, et ensuite voir une catégorie affiche à la fois son parent et ses enfants inline.

Déboguer les problèmes de clés étrangères

Même quand les clés étrangères sont activées, des choses tournent mal. Tu hérites d'une base avec des lignes orphelines. Une migration n'a pas tout à fait marché. Un job en arrière-plan a inséré de mauvaises données pendant que les clés étrangères étaient désactivées. SQLite te donne deux PRAGMAs pour comprendre ce qui s'est passé.

PRAGMA foreign_key_check;

Ça scanne toute la base à la recherche de lignes qui violent une contrainte de clé étrangère et les retourne. Lance-le dès que tu as un doute :

PRAGMA foreign_key_check;
-- Returns rows like:
-- orders|42|users|1
-- meaning: table 'orders', rowid 42, violates FK into 'users', constraint #1

Chaque ligne te donne la table enfant, le rowid de la ligne fautive, la table parente, et quelle contrainte de clé étrangère (par index) a été violée. De là, tu peux faire SELECT * FROM orders WHERE rowid = 42 pour voir la ligne réelle et décider quoi en faire — corriger le user_id, supprimer la ligne, ou insérer le parent manquant.

C'est aussi ce que tu devrais lancer à la fin d'une reconstruction en 12 étapes, avant de committer. Si ça ne retourne rien, ta migration est cohérente.

PRAGMA foreign_key_list(table);

Ça t'affiche les contraintes de clé étrangère définies sur une table spécifique :

PRAGMA foreign_key_list(orders);
-- Returns one row per FK, showing:
-- id | seq | table  | from     | to | on_update | on_delete | match
-- 0  | 0   | users  | user_id  | id | NO ACTION | CASCADE   | NONE

Utile quand tu as oublié quelles contraintes une table possède — surtout sur une base que tu n'as pas conçue. Les colonnes from et to te disent quelle colonne locale pointe vers quelle colonne parente, et on_delete / on_update te donnent les actions.

Trouver les lignes orphelines manuellement

Si tu veux trouver les lignes orphelines pour une relation spécifique sans scanner toute la base :

SELECT o.*
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE u.id IS NULL;

C'est la requête classique « trouver les enfants sans parent. » Elle est plus rapide que foreign_key_check si tu ne te soucies que d'une seule relation, et elle te donne les lignes enfants complètes au lieu de juste des rowids.

Un workflow de débogage

Quand quelque chose cloche avec les relations, voici l'ordre dans lequel je procèderais :

  1. Lance PRAGMA foreign_keys; sur la connexion qui a fait l'écriture. Si c'est 0, tu as ta réponse.
  2. Lance PRAGMA foreign_key_check; pour voir les dégâts.
  3. Pour chaque violation, décide : corriger l'enfant, supprimer l'enfant, ou insérer le parent manquant.
  4. Lance PRAGMA foreign_key_list(child_table); pour confirmer que la contrainte est bien ce que tu penses.
  5. Corrige la configuration de connexion pour que les clés étrangères soient activées partout, à l'avenir.

La cause racine la plus courante, de loin, c'est l'étape 1. J'estimerais que 80 % des problèmes « mes clés étrangères SQLite ne marchent pas » sont juste une connexion avec le PRAGMA désactivé.

En résumé

Le support des clés étrangères dans SQLite est correct. Ce n'est pas le manque de fonctionnalités qui pose problème — ce sont les défauts et l'ergonomie. Plus précisément :

  • Les clés étrangères sont désactivées par défaut. Configure PRAGMA foreign_keys = ON sur chaque connexion, dans le code qui crée la connexion. Vérifie avec PRAGMA foreign_keys; au débogage.
  • Le PRAGMA est par connexion. La doc de ton ORM te dira comment il gère ça. Si elle ne le dit pas, suppose qu'il ne le fait pas, et ajoute un event hook.
  • ALTER TABLE ne peut pas ajouter de clés étrangères. Corriger une relation manquante implique une reconstruction de table en 12 étapes, ou définir la relation au niveau applicatif/UI à la place.
  • Les actions référentielles supportées sont suffisamment complètes pour un usage réel. CASCADE, SET NULL, RESTRICT, NO ACTION fonctionnent toutes. Les contraintes différées existent si tu en as besoin.
  • Le débogage tient en deux PRAGMAs. foreign_key_check et foreign_key_list te diront tout ce que tu dois savoir.

Et si tu veux juste travailler avec des relations dans SQLite sans réécrire des schémas — parcourir des données jointes, naviguer entre lignes parentes et enfants, construire une interface d'admin — définis-les dans la couche UI plutôt que de te battre avec ALTER TABLE. C'est exactement à ça que sert BaseVolt : pointe-la vers ta base, définis les relations visuellement, et obtiens un panneau d'administration utilisable sans toucher à ton schéma ni à ton code applicatif.

Essayez-le sur basevolt.app — pas d'inscription, pas de carte bancaire. Il y a une démo en direct sur demo.basevolt.app si tu veux fouiller d'abord.

Si tu as trouvé ça utile, j'écris sur les bases de données, le logiciel local-first, et les parties peu glamour de la création d'outils pour développeurs. Retrouvez-moi sur X.

BasevoltBasevolt

Essayez Basevolt — un panneau d'administration de base de données local et gratuit pour PostgreSQL, MySQL, SQLite et Cloudflare D1.

Télécharger Basevolt gratuitement