Comprendre et utiliser l'AUTOINCREMENT dans SQLite

Le mystère du rowid dans SQLite

Dans SQLite, chaque table possède par défaut un identifiant caché appelé rowid. Ce dernier agit comme une sorte de clé primaire interne, un numéro unique attribué automatiquement à chaque ligne. Il permet un accès rapide aux données sans que l’utilisateur ait besoin de déclarer explicitement une colonne dédiée.

Pourtant, derrière cette simplicité se cache une limite importante : le rowid n’est pas une colonne visible ni manipulable directement dans toutes les situations. Même s'il peut être interrogé (via SELECT rowid FROM table), il n'est pas le garant de l'intégrité référentielle. Il ne peut pas être utilisé comme clé étrangère dans d’autres tables car après la suppression d'une ligne, SQLite est libre de réutiliser cet identifiant lors d'une insertion ultérieure (ce que AUTOINCREMENT empêche). Le rowid est une solution SQLite pour l’accès interne rapide, mais il n’est pas conçu pour assurer l’intégrité référentielle entre tables.

Déclarer sa propre clé AUTOINCREMENT

Pour pallier les limites du rowid, SQLite permet de déclarer explicitement une colonne INTEGER PRIMARY KEY AUTOINCREMENT.

CREATE TABLE matable (id INTEGER PRIMARY KEY AUTOINCREMENT, …);

Par défaut, si vous déclarez simplement une colonne en tant que INTEGER PRIMARY KEY (sans AUTOINCREMENT), celle-ci agit comme un alias de l'identifiant interne (rowid). Bien qu'elle soit visible, elle hérite du comportement standard : si vous supprimez la ligne avec l'ID maximal, SQLite est libre de réutiliser cet identifiant lors de la prochaine insertion.

L'ajout du mot-clé AUTOINCREMENT transforme ce comportement. Il ne s'agit plus d'un simple alias. Cette contrainte force SQLite à garantir une séquence strictement monotone : L'ID ne fera qu'augmenter et ne sera jamais réattribué, même après la suppression de lignes. Pour respecter cette garantie, SQLite ignore le suivi standard du rowid et gère un compteur persistant via la table système cachée sqlite_sequence. C'est ce mécanisme qui assure la stabilité de l'identifiant, ce qui est crucial pour maintenir l'intégrité référentielle via des clés étrangères.

Le cas particulier du WITHOUT ROWID

SQLite offre aussi la possibilité de créer des tables WITHOUT ROWID, c’est-à-dire sans cet identifiant interne qui facilite l’accès rapide aux lignes. Cette approche peut améliorer les performances et réduire l’espace de stockage dans certains cas, notamment lorsque la clé primaire est complexe ou composite.

En choisissant cette option, vous supprimez le mécanisme interne du rowid qui gère l'incrémentation. Par conséquent, l'utilisation de AUTOINCREMENT est exclue. Si vous optez pour WITHOUT ROWID, vous devez impérativement vous occuper vous-même de la gestion et de l'unicité de votre clé primaire. Cela signifie que vous devrez utiliser une clé composite (plusieurs colonnes pour garantir l'unicité) ou générer vous-même les identifiants uniques (par exemple, en utilisant des UUID/GUID ou un mécanisme de séquence applicatif) avant l'insertion. Par conséquent, si votre modèle de données repose sur des clés numériques auto-incrémentées simples et fiables pour les relations entre tables, l'option WITHOUT ROWID n'est pas adaptée.