Docs ejecutables de SQLite: Claves foráneas

Q: ¿Cómo se crea una clave foránea en SQLite?

Basta con añadir una cláusula REFERENCES otratabla(columna) a la definición de la columna dentro del CREATE TABLE. Por ejemplo, authorid INTEGER REFERENCES authors(id) hace que author_id apunte a una fila de authors. Eso sí, la columna referenciada debe ser PRIMARY KEY o tener una restricción UNIQUE.

Q: ¿Qué hace ON DELETE CASCADE en SQLite?

ON DELETE CASCADE le dice a SQLite que borre automáticamente las filas hijas cuando se elimine la fila padre. Las otras opciones son RESTRICT (bloquea el borrado), SET NULL (deja la columna FK a NULL), SET DEFAULT y NO ACTION (la opción por defecto, en la práctica equivale a RESTRICT). Elige una u otra según si las filas hijas tienen sentido sin su padre.

Una clave foránea es un puntero entre tablas

Una clave foránea (foreign key) es una columna de una tabla cuyo valor tiene que coincidir con una fila de otra tabla. Es la forma que tienen las bases de datos relacionales de decir "esta fila de posts pertenece a aquella fila de authors" sin necesidad de duplicar el nombre y el correo del autor en cada post.

Veamos el ejemplo más sencillo posible: una tabla padre y una tabla hija enlazadas mediante una FK:

PRAGMA foreign_keys = ON;

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

CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT NOT NULL,
    author_id INTEGER REFERENCES authors(id)
);

INSERT INTO authors (name) VALUES ('Ada'), ('Boris');
INSERT INTO posts (title, author_id) VALUES ('Hola', 1), ('Notas', 2);

SELECT p.title, a.name
FROM posts p JOIN authors a ON a.id = p.author_id;

author_id INTEGER REFERENCES authors(id) es la declaración completa de la clave foránea. Lo que dice es: esta columna guarda un id de la tabla authors. La base de datos ya sabe que las dos tablas están relacionadas y, si la verificación está activa, rechazará cualquier inserción que apunte a un autor que no existe.

Las claves foráneas en SQLite están desactivadas por defecto

Este es el dato más importante sobre las claves foráneas en SQLite, y siempre pilla a todo el mundo por sorpresa: SQLite reconoce las cláusulas REFERENCES al parsear, pero no las aplica salvo que se lo pidas explícitamente. El motivo es de compatibilidad histórica, ya que muchas bases de datos antiguas se crearon antes de que existiera esta funcionalidad.

Mira lo que pasa cuando no hay verificación activa:

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT,
    author_id INTEGER REFERENCES authors(id)
);

-- el autor 999 no existe, pero SQLite lo acepta sin problema:
INSERT INTO posts (title, author_id) VALUES ('Huérfano', 999);

SELECT * FROM posts;

La fila huérfana entró sin problema. Para activar realmente la protección que buscas, ejecuta PRAGMA foreign_keys = ON; al inicio de cada conexión:

PRAGMA foreign_keys = ON;

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT,
    author_id INTEGER REFERENCES authors(id)
);

INSERT INTO posts (title, author_id) VALUES ('Huérfano', 999);

Ahora el insert falla con FOREIGN KEY constraint failed. El pragma se aplica por conexión, no por base de datos: la configuración no se guarda en el archivo. Cada aplicación, cada sesión de la CLI, cada fixture de tests tiene que activarlo. La mayoría del código en producción ejecuta PRAGMA foreign_keys = ON; justo después de abrir la conexión.

Qué exige realmente la cláusula REFERENCES

La columna a la que apuntas tiene que ser PRIMARY KEY o tener una restricción UNIQUE. Es la forma en la que SQLite puede garantizar que la búsqueda sea inequívoca. Los tipos también deberían ser compatibles: SQLite es bastante flexible con los tipos, pero mezclarlos es pedir problemas.

Puedes declarar la foreign key de dos maneras. En línea, junto a la columna:

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);

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

SELECT sql FROM sqlite_master WHERE name = 'posts';

O bien como una restricción a nivel de tabla, algo que se vuelve obligatorio cuando la clave foránea abarca varias columnas:

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

CREATE TABLE post_tags (
    post_id INTEGER NOT NULL,
    tag_id  INTEGER NOT NULL,
    PRIMARY KEY (post_id, tag_id),
    FOREIGN KEY (post_id) REFERENCES posts(id),
    FOREIGN KEY (tag_id)  REFERENCES tags(id)
);

SELECT sql FROM sqlite_master WHERE name = 'post_tags';

Ambas formas generan exactamente la misma restricción. Usa la que se lea mejor en cada tabla.

ON DELETE: qué pasa con las filas hijas

Cuando borras una fila padre, SQLite tiene que decidir qué hacer con las filas hijas que la apuntan. Esa política se define con ON DELETE:

PRAGMA foreign_keys = ON;

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);

CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT,
    author_id INTEGER REFERENCES authors(id) ON DELETE CASCADE
);

INSERT INTO authors (id, name) VALUES (1, 'Ada');
INSERT INTO posts (title, author_id) VALUES ('A'), ('B') ON CONFLICT DO NOTHING;
INSERT INTO posts (title, author_id) VALUES ('A', 1), ('B', 1);

DELETE FROM authors WHERE id = 1;
SELECT * FROM posts;

Borrar a Ada eliminó también sus dos publicaciones. Las opciones son:

CASCADE: borra los hijos junto con el padre. Va bien para datos "de propiedad", como las publicaciones de un autor o los ítems de una orden.
SET NULL: pone la columna FK a NULL. Útil cuando los hijos deben sobrevivir sin padre (por ejemplo, los comentarios de un usuario eliminado quedan como anónimos).
SET DEFAULT: asigna a la columna FK el valor por defecto declarado.
RESTRICT: impide el borrado si existen hijos. Falla de inmediato, al ejecutar la sentencia.
NO ACTION: el comportamiento por defecto. En la práctica funciona casi igual que RESTRICT (la comprobación se aplaza hasta el commit, pero el resultado es el mismo: no puedes dejar hijos huérfanos).

ON UPDATE funciona de la misma forma cuando cambia la clave del padre, aunque actualizar claves primarias es algo poco habitual.

Qué significa "foreign key constraint failed" en SQLite

Este error aparece en dos situaciones. La primera: al insertar o actualizar un hijo con un valor que no tiene padre correspondiente:

sqlite> INSERT INTO posts (title, author_id) VALUES ('Stray', 999);
Runtime error: FOREIGN KEY constraint failed

O bien el autor 999 no existe, o cruzaste los tipos de columna. Inserta primero el padre, o corrige el valor.

Segundo, borrar (o actualizar) un padre que todavía tiene hijos, cuando la FK usa RESTRICT o NO ACTION:

sqlite> DELETE FROM authors WHERE id = 1;
Runtime error: FOREIGN KEY constraint failed

O bien borras primero los hijos, o cambias la FK a ON DELETE CASCADE/SET NULL si lo que realmente quieres es propagar la operación.

Existe también un primo menos famoso: FOREIGN KEY mismatch. Ese salta cuando la columna referenciada no es clave primaria ni UNIQUE, o cuando el número de columnas no cuadra. Es un error de esquema, no de datos.

Agregar claves foráneas a tablas existentes

El ALTER TABLE de SQLite es limitado: puedes añadir una columna nueva con una clave foránea, pero no puedes ponerle una FK a una columna que ya existe. El truco habitual es el clásico baile de renombrar y reconstruir:

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT NOT NULL,
    author_id INTEGER
);

INSERT INTO authors (id, name) VALUES (1, 'Ada');
INSERT INTO posts (title, author_id) VALUES ('Hola', 1), ('Mundo', 1);

PRAGMA foreign_keys = OFF;
BEGIN;

CREATE TABLE posts_new (
    id        INTEGER PRIMARY KEY,
    title     TEXT NOT NULL,
    author_id INTEGER REFERENCES authors(id) ON DELETE CASCADE
);

INSERT INTO posts_new SELECT id, title, author_id FROM posts;

DROP TABLE posts;
ALTER TABLE posts_new RENAME TO posts;

COMMIT;
PRAGMA foreign_keys = ON;

SELECT sql FROM sqlite_master WHERE name = 'posts';

El patrón es así: desactivas la validación, creas la tabla nueva con las restricciones que quieras, copias los datos, eliminas la tabla vieja y renombras. El BEGIN/COMMIT lo hace atómico. Al final reactivas la validación y SQLite revisará todas las filas existentes contra las nuevas restricciones — si hay datos inválidos, la transacción ya se confirmó, así que verifica antes si te preocupa.

Después de la migración, ejecuta PRAGMA foreign_key_check; para confirmar que no quedan filas huérfanas.

Un esquema realista

Juntando todo — un mini esquema de blog con padres, hijos y una tabla intermedia para etiquetas con relación muchos a muchos:

PRAGMA foreign_keys = ON;

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

CREATE TABLE posts (
    id         INTEGER PRIMARY KEY,
    author_id  INTEGER NOT NULL REFERENCES authors(id) ON DELETE CASCADE,
    title      TEXT NOT NULL,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE tags (
    id   INTEGER PRIMARY KEY,
    name TEXT UNIQUE NOT NULL
);

CREATE TABLE post_tags (
    post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    tag_id  INTEGER NOT NULL REFERENCES tags(id)  ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

Tres cosas que conviene notar. author_id es NOT NULL — toda publicación debe tener un autor. La FK de posts → authors hace cascada, así que al borrar un autor desaparecen sus publicaciones. La tabla intermedia post_tags cascada por ambos lados, de modo que al eliminar una publicación o una etiqueta, las filas de enlace se limpian solas.

Buenas costumbres que te ahorran dolores de cabeza

Ejecuta PRAGMA foreign_keys = ON; en cada conexión. Que forme parte de la rutina de apertura de tu base de datos, no algo que tengas que recordar. Así te aseguras de activar las claves foráneas en SQLite siempre.
Añade un índice en la columna FK. SQLite indexa automáticamente la clave del padre, pero no la del hijo, y ON DELETE CASCADE hace una búsqueda en el hijo cada vez que se borra el padre.
Elige ON DELETE con criterio. El valor por defecto (NO ACTION) es seguro, pero significa que vas a chocar con "foreign key constraint failed" cada vez que intentes limpiar datos. Decide qué debe pasar y decláralo.
Lanza PRAGMA foreign_key_check; después de migraciones o cargas masivas para detectar registros huérfanos antes de que se conviertan en bugs.

Siguiente paso: INNER JOIN

Las claves foráneas describen la relación; los joins son la forma de consultarla en la práctica. La siguiente página cubre INNER JOIN — cómo combinar filas de tablas relacionadas y obtener las columnas que te interesan de cada una.

Preguntas frecuentes

¿Cómo se crea una clave foránea en SQLite?

Basta con añadir una cláusula REFERENCES otra_tabla(columna) a la definición de la columna dentro del CREATE TABLE. Por ejemplo, author_id INTEGER REFERENCES authors(id) hace que author_id apunte a una fila de authors. Eso sí, la columna referenciada debe ser PRIMARY KEY o tener una restricción UNIQUE.

¿Por qué SQLite no valida mis claves foráneas?

SQLite reconoce la sintaxis de las claves foráneas, pero no las aplica salvo que lo actives explícitamente. Tienes que ejecutar PRAGMA foreign_keys = ON; al inicio de cada conexión. Es una configuración por conexión, no se guarda en el archivo de la base de datos, así que tanto las librerías como la CLI necesitan activarlo cada vez que conectan.

¿Qué hace ON DELETE CASCADE en SQLite?

ON DELETE CASCADE le dice a SQLite que borre automáticamente las filas hijas cuando se elimine la fila padre. Las otras opciones son RESTRICT (bloquea el borrado), SET NULL (deja la columna FK a NULL), SET DEFAULT y NO ACTION (la opción por defecto, en la práctica equivale a RESTRICT). Elige una u otra según si las filas hijas tienen sentido sin su padre.

¿Cómo soluciono el error 'foreign key constraint failed' en SQLite?

Ese error significa que has intentado insertar o actualizar una fila cuya clave foránea no coincide con ninguna fila de la tabla referenciada, o que has intentado borrar un padre que aún tiene hijos. Comprueba primero que la fila referenciada existe, o configura ON DELETE CASCADE si quieres que los hijos se eliminen automáticamente.

Conceptos relacionados

Claves foráneas en SQLite: REFERENCES y PRAGMA