Restricciones de clave foránea y acciones referenciales (ON DELETE / ON UPDATE)

Los datos que vamos a usar — category e item

Una restricción de clave foránea (la restricción que garantiza que el valor de una columna existe siempre como clave primaria en otra tabla) es el mecanismo que protege la integridad referencial entre tablas (el estado en el que cada fila referenciada existe realmente).

Bloquea contradicciones como "un item apunta a una category inexistente" o "eliminar una category deja a sus items colgando", en la capa de base de datos.

Antes de los ejercicios, échale un vistazo a las definiciones de columnas y a los datos de ejemplo de las dos tablas — category e item.

① Ejecuta PRAGMA table_info(category); y PRAGMA table_info(item); para comprobar las definiciones de columnas.

② Previsualiza todas las filas con SELECT * FROM category; y SELECT * FROM item;.

③ Ejecuta PRAGMA foreign_keys; para ver el estado actual de la comprobación de claves foráneas (devuelto como un número).

La comprobación de claves foráneas se activa con PRAGMA foreign_keys=ON

Las restricciones de clave foránea se declaran escribiendo REFERENCES tabla_padre(col_padre) en la definición de columna de la tabla hija.

item.cat_id lleva REFERENCES category(cat_id), que codifica la regla: cada valor de item.cat_id debe existir en category.

Sin embargo, en la consola de este curso la comprobación de claves foráneas está por defecto desactivada (PRAGMA foreign_keys es 0).

Mientras está desactivada, `REFERENCES` no se aplica realmente y se pueden insertar filas que apuntan a una category inexistente.

Para aplicar realmente la restricción, ejecuta PRAGMA foreign_keys=ON; una vez por conexión.

-- Activa la comprobación de claves foráneas
PRAGMA foreign_keys=ON;

-- Confirma el estado (1 significa activada)
PRAGMA foreign_keys;

-- Intenta insertar una fila que apunta a cat_id=99 (no está en category)
-- Con foreign_keys=ON, esto se rechaza con FOREIGN KEY constraint failed
INSERT INTO item VALUES (5,'Mouse',99);

Imagina el requisito: "que la base de datos bloquee los errores de registro en los que un item referencia una category inexistente". Con la comprobación de claves foráneas activada, insertarás a propósito una fila inconsistente y verás cómo da error. (Ejecútalo correctamente y aparecerá la explicación.)

① En la línea 1, ejecuta PRAGMA foreign_keys=ON; para activar la comprobación de claves foráneas.

② Luego INSERT una fila en item con item_id 5, item_name Mouse y cat_id 99 — un valor que no existe en category. Este INSERT viola la restricción de clave foránea, así que un error es el resultado correcto.

ON DELETE — qué hacer con las filas hijas cuando se elimina una fila padre

Cuando se elimina una fila padre (category), ¿qué debe pasar con las filas hijas (item) que apuntaban a ella? Lo decides escribiendo una acción referencial en la cláusula REFERENCES.

Hay tres acciones referenciales habituales.

ON DELETE CASCADE significa que eliminar el padre también elimina a los hijos (borrado en cascada), ON DELETE SET NULL significa que la columna de referencia del hijo se pone a NULL y la fila se conserva, y ON DELETE RESTRICT significa que se rechaza el propio borrado del padre mientras los hijos aún lo referencian.

Si no especificas ninguna acción, el valor por defecto también es similar a RESTRICT (un padre referenciado no puede eliminarse).

Qué comportamiento es apropiado depende del requisito de negocio.

En el siguiente ejercicio configurarás SET NULL y RESTRICT lado a lado en una sola consola para compararlos.

-- Ejemplo de declaración con ON DELETE SET NULL
CREATE TABLE cat_demo(cat_id INTEGER PRIMARY KEY, cat_name TEXT NOT NULL);
CREATE TABLE item_demo(
  item_id INTEGER PRIMARY KEY,
  item_name TEXT NOT NULL,
  cat_id INTEGER REFERENCES cat_demo(cat_id) ON DELETE SET NULL
);
INSERT INTO cat_demo VALUES (1,'Stationery'),(2,'Electronics');
INSERT INTO item_demo VALUES (1,'Pen',1),(2,'Phone',2),(3,'Cable',2);

-- Eliminar el padre cat_id=2 pone a NULL el cat_id de Phone / Cable
DELETE FROM cat_demo WHERE cat_id=2;
SELECT item_id, item_name, cat_id FROM item_demo ORDER BY item_id;

Imagina el requisito: "cuando se elimina una category, elimina también todos los items que le pertenecen". Construye un padre/hijo autocontenido con ON DELETE CASCADE y observa cómo el borrado del padre se propaga en cascada a los hijos.

① Activa la comprobación de claves foráneas, luego DROP TABLE IF EXISTS y recrea cat_x (cat_id, cat_name) e item_x (item_id, item_name, cat_id) donde item_x.cat_id tiene una clave foránea ON DELETE CASCADE hacia cat_x.

② Inserta 2 filas en cat_x (Stationery / Electronics) y 3 filas en item_x (una con cat_id 1, dos con cat_id 2).

③ Elimina la category con cat_id 2 de cat_x, luego SELECT todas las filas de item_x y confirma que los items que apuntaban a cat_id=2 se eliminaron en cascada.

SET NULL y RESTRICT — elige entre conservar y rechazar

ON DELETE SET NULL conserva la fila hija cuando se elimina el padre, simplemente reemplazando la columna de referencia con NULL.

Úsalo cuando no quieras eliminar el registro hijo en sí, como "conserva el item como 'sin categoría'".

Si la columna de referencia del hijo es NOT NULL, no se puede escribir NULL, así que las columnas usadas con SET NULL deben permitir NULL.

ON DELETE RESTRICT rechaza el propio borrado del padre mientras los hijos aún lo referencian.

Úsalo cuando quieras fallar de forma segura (no tirar accidentalmente una fila maestra que aún está referenciada).

Una vez que has eliminado o reasignado todos los hijos para que no queden referencias, el padre puede eliminarse.

En el siguiente ejercicio configurarás SET NULL y RESTRICT lado a lado en una sola consola para compararlos.

-- Ejemplo de RESTRICT: un padre con hijos no puede eliminarse
CREATE TABLE cat_r(cat_id INTEGER PRIMARY KEY, cat_name TEXT NOT NULL);
CREATE TABLE item_r(
  item_id INTEGER PRIMARY KEY,
  item_name TEXT NOT NULL,
  cat_id INTEGER REFERENCES cat_r(cat_id) ON DELETE RESTRICT
);
INSERT INTO cat_r VALUES (1,'Stationery'),(2,'Electronics');
INSERT INTO item_r VALUES (1,'Pen',1);

-- cat_id=1 está referenciado por item_r, así que se rechaza el borrado
DELETE FROM cat_r WHERE cat_id=1;

-- cat_id=2 no tiene referencias, así que se puede eliminar
DELETE FROM cat_r WHERE cat_id=2;
SELECT * FROM cat_r ORDER BY cat_id;

Imagina el requisito: "si descatalogamos una category, conserva sus items como 'sin categoría' pero no toques en absoluto los items de las otras categorías". Con 3 categorías y 5 items bajo ON DELETE SET NULL, verás que solo los hijos del padre descatalogado pasan a NULL mientras que los demás quedan intactos.

① Activa la comprobación de claves foráneas, luego DROP TABLE IF EXISTS y recrea cat_s (cat_id, cat_name) e item_s (item_id, item_name, cat_id) donde item_s.cat_id tiene una clave foránea ON DELETE SET NULL hacia cat_s. item_s.cat_id debe permitir NULL.

② Inserta 3 filas en cat_s (Office=1 / Gadget=2 / Drink=3) y 5 filas en item_s (Marker y Tape en Office, Tablet y Charger en Gadget, Coffee en Drink).

③ Elimina solo Gadget (cat_id=2) de cat_s, luego SELECT todas las filas de item_s y confirma que solo el cat_id de Tablet y Charger pasó a NULL mientras que los items bajo Office / Drink conservan su cat_id original — las 5 filas siguen ahí.

Imagina el requisito: "evita el borrado accidental de una category que aún tiene muchos items perteneciendo a ella". Construye un padre/hijo autocontenido con ON DELETE RESTRICT y observa cómo da error cuando intentas eliminar un padre que tiene 3 hijos.

① Activa la comprobación de claves foráneas, luego DROP TABLE IF EXISTS y recrea cat_t (cat_id, cat_name) e item_t (item_id, item_name, cat_id) donde item_t.cat_id tiene una clave foránea ON DELETE RESTRICT hacia cat_t.

② Inserta Stationery (cat_id 1) en cat_t, y 3 items bajo ella (p. ej. Pen / Pencil / Notebook) en item_t.

③ Intenta DELETE la category con cat_id 1 de cat_t. Como 3 hijos siguen referenciándola, un error es el resultado correcto.