Docs ejecutables de SQLite: Claves primarias

Q: ¿Cómo se define una clave primaria en SQLite?

Añade PRIMARY KEY a una columna dentro de tu sentencia CREATE TABLE, por ejemplo id INTEGER PRIMARY KEY. Si necesitas una clave que abarque varias columnas, usa la restricción a nivel de tabla: PRIMARY KEY (cola, colb). La columna —o la combinación— tiene que ser única para cada fila.

Q: ¿Qué diferencia hay entre INTEGER PRIMARY KEY y el resto de claves primarias en SQLite?

INTEGER PRIMARY KEY es un caso especial: se convierte en un alias del rowid interno de la tabla, así que se almacena directamente en el árbol B sin ningún índice extra. Cualquier otro tipo, o una clave compuesta, genera un índice único aparte. Para ids numéricos de una sola columna, INTEGER PRIMARY KEY resulta más rápido y ocupa menos.

Q: ¿Hace falta usar AUTOINCREMENT en una clave primaria de SQLite?

Normalmente no. Una INTEGER PRIMARY KEY ya asigna automáticamente un rowid único cuando insertas NULL. Lo único que añade AUTOINCREMENT es la garantía de que los ids no se reutilizan tras un borrado, y eso tiene un coste: una tabla extra sqlite_sequence. Salvo que necesites expresamente ese comportamiento monótono, mejor déjalo fuera.

Qué hace realmente una clave primaria

La clave primaria es la columna (o combinación de columnas) que identifica de forma única cada fila de una tabla. Dos filas no pueden compartir el mismo valor de clave primaria. SQLite se encarga de hacer cumplir esa regla y, además, usa la clave para localizar filas rápidamente.

La forma más sencilla se declara directamente en la columna:

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

INSERT INTO users (email, name) VALUES ('ada@example.com', 'Ada');
INSERT INTO users (email, name) VALUES ('boris@example.com', 'Boris');

SELECT * FROM users;

No has indicado un id y SQLite lo ha rellenado por ti. No es magia: es un caso especial de INTEGER PRIMARY KEY que conviene entender antes de escribir nada más.

INTEGER PRIMARY KEY es un caso especial

En la mayoría de bases de datos, una clave primaria es simplemente un índice único. En SQLite, cada tabla normal ya tiene un entero oculto de 64 bits llamado rowid que identifica las filas a nivel interno. Cuando declaras una columna exactamente como INTEGER PRIMARY KEY, esa columna pasa a ser el rowid. Sin índice adicional, sin almacenamiento extra: tu id y la ubicación física de la fila son lo mismo.

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

INSERT INTO posts (title) VALUES ('Primero'), ('Segundo'), ('Tercero');

SELECT id, rowid, title FROM posts;

id y rowid son la misma columna con dos nombres. Las búsquedas por id van directas a la fila: no hay un segundo árbol que recorrer. Por eso la recomendación habitual en SQLite es: si quieres una clave primaria numérica, escribe INTEGER PRIMARY KEY tal cual. Ni INT, ni BIGINT, ni INTEGER NOT NULL PRIMARY KEY (bueno, ese último funciona, pero el tipo debe ser exactamente INTEGER).

Otros tipos también funcionan, solo que SQLite les crea un índice único aparte. No pasa nada, simplemente no es tan compacto.

AUTOINCREMENT casi nunca hace falta

Un reflejo típico cuando vienes de otras bases de datos es escribir id INTEGER PRIMARY KEY AUTOINCREMENT. En SQLite, la palabra clave AUTOINCREMENT hace algo más acotado de lo que sugiere su nombre, y la mayoría de las veces no la necesitas.

Sin AUTOINCREMENT, una columna INTEGER PRIMARY KEY se rellena automáticamente con el rowid más alto existente más uno. Si borras la última fila, el siguiente INSERT puede reutilizar ese id.

Con AUTOINCREMENT, SQLite lleva la cuenta del id más alto que se ha usado en una tabla auxiliar llamada sqlite_sequence y nunca reutiliza valores, ni siquiera después de un borrado.

CREATE TABLE plain (id INTEGER PRIMARY KEY, label TEXT);
CREATE TABLE strict_id (id INTEGER PRIMARY KEY AUTOINCREMENT, label TEXT);

INSERT INTO plain (label) VALUES ('a'), ('b'), ('c');
INSERT INTO strict_id (label) VALUES ('a'), ('b'), ('c');

DELETE FROM plain WHERE id = 3;
DELETE FROM strict_id WHERE id = 3;

INSERT INTO plain (label) VALUES ('nuevo');
INSERT INTO strict_id (label) VALUES ('nuevo');

SELECT 'plain' AS table_name, id, label FROM plain
UNION ALL
SELECT 'strict_id', id, label FROM strict_id;

La tabla plain reutilizó el id 3. La tabla con AUTOINCREMENT, en cambio, saltó al 4. A menos que tengas una razón real para impedir la reutilización de ids —auditorías, referencias externas que sobreviven al borrado—, olvídate de AUTOINCREMENT. Te cuesta una escritura extra por cada inserción y una tabla aparte solo para llevar la cuenta.

Clave primaria compuesta en SQLite

A veces una sola columna no alcanza. Por ejemplo, una tabla intermedia que asocia usuarios con roles se identifica de forma única por el par (user_id, role_id). Para esos casos, declara la clave a nivel de tabla:

CREATE TABLE user_roles (
    user_id  INTEGER NOT NULL,
    role_id  INTEGER NOT NULL,
    granted_at TEXT DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, role_id)
);

INSERT INTO user_roles (user_id, role_id) VALUES (1, 10), (1, 20), (2, 10);
-- Esta viola la clave compuesta:
INSERT INTO user_roles (user_id, role_id) VALUES (1, 10);

El par tiene que ser único en toda la tabla: (1, 10) solo puede aparecer una vez. Cada columna por separado sí puede repetirse sin problema. Esa es justamente la idea: un usuario puede tener varios roles, un rol puede pertenecer a varios usuarios, pero una combinación concreta usuario-rol existe como mucho una vez.

Una clave primaria compuesta en SQLite genera un índice aparte que cubre las columnas indicadas. No se convierte en el rowid: ese trato especial solo lo recibe un único INTEGER PRIMARY KEY.

El footgun de los NULL en la clave primaria

Aquí va una rareza que pilla por sorpresa a quienes vienen de PostgreSQL o MySQL: en una tabla SQLite normal, una columna de clave primaria que no sea INTEGER PRIMARY KEY puede contener NULL. Es un bug histórico que los autores de SQLite mantuvieron por compatibilidad hacia atrás.

CREATE TABLE products (
    sku   TEXT PRIMARY KEY,
    name  TEXT
);

INSERT INTO products (sku, name) VALUES ('A1', 'Manzana');
INSERT INTO products (sku, name) VALUES (NULL, 'Misterio 1');
INSERT INTO products (sku, name) VALUES (NULL, 'Misterio 2');

SELECT * FROM products;

Se colaron dos filas con NULL saltándose la clave primaria. La solución es añadir NOT NULL de forma explícita en cada columna de la clave primaria que no sea entera:

CREATE TABLE products (
    sku   TEXT NOT NULL PRIMARY KEY,
    name  TEXT
);

INSERT INTO products (sku, name) VALUES ('A1', 'Manzana');
INSERT INTO products (sku, name) VALUES (NULL, 'Misterio');  -- ahora se rechaza

O bien usa una tabla STRICT, donde el bug del NULL en la PK está corregido. Acostumbrarse a escribir NOT NULL en cada columna de la clave primaria es un seguro barato.

Diferencia entre PRIMARY KEY y UNIQUE en SQLite

Ambas evitan duplicados. Las diferencias son estas:

Una tabla tiene como máximo una clave primaria, pero puede tener varias restricciones UNIQUE.
La clave primaria es el identificador "principal" de la tabla: las claves foráneas apuntan a ella por defecto.
Una columna INTEGER PRIMARY KEY se convierte en el rowid; una columna entera con UNIQUE no.
Las columnas UNIQUE aceptan sin problemas varios NULL (cada NULL se considera distinto).

CREATE TABLE accounts (
    id       INTEGER PRIMARY KEY,
    email    TEXT NOT NULL UNIQUE,
    username TEXT NOT NULL UNIQUE
);

INSERT INTO accounts (email, username) VALUES ('a@x.com', 'ada');
INSERT INTO accounts (email, username) VALUES ('b@x.com', 'boris');
SELECT * FROM accounts;

id es la identidad de la fila. email y username también son únicos, pero son atributos de negocio: pueden cambiar, mientras que el id no debería hacerlo.

Añadir una clave primaria después (mejor: no lo hagas)

El ALTER TABLE de SQLite es limitado. No puedes ejecutar ALTER TABLE ... ADD PRIMARY KEY, esa sentencia simplemente no existe. Si se te olvidó definir la clave primaria y la tabla ya tiene datos, el camino es recrearla:

CREATE TABLE events (
    name TEXT,
    occurred_at TEXT
);
INSERT INTO events VALUES ('login', '2026-01-01'), ('logout', '2026-01-02');

-- Reconstruir con una clave primaria:
CREATE TABLE events_new (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    occurred_at TEXT NOT NULL
);
INSERT INTO events_new (name, occurred_at) SELECT name, occurred_at FROM events;
DROP TABLE events;
ALTER TABLE events_new RENAME TO events;

SELECT * FROM events;

Este es el baile típico de migración en SQLite. En código real, envuélvelo en una transacción y desactiva las claves foráneas un momento si otras tablas hacen referencia a esta. La lección: define bien la clave primaria desde el CREATE TABLE.

Checklist rápido

Cuando escribas una tabla nueva, pregúntate:

¿La fila tiene un identificador único natural? Si es un único entero, usa INTEGER PRIMARY KEY.
¿La identidad es realmente la combinación de varias columnas (una tabla de unión)? Usa una clave primaria compuesta a nivel de tabla con PRIMARY KEY (col_a, col_b).
¿La clave es de tipo texto u otro tipo no entero? Añade NOT NULL de forma explícita.
¿De verdad necesitas AUTOINCREMENT? Lo más probable es que no.
¿Es una tabla pequeña, mayormente de lectura y con una clave primaria no entera? Plantéate usar WITHOUT ROWID (lo vemos junto con la doc de rowid).

Siguiente paso: rowid

INTEGER PRIMARY KEY apareció de pasada como "un alias de rowid", pero el rowid es la base que sostiene cualquier tabla normal de SQLite, y vale la pena entenderlo a fondo. De eso va la siguiente página.

Preguntas frecuentes

¿Cómo se define una clave primaria en SQLite?

Añade PRIMARY KEY a una columna dentro de tu sentencia CREATE TABLE, por ejemplo id INTEGER PRIMARY KEY. Si necesitas una clave que abarque varias columnas, usa la restricción a nivel de tabla: PRIMARY KEY (col_a, col_b). La columna —o la combinación— tiene que ser única para cada fila.

¿Qué diferencia hay entre INTEGER PRIMARY KEY y el resto de claves primarias en SQLite?

INTEGER PRIMARY KEY es un caso especial: se convierte en un alias del rowid interno de la tabla, así que se almacena directamente en el árbol B sin ningún índice extra. Cualquier otro tipo, o una clave compuesta, genera un índice único aparte. Para ids numéricos de una sola columna, INTEGER PRIMARY KEY resulta más rápido y ocupa menos.

¿Hace falta usar AUTOINCREMENT en una clave primaria de SQLite?

Normalmente no. Una INTEGER PRIMARY KEY ya asigna automáticamente un rowid único cuando insertas NULL. Lo único que añade AUTOINCREMENT es la garantía de que los ids no se reutilizan tras un borrado, y eso tiene un coste: una tabla extra sqlite_sequence. Salvo que necesites expresamente ese comportamiento monótono, mejor déjalo fuera.

¿Por qué mi clave primaria en SQLite admite valores NULL?

Es un bug histórico que se mantiene por compatibilidad: en las tablas normales, una columna de clave primaria que no sea INTEGER acepta NULL salvo que añadas explícitamente NOT NULL. La excepción es INTEGER PRIMARY KEY, que nunca admite NULL. Para curarte en salud, escribe NOT NULL en cada columna de clave primaria, o usa una tabla STRICT, donde la regla sí se aplica como toca.

Conceptos relacionados

Claves primarias en SQLite: INTEGER, compuestas y AUTOINCREMENT