Cuidado con la columna rowid autoincrementable de SQLite

Introducción

Internamente, SQLite (la base de datos que no necesita servidor) agrega una columna llamada rowid. Esto es una columna autoincrementable y por lo tanto no se repite.

Probablemente seamos perezosos y confiemos en ella, creyendo que todo irá bien.

Recientemente estuve trabajando en un proyecto con SQLite y descubrí algo muy importante que debemos saber al trabajar con el rowid implícito.

Explicación del error

Si no utilizas relaciones en tus tablas, puedes dejar de leer. Lo que hace esta característica es que no recuerda cuáles ids ya fueron utilizados.

Es decir, si tenemos 3 filas (con el id 1, 2 y 3 respectivamente) y eliminamos la tercer fila, sólo quedarán 2 (pero el id 3 ya había estado en uso).

Más tarde insertamos un nuevo registro, esperando que el id sea 4 (pues el 3, aunque no existe, ya fue utilizado), pero no es así, el id será 3.

Esto trae muchos problemas, pues si confiamos en que nunca se repetirá aunque se elimine, traerá resultados inesperados.

Un ejemplo que se me ocurre es tener 3 clientes. Luego hacer ventas que tengan una relación con clientes y que guarden el id de cliente.

Si eliminamos el tercer cliente, y luego creamos uno nuevo, parecerá que este nuevo cliente será el que ha hecho las ventas.

Rowid autoincrementable

Como lo dije hace un momento, si tenemos una tabla así:

CREATE TABLE mascotas(
  nombre TEXT NOT NULL
);

Automáticamente se agregará el rowid aunque no lo declaremos. Si no lo necesitamos, no hay problema.

Pero si necesitamos una columna autoincrementable hay que declararlo explícitamente como se ve abajo.

Solución

Hay que declarar implícitamente una columna de autoincremento, y también indicar que es una clave primaria.

Puede llamarse como deseemos pero en mi caso le pondré “rowid” para que mi app siga siendo compatible.

La tabla quedaría así:

CREATE TABLE mascotas(
  rowid INTEGER PRIMARY KEY AUTOINCREMENT,
  nombre TEXT NOT NULL
);

Con eso terminamos. Aquí dejo las referencias.