SQLite ROWID : clé cachée et WITHOUT ROWID expliqués

Chaque table cache une colonne secrète

Quand vous créez une simple table en SQLite, vous héritez déjà d'une colonne que vous n'avez jamais déclarée :

CREATE TABLE notes (
    title TEXT,
    body  TEXT
);

INSERT INTO notes (title, body) VALUES ('première', 'bonjour');
INSERT INTO notes (title, body) VALUES ('deuxième', 'monde');

SELECT rowid, title, body FROM notes;

Cette colonne rowid existe vraiment. SQLite en attribue une à chaque ligne de chaque table ordinaire, que vous le demandiez ou non. C'est un entier signé sur 64 bits, unique au sein de la table, et c'est la véritable clé que SQLite utilise pour retrouver les lignes dans son stockage en B-tree. Voyez-la comme la colonne vertébrale de la table — l'index qui maintient tout le reste en ordre.

En général, on ne la voit pas, car SELECT * ne l'inclut pas. Il faut la demander explicitement par son nom.

Les trois alias du ROWID en SQLite

Comme rowid revient souvent dans le SQL écrit pour d'autres bases de données, SQLite accepte trois noms différents pour désigner la même colonne :

CREATE TABLE notes (title TEXT);
INSERT INTO notes VALUES ('a'), ('b'), ('c');

SELECT rowid, oid, _rowid_, title FROM notes;

rowid, oid et _rowid_ désignent tous la même colonne cachée. Si vous avez déclaré une vraie colonne portant l'un de ces noms, c'est la vôtre qui l'emporte et l'alias devient inutilisable — mais c'est le seul piège. Au quotidien, contentez-vous d'écrire rowid.

INTEGER PRIMARY KEY : la formule magique

Voici le détail qui surprend tous ceux qui débarquent d'un autre SGBD. Si vous déclarez une colonne exactement comme INTEGER PRIMARY KEY, cette colonne n'est pas stockée à part — elle devient le rowid :

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

INSERT INTO users (name) VALUES ('Ada'), ('Boris'), ('Rosa');

SELECT rowid, id, name FROM users;

rowid et id, c'est en fait la même colonne sous deux noms différents. Quand un INSERT ne précise pas id, SQLite choisit automatiquement un entier (en général max(rowid) + 1). C'est précisément pour ça que INTEGER PRIMARY KEY est la façon la plus efficace de doter une table d'une clé auto-incrémentée en SQLite : aucune colonne supplémentaire, aucun index en plus, juste le rowid lui-même.

Attention à l'orthographe exacte. INT PRIMARY KEY n'est pas équivalent : ici, INT et INTEGER ne produisent pas le même comportement.

CREATE TABLE a (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE b (id INT PRIMARY KEY, name TEXT);

INSERT INTO a (name) VALUES ('x');
INSERT INTO b (name) VALUES ('y');

SELECT rowid, id, name FROM a;
SELECT rowid, id, name FROM b;

Dans la table a, id et rowid pointent vers la même chose. Dans la table b, id est une colonne classique et rowid reste l'entier caché habituel, distinct. Pire encore : b.id ne se remplit pas automatiquement à l'insertion — il vaut NULL tant que vous ne lui donnez pas de valeur. Bref, restez sur INTEGER PRIMARY KEY (en toutes lettres) si vous voulez profiter de l'alias rowid.

Récupérer le ROWID après un INSERT

Après un INSERT, on a souvent besoin de connaître le rowid qui vient d'être attribué — typiquement pour rattacher une ligne enfant à cet identifiant. SQLite met à disposition la fonction last_insert_rowid() pour ça :

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

INSERT INTO posts (title) VALUES ('premier');
INSERT INTO posts (title) VALUES ('deuxième');

SELECT last_insert_rowid();

La fonction renvoie le rowid de la dernière insertion réussie sur la connexion courante. La plupart des pilotes de base de données exposent la même valeur via cursor.lastrowid (ou un nom équivalent). La clause RETURNING (qu'on verra plus loin) est une autre façon de récupérer cette valeur directement au sein de l'INSERT.

Le rowid SQLite n'est pas permanent

Le rowid d'une ligne reste stable tant que la ligne existe, mais ce n'est pas un identifiant à vie. Un VACUUM peut renuméroter les rowids, et si vous supprimez une ligne, son numéro pourra être réutilisé par une insertion ultérieure :

CREATE TABLE items (name TEXT);
INSERT INTO items VALUES ('a'), ('b'), ('c');

DELETE FROM items WHERE name = 'c';
INSERT INTO items VALUES ('d');

SELECT rowid, name FROM items;

Bon à savoir : selon la version de SQLite et le contexte, la nouvelle ligne peut réutiliser ou non l'ancien rowid — l'important, c'est qu'il ne faut pas compter sur lui pour rester unique éternellement. Si vous avez besoin d'un identifiant qui survit aux suppressions, aux VACUUM et aux exports, déclarez votre propre colonne INTEGER PRIMARY KEY (qui fige la valeur sur la ligne concernée) et envisagez le mot-clé AUTOINCREMENT si, en plus, vous voulez des valeurs strictement croissantes qui ne sont jamais réutilisées.

Les tables WITHOUT ROWID dans SQLite

Parfois, le rowid devient un poids mort dont on se passerait bien — typiquement quand votre vraie clé n'est pas un entier. Prenez une table de villes indexée par nom : vous vous retrouvez avec deux structures côte à côte, le B-tree du rowid d'un côté, et un index séparé sur name pour faire respecter la clé primaire de l'autre. La clause WITHOUT ROWID permet de fusionner tout ça en une seule structure :

CREATE TABLE cities (
    name       TEXT PRIMARY KEY,
    population INTEGER
) WITHOUT ROWID;

INSERT INTO cities VALUES ('Lisbon', 545000), ('Paris', 2102000);

SELECT * FROM cities WHERE name = 'Lisbon';
-- SELECT rowid FROM cities;   -- erreur : aucune colonne nommée rowid

Désormais, c'est name qui sert de vraie clé de stockage. Les recherches par name évitent un niveau d'indirection, et la table occupe moins d'espace. En contrepartie :

• Plus de rowid, oid ou _rowid_ — ces colonnes n'existent tout simplement pas.
• last_insert_rowid() n'est plus mis à jour lors des insertions dans cette table.
• L'I/O incrémentale sur les BLOB et quelques fonctionnalités de réplication ne sont pas disponibles.
• La table doit obligatoirement déclarer une PRIMARY KEY.

WITHOUT ROWID est une optimisation ciblée, pas un réglage par défaut. On y a recours quand la clé primaire n'est pas un entier et que la table est volumineuse ou très sollicitée en écriture. Pour les tables classiques avec clé entière, l'organisation rowid standard est déjà optimale.

Le modèle mental à retenir

L'essentiel en quelques points :

• Toute table SQLite ordinaire possède une clé entière 64 bits cachée appelée rowid.
• INTEGER PRIMARY KEY (orthographe exacte) fait de votre colonne un alias de ce rowid.
• last_insert_rowid() permet de récupérer la valeur qui vient d'être attribuée.
• Les rowid peuvent être réutilisés après suppression, et renumérotés par VACUUM.
• Les tables WITHOUT ROWID se passent de la clé cachée et utilisent directement votre clé primaire déclarée — pratique pour les clés non entières, mais on perd certaines fonctionnalités.

La plupart du temps, vous n'avez même pas à penser au rowid. Vous déclarez id INTEGER PRIMARY KEY, vous laissez SQLite gérer la numérotation, et basta. Ces détails comptent surtout quand vous optimisez le stockage, quand vous lisez un schéma existant, ou quand vous vous demandez pourquoi INT PRIMARY KEY ne se comporte pas comme INTEGER PRIMARY KEY.

La suite : NOT NULL et DEFAULT

Maintenant que l'identité de la ligne est cadrée, la couche suivante consiste à s'assurer que les autres colonnes contiennent des valeurs cohérentes. NOT NULL et DEFAULT sont les deux clauses qui font le gros du boulot — c'est ce qu'on aborde ensuite.