Ir al contenido principal

SQLite: primary keys y foreign keys

De

 ¿en sqlite se puede especificar algo para que una pk sea autoincrement?

Respuesta rápida

Sí, en SQLite existen dos formas de crear una clave primaria autoincremental, dependiendo de si se requiere la reutilización o no de los IDs eliminados.

La forma estándar y recomendada es declarar la columna como INTEGER PRIMARY KEY. Esto hace que la columna sea un alias de la columna interna ROWID, la cual se incrementa automáticamente para cada nueva fila.

CREATE TABLE usuarios (
    id INTEGER PRIMARY KEY,
    nombre TEXT
);

Si se necesita garantizar que los IDs no se reutilicen (incluso si se eliminan filas), se debe añadir la palabra clave AUTOINCREMENT explícitamente. Sin embargo, esto impone una sobrecarga adicional de CPU, memoria y E/S, por lo que debe evitarse si no es estrictamente necesario.

CREATE TABLE usuarios (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    nombre TEXT
);

Puntos clave a considerar:

  • El tipo de dato debe ser INTEGER; usar INT o BIGINT no activará el autoincremento correctamente en SQLite.

  • Al insertar datos, se puede omitir la columna de ID o insertar NULL para que SQLite asigne el siguiente valor automáticamente.

  • Sin AUTOINCREMENT, SQLite puede reutilizar el ID de una fila eliminada si ese es el valor más alto disponible; con AUTOINCREMENT, los IDs siempre son mayores que cualquier valor usado previamente.

¿Es redundante si además de id INTEGER PRIMARY KEY también le pongo CONSTRAINT tabla_pk PRIMARY KEY (id)?

No es solo redundante: produce un error. SQLite no permite más de una declaración de PRIMARY KEY en una misma tabla. Si escribes ambas:

CREATE TABLE usuarios (
    id INTEGER PRIMARY KEY,
    nombre TEXT,
    CONSTRAINT usuarios_pk PRIMARY KEY (id)
);

obtendrás un error de ejecución al crear la tabla.

El PRIMARY KEY a nivel de columna (id INTEGER PRIMARY KEY) ya es la restricción de clave primaria. La forma a nivel de tabla (PRIMARY KEY (id)) es simplemente otra sintaxis para declarar la misma cosa, no una restricción adicional. Son dos formas equivalentes de lo mismo, y solo puedes usar una.

Si quieres un nombre para la restricción (útil por ejemplo para ON CONFLICT o para documentar), la forma correcta es usar solo la declaración a nivel de tabla:

CREATE TABLE usuarios (
    id INTEGER,
    nombre TEXT,
    CONSTRAINT usuarios_pk PRIMARY KEY (id)
);

Esto te da el mismo comportamiento de autoincremento (si el tipo es INTEGER) y te permite referenciar la restricción por nombre.

dada una PK con nombre ¿puedo agrergar una FK a dicha PK a una tabla que ya existe?

No. SQLite no soporta ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY en ninguna forma. El hecho de que la PK tenga nombre no cambia nada: no existe ninguna sintaxis para agregar una FK a una tabla ya creada.

La única forma es reconstruir la tabla:

PRAGMA foreign_keys = OFF;

BEGIN TRANSACTION;

ALTER TABLE pedidos RENAME TO _pedidos_old;

CREATE TABLE pedidos (
    id INTEGER PRIMARY KEY,
    usuario_id INTEGER,
    total REAL,
    CONSTRAINT fk_pedidos_usuario
        FOREIGN KEY (usuario_id) REFERENCES usuarios(id)
);

INSERT INTO pedidos SELECT * FROM _pedidos_old;

DROP TABLE _pedidos_old;

COMMIT;

PRAGMA foreign_keys = ON;

Puntos a tener en cuenta:

  • Desactivar foreign_keys antes es obligatorio: si lo dejas activo, al hacer DROP TABLE sobre la tabla padre (o sobre la propia tabla si tiene FK entrantes) se pueden disparar ON DELETE CASCADE y perder datos.

  • No olvides recrear índices, triggers o views que dependieran de la tabla original.

  • Al final, PRAGMA foreign_key_check; verifica que no queden referencias huérfanas.


mostrame cómo uso PRAGMA foreign_key_check

Sintaxis

-- Chequea toda la base de datos
PRAGMA foreign_key_check;

-- Chequea solo una tabla
PRAGMA foreign_key_check(pedidos);

-- Con esquema explícito (útil con ATTACH)
PRAGMA main.foreign_key_check;
PRAGMA temp.foreign_key_check(pedidos);

Resultado

Devuelve una fila por cada violación, con 4 columnas:

tablerowidparentfkid
pedidos42usuarios0

  • table → tabla hija (la que tiene el REFERENCES)

  • rowid → fila que viola la FK

  • parent → tabla padre referenciada

  • fkid → índice de la restricción (0 = primera FK de la tabla)

Si no devuelve filas, no hay violaciones.

Ejemplo práctico

-- Tablas de ejemplo
CREATE TABLE usuarios (id INTEGER PRIMARY KEY, nombre TEXT);
CREATE TABLE pedidos (
    id INTEGER PRIMARY KEY,
    usuario_id INTEGER,
    total REAL,
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id)
);

INSERT INTO usuarios VALUES (1, 'Ana');
-- Insertar pedido con usuario inexistente (con FK OFF)
PRAGMA foreign_keys = OFF;
INSERT INTO pedidos VALUES (1, 99, 50.0);

-- Detectar la violación
PRAGMA foreign_key_check;
-- pedidos|1|usuarios|0

-- Ver la fila problemática
SELECT * FROM pedidos WHERE rowid = 1;

Nota importante

PRAGMA foreign_key_check no requiere que foreign_keys esté en ON. Funciona independientemente de ese pragma — justamente es útil para detectar violaciones que se introdujeron cuando las FK estaban desactivadas (por ejemplo, durante la reconstrucción de tablas que te mostré antes).

Comentarios

Publicar un comentario