FOREIGN KEY ON DELETE: CASCADE, SET NULL y RESTRICT en la practica

Una clave foranea hace mas que marcar una relacion entre tablas: dicta que hace la base de datos con las filas hijas cuando borras el padre. Esa reaccion se llama accion referencial y se elige con la clausula ON DELETE. Si te equivocas, o recibes un error en cada borrado o pierdes datos en silencio.

Un esquema base y un enlace

Tomemos tres tablas conocidas. orders tiene una clave foranea a users, y employees se referencia a si misma mediante manager_id.

CREATE TABLE users (
    id         bigint PRIMARY KEY,
    email      text UNIQUE NOT NULL,
    name       text,
    country    text,
    created_at timestamptz DEFAULT now()
);

CREATE TABLE orders (
    id         bigint PRIMARY KEY,
    user_id    bigint NOT NULL,
    amount     numeric(12,2),
    status     text,
    created_at timestamptz DEFAULT now(),
    CONSTRAINT fk_orders_user
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

Ahora DELETE FROM users WHERE id = 7 elimina automaticamente todos los pedidos de ese usuario. Sin clausula ON DELETE la accion por defecto es NO ACTION: borrar un padre que todavia tiene hijos simplemente falla con un error.

Las cuatro acciones de borrado

• CASCADE borra las filas hijas junto con el padre. Ideal para datos estrictamente dependientes (lineas dentro de un pedido), peligroso para cualquier cosa con valor propio.
• SET NULL pone a nulo la referencia en la fila hija. La columna FK debe admitir nulos. Util cuando el hijo sobrevive al padre: borras el usuario pero conservas los pedidos con user_id = NULL.
• RESTRICT impide borrar el padre mientras exista alguna fila hija. La comprobacion se dispara de inmediato.
• NO ACTION es el mismo comportamiento por defecto, pero la comprobacion puede aplazarse al final de la transaccion en una restriccion deferrable.

-- orders keep living, the link is cleared
ALTER TABLE orders
    DROP CONSTRAINT fk_orders_user,
    ADD CONSTRAINT fk_orders_user
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL;

Tambien existe ON UPDATE: las mismas acciones se disparan cuando cambia la propia clave primaria del padre. En la practica casi nunca modificas una PK, asi que ON UPDATE CASCADE rara vez hace falta.

Cuando el cascade es peligroso

ON DELETE CASCADE resulta tentador porque "todo se limpia solo". Pero tambien borra datos sin confirmacion y sin rastro en la aplicacion.

-- one innocent-looking statement
DELETE FROM users WHERE country = 'RU';
-- and every order of every matching user is gone too

Trampa: los cascades se encadenan. Si orders tiene una hija order_items tambien con CASCADE, borrar un usuario arrasa tres niveles. A gran escala eso es una transaccion larga y bloqueante y un WAL inflado. Para datos financieros o de auditoria prefiere RESTRICT o un borrado logico (deleted_at), y deja el DELETE real para tareas de limpieza.

Indexar la columna de la clave foranea

PostgreSQL indexa automaticamente la clave primaria a la que apunta una FK, pero NO crea un indice sobre la columna que referencia. Sin el, cada borrado o actualizacion de un padre dispara un escaneo secuencial de la tabla hija para encontrar las filas dependientes.

CREATE INDEX idx_orders_user_id ON orders (user_id);

Esto importa sobre todo con CASCADE y SET NULL, porque la comprobacion de hijos corre en cada borrado del padre. La misma regla aplica a la tabla autorreferenciada:

CREATE INDEX idx_employees_manager ON employees (manager_id);

Restricciones aplazables y autorreferencias

A veces necesitas romper la integridad de forma temporal dentro de una transaccion, por ejemplo para intercambiar filas o cargar datos con referencias circulares. Para eso declaras la restriccion DEFERRABLE:

CREATE TABLE employees (
    id         bigint PRIMARY KEY,
    name       text,
    manager_id bigint,
    dept       text,
    salary     numeric(12,2),
    CONSTRAINT fk_emp_manager
        FOREIGN KEY (manager_id) REFERENCES employees(id)
        ON DELETE SET NULL
        DEFERRABLE INITIALLY DEFERRED
);

Con INITIALLY DEFERRED la comprobacion se pospone hasta el COMMIT, asi que puedes mantener referencias colgantes dentro de la transaccion. Recuerda las diferencias entre motores:

• MySQL (InnoDB) soporta CASCADE, SET NULL, RESTRICT y NO ACTION, pero no tiene DEFERRABLE: toda comprobacion es inmediata.
• ClickHouse no tiene claves foraneas en absoluto: la integridad es tarea de la aplicacion y las relaciones se modelan con JOIN.

La regla base: cascade para datos estrictamente dependientes, SET NULL para hijos que sobreviven al padre, RESTRICT para todo lo valioso, e indexa siempre la columna FK.