ROWID en SQLite: claves ocultas y WITHOUT ROWID

Cada tabla tiene una columna secreta

Crea una tabla normalita en SQLite y resulta que ya hay una columna que no declaraste:

CREATE TABLE notes (
    title TEXT,
    body  TEXT
);

INSERT INTO notes (title, body) VALUES ('primera', 'hola');
INSERT INTO notes (title, body) VALUES ('segunda', 'mundo');

SELECT rowid, title, body FROM notes;

Esa columna rowid existe de verdad. SQLite le asigna una a cada fila de toda tabla normal, la pidas o no. Es un entero con signo de 64 bits, único dentro de la tabla, y es la clave real que SQLite usa para localizar filas en su almacenamiento B-tree. Pensalo como la columna vertebral de la tabla: el índice que mantiene todo lo demás en orden.

Normalmente no la ves porque SELECT * no la incluye. Tenés que pedirla por su nombre.

El rowid de SQLite tiene tres alias

Como rowid aparece muy seguido en SQL escrito para otras bases de datos, SQLite acepta tres nombres para la misma columna:

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

SELECT rowid, oid, _rowid_, title FROM notes;

rowid, oid y _rowid_ apuntan a la misma columna oculta. Si declaras una columna real con alguno de esos nombres, gana la tuya y el alias deja de estar disponible, pero esa es la única pega. En el día a día, usa rowid y listo.

INTEGER PRIMARY KEY: la frase mágica en SQLite

Aquí está el detalle que descoloca a quien viene de otras bases de datos. Si declaras una columna exactamente como INTEGER PRIMARY KEY, esa columna no se guarda aparte: se convierte en el 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 e id son la misma columna con dos nombres distintos. Cuando un INSERT no especifica id, SQLite asigna un entero automáticamente (normalmente el rowid máximo + 1). Por eso INTEGER PRIMARY KEY es la forma más eficiente de tener una clave autoincremental en SQLite: no añade una columna extra ni un índice adicional, sino que reutiliza el propio rowid.

Ojo con la sintaxis exacta. INT PRIMARY KEY no es lo mismo que INTEGER PRIMARY KEY: aquí INT e INTEGER se comportan de forma distinta:

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;

En la tabla a, id y rowid apuntan al mismo valor. En cambio, en la tabla b, id es una columna normal y rowid es el entero oculto que va por su cuenta. Y peor todavía: b.id no se rellena solo al insertar — se queda en NULL hasta que tú le asignes un valor. La recomendación es clara: usa INTEGER PRIMARY KEY (escrito completo, sin abreviar) cuando quieras que funcione como alias del rowid.

Cómo obtener el ROWID de un INSERT

Después de un INSERT, lo habitual es querer saber qué rowid se acaba de asignar — normalmente para enlazar una fila hija con ese registro. Para eso, SQLite te ofrece last_insert_rowid():

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

INSERT INTO posts (title) VALUES ('primero');
INSERT INTO posts (title) VALUES ('segundo');

SELECT last_insert_rowid();

La función devuelve el rowid del último INSERT exitoso en la conexión actual. La mayoría de los drivers de base de datos exponen ese mismo valor como cursor.lastrowid o algo parecido. La cláusula RETURNING (que veremos más adelante) es otra forma de recuperarlo directamente como parte del propio INSERT.

Los ROWID no son permanentes

El rowid de una fila se mantiene estable mientras la fila exista, pero no es un identificador de por vida. Un VACUUM puede renumerar los rowids y, si borras una fila, ese número puede reutilizarse en un INSERT posterior:

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;

Fíjate en que la nueva fila puede o no reutilizar el rowid antiguo, según la versión y las circunstancias. La idea de fondo es que no puedes asumir que ese valor vaya a ser único para siempre. Si necesitas un identificador que sobreviva a borrados, VACUUM y exportaciones, declara tu propia columna INTEGER PRIMARY KEY (que ancla el valor a esa fila) y plantéate la palabra clave AUTOINCREMENT si lo que buscas concretamente son valores estrictamente crecientes que nunca se reutilicen.

Tablas sin rowid en SQLite (WITHOUT ROWID)

A veces el rowid es un coste extra que no te interesa pagar, sobre todo cuando tu clave real no es un entero. Imagina una tabla de ciudades indexada por nombre: acabas con dos estructuras, el árbol B del rowid y, aparte, un índice sobre name que hace cumplir la clave primaria. Con WITHOUT ROWID ambas se fusionan en una sola:

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;   -- error: no existe la columna: rowid

Ahora name es la clave de almacenamiento real. Las búsquedas por name se ahorran un nivel de indirección y la tabla ocupa menos. A cambio:

• No existen rowid, oid ni _rowid_: esas columnas simplemente no están.
• last_insert_rowid() no se actualiza con los inserts sobre esta tabla.
• No tendrás disponibles la E/S incremental de BLOB ni algunas funciones de replicación.
• La tabla tiene que declarar un PRIMARY KEY.

WITHOUT ROWID es una optimización puntual, no un valor por defecto. Tiene sentido cuando la clave primaria no es entera y la tabla es grande o recibe muchas escrituras. Para tablas normales con clave entera, el formato habitual con rowid ya es óptimo.

El modelo mental

Resumido a lo esencial:

• Toda tabla normal de SQLite tiene una clave entera oculta de 64 bits llamada rowid.
• INTEGER PRIMARY KEY (escrito tal cual) convierte tu columna en un alias de esa clave.
• Usa last_insert_rowid() para leer el valor que se acaba de asignar.
• Los rowid se pueden reutilizar tras un delete y VACUUM puede renumerarlos.
• Las tablas WITHOUT ROWID prescinden de la clave oculta y usan directamente la clave primaria que declares: vienen bien para claves no enteras, pero pierdes algunas funcionalidades.

La mayor parte del tiempo ni piensas en el rowid. Declaras id INTEGER PRIMARY KEY, dejas que SQLite se encargue de la numeración y a otra cosa. Conviene conocer la mecánica cuando ajustas el almacenamiento, lees esquemas ajenos o te preguntas por qué INT PRIMARY KEY se comporta distinto a INTEGER PRIMARY KEY.

Lo siguiente: NOT NULL y DEFAULT

Con la identidad de la fila ya resuelta, toca asegurarse de que el resto de columnas guarden valores con sentido. De eso se encargan principalmente NOT NULL y DEFAULT, y son justo lo que veremos a continuación.