Docs SQLite exécutables : Clés primaires

Q: Comment définir une clé primaire en SQLite ?

Il suffit d'ajouter PRIMARY KEY à une colonne dans votre CREATE TABLE, par exemple id INTEGER PRIMARY KEY. Pour une clé qui couvre plusieurs colonnes, on passe par une contrainte au niveau de la table : PRIMARY KEY (cola, colb). La colonne (ou la combinaison) doit être unique sur l'ensemble des lignes.

Q: Quelle différence entre INTEGER PRIMARY KEY et les autres clés primaires en SQLite ?

INTEGER PRIMARY KEY est un cas à part : la colonne devient un alias du rowid interne de la table, donc elle est stockée directement dans le B-tree, sans index supplémentaire. Tout autre type, ou une clé composite, génère un index unique séparé. Pour un identifiant numérique sur une seule colonne, INTEGER PRIMARY KEY est plus rapide et plus compact.

Q: Faut-il vraiment ajouter AUTOINCREMENT sur une clé primaire SQLite ?

En général, non. Une INTEGER PRIMARY KEY attribue déjà automatiquement un rowid unique quand vous insérez NULL. AUTOINCREMENT ajoute uniquement la garantie qu'un id ne sera jamais réutilisé après suppression — au prix d'une table sqlite_sequence supplémentaire. À éviter sauf si vous avez réellement besoin de ce comportement strictement monotone.

À quoi sert vraiment une clé primaire

Une clé primaire SQLite, c'est la colonne (ou la combinaison de colonnes) qui identifie de manière unique chaque ligne d'une table. Impossible d'avoir deux lignes avec la même valeur de clé primaire : SQLite veille au grain, et il s'en sert aussi pour retrouver les lignes très rapidement.

La forme la plus simple se déclare directement sur une colonne :

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

INSERT INTO users (email, name) VALUES ('ada@example.com', 'Ada');
INSERT INTO users (email, name) VALUES ('boris@example.com', 'Boris');

SELECT * FROM users;

Vous n'avez pas fourni d'id et SQLite l'a rempli pour vous. Ce n'est pas de la magie : c'est un cas particulier réservé à INTEGER PRIMARY KEY, et il vaut mieux le comprendre avant d'écrire la moindre ligne de plus.

INTEGER PRIMARY KEY : un cas à part

Dans la plupart des bases de données, une clé primaire n'est rien de plus qu'un index unique. Avec SQLite, c'est différent : chaque table classique possède déjà un entier 64 bits caché, le rowid, qui sert à identifier les lignes en interne. Quand vous déclarez une colonne avec exactement le type INTEGER PRIMARY KEY, cette colonne devient le rowid. Pas d'index supplémentaire, pas d'espace en plus : votre id et l'emplacement physique de la ligne ne font qu'un.

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

INSERT INTO posts (title) VALUES ('Premier'), ('Deuxième'), ('Troisième');

SELECT id, rowid, title FROM posts;

id et rowid désignent en fait la même colonne sous deux noms différents. Une recherche par id tombe directement sur la ligne — pas de second arbre à parcourir. C'est pour ça que la règle de base en SQLite est la suivante : si vous voulez une clé primaire numérique, écrivez INTEGER PRIMARY KEY à la lettre. Pas INT, pas BIGINT, et pas non plus INTEGER NOT NULL PRIMARY KEY (bon, celle-ci passe, mais le type doit absolument être INTEGER).

Les autres types fonctionnent aussi — ils récupèrent simplement un index unique séparé, ce qui n'a rien de gênant, c'est juste un peu moins compact.

AUTOINCREMENT : presque toujours inutile

Quand on vient d'autres bases de données, on a tendance à écrire par réflexe id INTEGER PRIMARY KEY AUTOINCREMENT. Sauf qu'en SQLite, le mot-clé AUTOINCREMENT fait quelque chose de bien plus restreint que ce que son nom laisse penser, et la plupart du temps vous n'en avez pas besoin.

Sans AUTOINCREMENT, une colonne INTEGER PRIMARY KEY se remplit toute seule avec le rowid le plus élevé existant + 1. Si vous supprimez la dernière ligne, l'insertion suivante peut réutiliser cet id.

Avec AUTOINCREMENT, SQLite garde en mémoire le plus grand id jamais utilisé dans une table annexe appelée sqlite_sequence, et ne réutilise jamais une valeur, même après suppression.

CREATE TABLE plain (id INTEGER PRIMARY KEY, label TEXT);
CREATE TABLE strict_id (id INTEGER PRIMARY KEY AUTOINCREMENT, label TEXT);

INSERT INTO plain (label) VALUES ('a'), ('b'), ('c');
INSERT INTO strict_id (label) VALUES ('a'), ('b'), ('c');

DELETE FROM plain WHERE id = 3;
DELETE FROM strict_id WHERE id = 3;

INSERT INTO plain (label) VALUES ('nouveau');
INSERT INTO strict_id (label) VALUES ('nouveau');

SELECT 'plain' AS table_name, id, label FROM plain
UNION ALL
SELECT 'strict_id', id, label FROM strict_id;

La table plain a réutilisé l'id 3. Celle avec AUTOINCREMENT, elle, est passée directement à 4. À moins d'avoir une vraie raison d'interdire la réutilisation des id — audit, références externes qui traînent après une suppression — laisse tomber AUTOINCREMENT. Ça coûte une écriture supplémentaire à chaque insertion, plus une table de suivi dédiée.

Clé primaire composite SQLite

Parfois, une seule colonne ne suffit pas. Prenez une table de liaison entre utilisateurs et rôles : ce qui l'identifie de façon unique, c'est la paire (user_id, role_id). Dans ce cas, on déclare la clé primaire au niveau de la table, sur plusieurs colonnes :

CREATE TABLE user_roles (
    user_id  INTEGER NOT NULL,
    role_id  INTEGER NOT NULL,
    granted_at TEXT DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, role_id)
);

INSERT INTO user_roles (user_id, role_id) VALUES (1, 10), (1, 20), (2, 10);
-- Celle-ci viole la clé composite :
INSERT INTO user_roles (user_id, role_id) VALUES (1, 10);

La paire doit être unique sur l'ensemble de la table — (1, 10) ne peut apparaître qu'une seule fois. Chaque colonne prise isolément, en revanche, peut se répéter sans problème. C'est précisément l'objectif : un utilisateur peut avoir plusieurs rôles, un rôle peut être attribué à plusieurs utilisateurs, mais un couple utilisateur-rôle donné n'existe qu'une fois.

Une clé primaire composite SQLite crée un index dédié couvrant les colonnes listées. Elle ne devient pas le rowid — seul un INTEGER PRIMARY KEY bénéficie de ce traitement particulier.

Le piège du NULL dans la clé primaire

Voici une bizarrerie qui déconcerte ceux qui viennent de PostgreSQL ou MySQL : dans une table SQLite classique, une colonne de clé primaire autre qu'INTEGER PRIMARY KEY peut contenir des NULL. Il s'agit d'un bug ancien que les auteurs de SQLite ont conservé pour des raisons de rétrocompatibilité.

CREATE TABLE products (
    sku   TEXT PRIMARY KEY,
    name  TEXT
);

INSERT INTO products (sku, name) VALUES ('A1', 'Pomme');
INSERT INTO products (sku, name) VALUES (NULL, 'Mystère 1');
INSERT INTO products (sku, name) VALUES (NULL, 'Mystère 2');

SELECT * FROM products;

Deux lignes avec NULL sont passées à travers les mailles de la clé primaire. La parade : ajouter explicitement NOT NULL sur chaque colonne de clé primaire qui n'est pas un entier :

CREATE TABLE products (
    sku   TEXT NOT NULL PRIMARY KEY,
    name  TEXT
);

INSERT INTO products (sku, name) VALUES ('A1', 'Pomme');
INSERT INTO products (sku, name) VALUES (NULL, 'Mystère');  -- désormais rejeté

Vous pouvez aussi opter pour une table STRICT, qui corrige ce bug du NULL dans la clé primaire. Ajouter systématiquement NOT NULL sur chaque colonne d'une clé primaire SQLite, c'est une assurance qui ne coûte rien.

Clé primaire ou contrainte UNIQUE ?

Les deux empêchent les doublons. Voici ce qui les distingue :

Une table ne peut avoir qu'une seule clé primaire, mais autant de contraintes UNIQUE que vous voulez.
La clé primaire est l'identifiant « principal » de la table — c'est elle que les clés étrangères ciblent par défaut.
Une colonne INTEGER PRIMARY KEY devient le rowid de la table, alors qu'une colonne entière marquée UNIQUE, non.
Les colonnes UNIQUE acceptent sans broncher plusieurs NULL (chaque NULL est considéré comme distinct des autres).

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

INSERT INTO accounts (email, username) VALUES ('a@x.com', 'ada');
INSERT INTO accounts (email, username) VALUES ('b@x.com', 'boris');
SELECT * FROM accounts;

id représente l'identité de la ligne. email et username sont eux aussi uniques, mais ce sont des attributs métier : ils peuvent changer un jour, alors que id ne devrait jamais bouger.

Ajouter une clé primaire après coup (à éviter, en général)

La commande ALTER TABLE de SQLite est volontairement limitée. Impossible de lancer ALTER TABLE ... ADD PRIMARY KEY : cette syntaxe n'existe tout simplement pas. Si vous avez oublié la clé primaire et que la table contient déjà des données, la seule solution propre est de la recréer :

CREATE TABLE events (
    name TEXT,
    occurred_at TEXT
);
INSERT INTO events VALUES ('login', '2026-01-01'), ('logout', '2026-01-02');

-- Reconstruction avec une clé primaire :
CREATE TABLE events_new (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    occurred_at TEXT NOT NULL
);
INSERT INTO events_new (name, occurred_at) SELECT name, occurred_at FROM events;
DROP TABLE events;
ALTER TABLE events_new RENAME TO events;

SELECT * FROM events;

C'est la procédure habituelle de migration en SQLite. En vrai, encapsulez-la dans une transaction, et désactivez momentanément les clés étrangères si d'autres tables référencent celle-ci. Morale de l'histoire : soignez votre clé primaire dès le CREATE TABLE.

Petite checklist

Au moment d'écrire une nouvelle table, posez-vous ces questions :

La ligne possède-t-elle un identifiant unique naturel ? Si c'est un simple entier, partez sur INTEGER PRIMARY KEY.
L'identité repose-t-elle en réalité sur une combinaison de colonnes (table d'association) ? Dans ce cas, utilisez une PRIMARY KEY (col_a, col_b) au niveau de la table.
La clé est-elle du texte ou un autre type non entier ? Ajoutez explicitement NOT NULL.
Avez-vous vraiment besoin d'AUTOINCREMENT ? Probablement pas.
La table est-elle petite, lue plus souvent qu'écrite, avec une clé primaire non entière ? Pensez à WITHOUT ROWID (abordé dans la doc sur rowid).

La suite : rowid

INTEGER PRIMARY KEY est apparu rapidement comme « un alias de rowid » — mais le rowid est la fondation de toute table SQLite classique, et il mérite qu'on s'y attarde. C'est l'objet de la page suivante.

Questions fréquentes

Comment définir une clé primaire en SQLite ?

Il suffit d'ajouter PRIMARY KEY à une colonne dans votre CREATE TABLE, par exemple id INTEGER PRIMARY KEY. Pour une clé qui couvre plusieurs colonnes, on passe par une contrainte au niveau de la table : PRIMARY KEY (col_a, col_b). La colonne (ou la combinaison) doit être unique sur l'ensemble des lignes.

Quelle différence entre INTEGER PRIMARY KEY et les autres clés primaires en SQLite ?

INTEGER PRIMARY KEY est un cas à part : la colonne devient un alias du rowid interne de la table, donc elle est stockée directement dans le B-tree, sans index supplémentaire. Tout autre type, ou une clé composite, génère un index unique séparé. Pour un identifiant numérique sur une seule colonne, INTEGER PRIMARY KEY est plus rapide et plus compact.

Faut-il vraiment ajouter AUTOINCREMENT sur une clé primaire SQLite ?

En général, non. Une INTEGER PRIMARY KEY attribue déjà automatiquement un rowid unique quand vous insérez NULL. AUTOINCREMENT ajoute uniquement la garantie qu'un id ne sera jamais réutilisé après suppression — au prix d'une table sqlite_sequence supplémentaire. À éviter sauf si vous avez réellement besoin de ce comportement strictement monotone.

Pourquoi ma clé primaire SQLite accepte-t-elle des valeurs NULL ?

C'est un bug historique conservé pour la rétrocompatibilité : dans une table classique, une colonne de clé primaire dont le type n'est pas INTEGER accepte les NULL tant que vous n'ajoutez pas explicitement NOT NULL. La seule exception, c'est INTEGER PRIMARY KEY, qui n'autorise jamais NULL. Le réflexe sûr : mettre NOT NULL sur chaque colonne de clé primaire, ou utiliser une table STRICT où la règle est correctement appliquée.

Concepts liés

Clés primaires SQLite : INTEGER, composite et AUTOINCREMENT