Eliminación y modificación de datos teniendo en cuenta claves foráneas

Bueno, ya sabemos qué son las claves foráneas (FOREIGN KEY), cómo funcionan, e incluso hemos practicado creando tablas con ellas. Pero, ¿qué pasa cuando llega el momento de borrar datos o modificar registros relacionados? Ahora vamos a ver cómo funciona la eliminación y modificación de datos junto con las claves foráneas, y qué trucos hay aquí.

Imagina que la base de datos es como un castillo de naipes complicado. Si sacas una carta, te arriesgas a que todo se venga abajo. Aquí es donde entran en juego las claves foráneas, que evitan que "se destruya" nuestra base al eliminar datos relacionados. Vamos a ver cómo funciona esto.

¿Qué pasa si intentas eliminar datos?

Cuando hay una clave foránea en una tabla, el SGBD comprueba si el registro está relacionado con otras tablas. Si lo está, intentar eliminar ese registro puede provocar un error de integridad de datos. Para evitar sorpresas, puedes definir de antemano qué hacer con la clave foránea. Estas acciones se configuran con la opción ON DELETE.

Configurando el comportamiento con ON DELETE

Puedes elegir una de las siguientes reglas para la clave foránea al crearla:

1. ON DELETE CASCADE: Eliminar un registro en la tabla padre elimina automáticamente todos los registros relacionados en la tabla hija.
2. ON DELETE SET NULL: En vez de eliminar los registros relacionados, sus claves foráneas se ponen en NULL.
3. ON DELETE SET DEFAULT: El valor de la clave foránea se pone en el valor por defecto.
4. ON DELETE RESTRICT (comportamiento por defecto): No se puede eliminar el registro si hay registros relacionados, y se lanzará un error.
5. ON DELETE NO ACTION: Casi igual que RESTRICT, pero la comprobación de integridad se retrasa hasta que termina la transacción.

Ejemplo: Eliminación en cascada ON DELETE CASCADE

-- Tabla de clientes
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

-- Tabla de pedidos
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id) ON DELETE CASCADE,
    order_date DATE NOT NULL
);

-- Insertar datos
INSERT INTO customers (name) VALUES ('Iván Ivanov');
INSERT INTO orders (customer_id, order_date) VALUES (1, '2023-10-01');

-- Eliminar cliente
DELETE FROM customers WHERE customer_id = 1;

-- Comprobamos qué pasó con la tabla orders
SELECT * FROM orders; -- ¡No hay registros, se eliminaron en cascada!

Cuando eliminamos un registro de la tabla customers, PostgreSQL borra automáticamente todos los pedidos de ese cliente en la tabla orders.

Ejemplo: Poner referencias a NULL ON DELETE SET NULL

-- Tabla de pedidos con nueva regla de comportamiento
CREATE TABLE orders_with_null (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id) ON DELETE SET NULL,
    order_date DATE NOT NULL
);

-- Insertar datos
INSERT INTO orders_with_null (customer_id, order_date) VALUES (1, '2023-10-01');

-- Eliminamos cliente
DELETE FROM customers WHERE customer_id = 1;

-- Comprobamos la tabla orders_with_null
SELECT * FROM orders_with_null;

Resultado:

Con ON DELETE SET NULL podemos guardar los pedidos, pero "desvincularlos" del cliente que ya no existe.

Modificación de datos teniendo en cuenta claves foráneas

Aparte de eliminar, modificar datos en las tablas padre también puede afectar a los registros relacionados. Por ejemplo, ¿qué pasa si el cliente cambia su customer_id? Aquí entra en juego la opción ON UPDATE.

Configurando el comportamiento con ON UPDATE

Puedes gestionar los cambios en la tabla padre con estas estrategias:

1. ON UPDATE CASCADE: Cambiar el valor de la clave foránea en la tabla padre lo actualiza automáticamente en todos los registros relacionados.
2. ON UPDATE SET NULL: El valor de la clave foránea en las tablas hijas se pone en NULL.
3. ON UPDATE SET DEFAULT: Se pone el valor por defecto.
4. ON UPDATE RESTRICT: No se puede cambiar el valor de la clave foránea si hay registros relacionados.
5. ON UPDATE NO ACTION: La comprobación se retrasa hasta el final de la transacción.

Ejemplo: Actualización en cascada ON UPDATE CASCADE

CREATE TABLE customers_with_cascade (
    customer_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE orders_with_cascade (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers_with_cascade(customer_id) ON UPDATE CASCADE,
    order_date DATE NOT NULL
);

-- Insertar datos
INSERT INTO customers_with_cascade (name) VALUES ('Iván Ivanov');
INSERT INTO orders_with_cascade (customer_id, order_date) VALUES (1, '2023-10-01');

-- Cambiar customer_id
UPDATE customers_with_cascade SET customer_id = 100 WHERE customer_id = 1;

-- Comprobamos la tabla orders_with_cascade
SELECT * FROM orders_with_cascade;

Resultado:

Al cambiar el customer_id, PostgreSQL lo actualiza automáticamente en la tabla orders_with_cascade.

Práctica: Tabla enrollments

Vamos a recordar nuestro ejemplo con estudiantes students y cursos courses. Vamos a trabajar con la tabla de inscripciones a cursos enrollments y configurar la gestión de eliminación de datos.

-- Tabla de estudiantes
CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

-- Tabla de cursos
CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    title TEXT NOT NULL
);

-- Tabla intermedia de inscripciones
CREATE TABLE enrollments (
    student_id INT REFERENCES students(student_id) ON DELETE CASCADE,
    course_id INT REFERENCES courses(course_id) ON DELETE CASCADE,
    PRIMARY KEY (student_id, course_id)
);

-- Insertar datos
INSERT INTO students (name) VALUES ('Aleksei Petrov');
INSERT INTO courses (title) VALUES ('Matemáticas');
INSERT INTO enrollments (student_id, course_id) VALUES (1, 1);

-- Eliminamos estudiante
DELETE FROM students WHERE student_id = 1;

-- Comprobamos la tabla enrollments
SELECT * FROM enrollments; -- ¡Vacío! El registro fue eliminado automáticamente.

Errores típicos y cómo evitarlos

Uno de los errores más comunes es intentar eliminar un registro de la tabla padre sin configurar correctamente el comportamiento de las claves foráneas. Por ejemplo, si no pones nada para ON DELETE, por defecto se aplica RESTRICT, lo que dará un error.

También es importante recordar que abusar de las operaciones en cascada (CASCADE) puede tener consecuencias inesperadas. Por ejemplo, puedes borrar más datos de los que pensabas.

Para evitar estos problemas, sigue estos consejos:

• Piensa siempre bien el comportamiento de ON DELETE y ON UPDATE según la lógica de tu aplicación.
• Para operaciones importantes, añade consultas de comprobación antes de hacer cambios o eliminaciones.
• Usa transacciones para poder deshacer los cambios si hay algún error.