Introducción

El mismo sitio de SQLite3 dice que no recomienda el autoincremento o las columnas auto incrementables. Sin embargo, algunas veces es necesario hacer esto y según yo, no afecta tanto al rendimiento.

Veamos cómo crear una tabla con autoincremento y cómo es que SQLite3 maneja eso internamente. Por cierto, ya sabemos que este motor crea la columna rowid pero no podemos confiar en ella.

¿Para qué el autoincremento en SQLite3?

A veces es usado para ir incrementando un valor entero y tomarlo como id. Es como la columna autoincrementable de MySQL.

Ejemplo de tabla con autoincremento en SQLite3

Vamos a crear una tabla llamada canciones.

No veremos relaciones, normalización ni esas cosas porque no tienen que ver con el post.

La tabla guardará el nombre de la canción y el artista que la canta. La definición queda así:

create table if not exists canciones (
  id      integer primary key  autoincrement,
  nombre  text not null,
  artista text not null
);

Con esto puedes darte una idea de cómo es la sintaxis para crear una tabla con autoincremento. La llave debe ser un entero, debes ponerla como clave primaria con primary key y finalmente definirla como autoincrement.

Más tarde podemos insertar datos sin preocuparnos por poner la clave, así:

insert into canciones(nombre, artista) values ('Burning Love', 'Elvis Presley');

Ahí no estamos poniendo el id autoincrementable, ya que SQLite3 se encarga de ello. Vamos a insertar más:

insert into canciones (nombre, artista)
values ('We three (My Echo, My Shadow, and Me)', 'Ink spots'),
       ('Under My Thumb', 'The Rolling Stones'),
       ('You Never Can Tell', 'Chuck Berry');

Al insertarlos y más tarde listarlos con un:

select id, nombre, artista from canciones;

Obtenemos esta salida:

En ese caso comenzó con 1, luego siguió el 2 y así hasta el 4.

Ah, y aunque eliminemos un dato, el id no se reutiliza como vimos en el post que cito al inicio. Así que esto es 100 % seguro.

Por ejemplo, si elimino la última canción con:

delete from canciones where id = 4;

y luego inserto otra con:

insert into canciones (nombre, artista) values ('Ramones', "We're a Happy Family");,

el id ahora será el 5 (no reutiliza el 4):

Con eso ya podemos trabajar a gusto.

¿Y cómo hace SQLite3 para saber la posición?

Como SQLite3 no se ejecuta en un servidor y es manejado en un fichero, puede ser un poco complicado guardar metadatos sobre la tabla. Así que el motor crea otra tabla que guarda los autoincrementables, y es llamada sqlite_sequence.

En ella, lo único que se guarda es el nombre de la tabla que tiene autoincremento y el último valor conocido. En el ejemplo de las canciones ese número va en 5.

Entonces ese nombre de tabla es reservado, pero igualmente podemos modificarlo para reiniciar el contador o cosas de esas.

Ya que estamos por aquí, te recomiendo el explorador que uso para ver mis bases de datos de SQLite3.

Funciona a la perfección, es como un phpmyadmin para SQLite (te permite administrar todo con una interfaz gráfica, crear tablas, editar datos, ver definiciones, etcétera).

Su nombre es DB Browser for SQLite y puedes visitar la página oficial aquí.

Oh, y si quieres probar un poco con Python aquí dejo un script que es una simple línea de comandos para comunicarte con este motor.