On Delete Cascade

Cuando estamos tratando con bases de datos relacionales, estamos implementando una robustez en el tratamiento de los datos que queremos guardar. Pero la gestión de este modelo puede volverse complicado si no conocemos en profundidad este concepto relacional y a la hora de desarrollar podemos perder mucho tiempo creando soluciones muy parciales en lugar de utilizar algunas características que nos ofrece este modelo. En este caso concreto vamos a hablar sobre la opción ON DELETE CASCADE / ON  UPDATE CASCADE.

Esta es quizás una de las opciones más utiles y peor explicadas en cualquier tutorial o página oficial de bases de datos. Realmente no es tan complicado pero vamos a realizar varias pruebas para entenderlo y llevarlo a cabo.

Vamos a crear un ejemplo de base de datos y veremos las características de estas opciones. Para empezar se van a crear 3 tablas (CLIENTES, CUENTAS y COMPRAS). Estas 3 tablas irán relacionadas entre sí con una relación 1:n entre CLIENTES y CUENTAS y una relación 1:n entre CUENTAS y COMPRAS. Es decir, existe una persona que puede tener abierta una cuenta de banco con unas características concretas y con la que realizará unas compras; pero puede también tener varias  cuentas y cuando no le cojan una tarjeta poder pagar con la otra.

El modelo se visualizaría de la siguiente manera:

En este primer ejercicio no se van a aplicar las opciones CASCADE para visualizar el error que nos devolverá cuando queramos manipular la información. Por lo que ahora solo ejecutaremos el siguiente código SQL para crear las tablas descritas por el modelo anterior.

-- -----------------------------------------------------
-- Schema CASCADE
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `CASCADE` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `CASCADE` ;

-- -----------------------------------------------------
-- Table `CASCADE`.`CLIENTES`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `CASCADE`.`CLIENTES` ;

CREATE TABLE IF NOT EXISTS `CASCADE`.`CLIENTES` (
  `dni` INT NOT NULL COMMENT '',
  `nombre` VARCHAR(45) NOT NULL COMMENT '',
  PRIMARY KEY (`dni`)  COMMENT '')
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `CASCADE`.`CUENTAS`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `CASCADE`.`CUENTAS` ;

CREATE TABLE IF NOT EXISTS `CASCADE`.`CUENTAS` (
  `id_cuenta` INT NOT NULL COMMENT '',
  `saldo` FLOAT NOT NULL COMMENT '',
  `CLIENTES_dni` INT NOT NULL COMMENT '',
  PRIMARY KEY (`id_cuenta`)  COMMENT '',
  INDEX `fk_CUENTAS_CLIENTES1_idx` (`CLIENTES_dni` ASC)  COMMENT '',
  CONSTRAINT `fk_CUENTAS_CLIENTES1`
    FOREIGN KEY (`CLIENTES_dni`)
    REFERENCES `CASCADE`.`CLIENTES` (`dni`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `CASCADE`.`COMPRAS`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `CASCADE`.`COMPRAS` ;

CREATE TABLE IF NOT EXISTS `CASCADE`.`COMPRAS` (
  `idcompra` INT NOT NULL COMMENT '',
  `articulo` VARCHAR(45) NOT NULL COMMENT '',
  `precio` FLOAT NOT NULL COMMENT '',
  `CUENTAS_id_cuenta` INT NOT NULL COMMENT '',
  PRIMARY KEY (`idcompra`)  COMMENT '',
  INDEX `fk_COMPRAS_CUENTAS_idx` (`CUENTAS_id_cuenta` ASC)  COMMENT '',
  CONSTRAINT `fk_COMPRAS_CUENTAS`
    FOREIGN KEY (`CUENTAS_id_cuenta`)
    REFERENCES `CASCADE`.`CUENTAS` (`id_cuenta`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

Este código está generado con MySQL Workbench y se puede ver que la opción por defecto ON DELETE y ON UPDATE es NO ACTION. Esto es importante, porque más adelante cambiaremos estos campos para resolver nuestro problema.
Ahora procederemos a insertar algunos datos de prueba en las tablas y observar el comportamiento de algunas acciones.

-- -----------------------------------------------------
-- Data for table `CASCADE`.`CLIENTES`
-- -----------------------------------------------------

INSERT INTO `CASCADE`.`CLIENTES` (`dni`, `nombre`) VALUES (123456789, 'John Carter');
INSERT INTO `CASCADE`.`CLIENTES` (`dni`, `nombre`) VALUES (987654321, 'JohnQ');
INSERT INTO `CASCADE`.`CLIENTES` (`dni`, `nombre`) VALUES (193749372, 'John Doe');

-- -----------------------------------------------------
-- Data for table `CASCADE`.`CUENTAS`
-- -----------------------------------------------------

INSERT INTO `CASCADE`.`CUENTAS` (`id_cuenta`, `saldo`, `CLIENTES_dni`) VALUES (10, 1000, 123456789);
INSERT INTO `CASCADE`.`CUENTAS` (`id_cuenta`, `saldo`, `CLIENTES_dni`) VALUES (11, 0, 123456789);
INSERT INTO `CASCADE`.`CUENTAS` (`id_cuenta`, `saldo`, `CLIENTES_dni`) VALUES (12, 20, 123456789);
INSERT INTO `CASCADE`.`CUENTAS` (`id_cuenta`, `saldo`, `CLIENTES_dni`) VALUES (20, 40.23, 987654321);
INSERT INTO `CASCADE`.`CUENTAS` (`id_cuenta`, `saldo`, `CLIENTES_dni`) VALUES (21, 2350.48, 987654321);
INSERT INTO `CASCADE`.`CUENTAS` (`id_cuenta`, `saldo`, `CLIENTES_dni`) VALUES (30, 12000000, 193749372);
INSERT INTO `CASCADE`.`CUENTAS` (`id_cuenta`, `saldo`, `CLIENTES_dni`) VALUES (31, 999999999999, 193749372);

-- -----------------------------------------------------
-- Data for table `CASCADE`.`COMPRAS`
-- -----------------------------------------------------

INSERT INTO `CASCADE`.`COMPRAS` (`idcompra`, `articulo`, `precio`, `CUENTAS_id_cuenta`) VALUES (1, 'Barra de pan', 1, 10);
INSERT INTO `CASCADE`.`COMPRAS` (`idcompra`, `articulo`, `precio`, `CUENTAS_id_cuenta`) VALUES (2, 'Cuota del gimnasio', 30, 10);
INSERT INTO `CASCADE`.`COMPRAS` (`idcompra`, `articulo`, `precio`, `CUENTAS_id_cuenta`) VALUES (3, 'Caja de cervezas 12 latas', 5, 10);
INSERT INTO `CASCADE`.`COMPRAS` (`idcompra`, `articulo`, `precio`, `CUENTAS_id_cuenta`) VALUES (4, 'Calzoncillos Boxer', 3, 12);
INSERT INTO `CASCADE`.`COMPRAS` (`idcompra`, `articulo`, `precio`, `CUENTAS_id_cuenta`) VALUES (5, 'Pack DVD Juego de tronos', 36, 20);
INSERT INTO `CASCADE`.`COMPRAS` (`idcompra`, `articulo`, `precio`, `CUENTAS_id_cuenta`) VALUES (6, 'Lavadora', 300, 21);
INSERT INTO `CASCADE`.`COMPRAS` (`idcompra`, `articulo`, `precio`, `CUENTAS_id_cuenta`) VALUES (7, 'Lavavajillas', 460, 21);
INSERT INTO `CASCADE`.`COMPRAS` (`idcompra`, `articulo`, `precio`, `CUENTAS_id_cuenta`) VALUES (8, 'Jacuzzi de Ikea', 3200, 30);
INSERT INTO `CASCADE`.`COMPRAS` (`idcompra`, `articulo`, `precio`, `CUENTAS_id_cuenta`) VALUES (9, 'Boeing 747', 17000000, 30);
INSERT INTO `CASCADE`.`COMPRAS` (`idcompra`, `articulo`, `precio`, `CUENTAS_id_cuenta`) VALUES (10, 'Servicio de compañia de lujo', 1350, 30);
INSERT INTO `CASCADE`.`COMPRAS` (`idcompra`, `articulo`, `precio`, `CUENTAS_id_cuenta`) VALUES (12, 'Empresa Google', 173652000000, 31);
INSERT INTO `CASCADE`.`COMPRAS` (`idcompra`, `articulo`, `precio`, `CUENTAS_id_cuenta`) VALUES (11, 'Asesino a sueldo', 3450, 30);
INSERT INTO `CASCADE`.`COMPRAS` (`idcompra`, `articulo`, `precio`, `CUENTAS_id_cuenta`) VALUES (13, 'Empresa Apple', 246992000000, 31);
INSERT INTO `CASCADE`.`COMPRAS` (`idcompra`, `articulo`, `precio`, `CUENTAS_id_cuenta`) VALUES (14, 'Empresa Microsoft', 115500000000, 31);

 

Una vez hecho esto, vamos a realizar una cosa tan sencilla, como eliminar un cliente cualquiera y descubriremos el siguiente error.

Cannot delete or update a parent row: a foreign key contraint fails

Este error aparece, debido a que se quiere eliminar un registro que tiene asociados un identificador en otras tablas. De manera que si solo eliminamos este registro, en las otras tablas existirían registros de un usuario inexistente y se perdería el modelo relacional.

Aunque pueda parecer un engorro, esto nos ayuda en un futuro a mantener una robustez que nos permite desarrollar aplicaciones más complejas y seguras. El objetivo final es no duplicar información entre tablas y que no haya ninguna fuga de consistencia que en un futuro pueda dar problemas.

Lo más cómodo sería que al borrar un usuario se borrarán también los campos asociados de otras tablas. De esta manera, deberemos cambiar nuestro modelo de base de datos al siguiente, solamente modificando la linea ON DELETE NO ACTION por ON DELETE CASCADE.

-- -----------------------------------------------------
-- Schema CASCADE
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `CASCADE` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `CASCADE` ;

-- -----------------------------------------------------
-- Table `CASCADE`.`CLIENTES`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `CASCADE`.`CLIENTES` ;

CREATE TABLE IF NOT EXISTS `CASCADE`.`CLIENTES` (
`dni` INT NOT NULL COMMENT '',
`nombre` VARCHAR(45) NOT NULL COMMENT '',
PRIMARY KEY (`dni`)  COMMENT '')
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `CASCADE`.`CUENTAS`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `CASCADE`.`CUENTAS` ;

CREATE TABLE IF NOT EXISTS `CASCADE`.`CUENTAS` (
`id_cuenta` INT NOT NULL COMMENT '',
`saldo` FLOAT NOT NULL COMMENT '',
`CLIENTES_dni` INT NOT NULL COMMENT '',
PRIMARY KEY (`id_cuenta`)  COMMENT '',
INDEX `fk_CUENTAS_CLIENTES1_idx` (`CLIENTES_dni` ASC)  COMMENT '',
CONSTRAINT `fk_CUENTAS_CLIENTES1`
FOREIGN KEY (`CLIENTES_dni`)
REFERENCES `CASCADE`.`CLIENTES` (`dni`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `CASCADE`.`COMPRAS`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `CASCADE`.`COMPRAS` ;

CREATE TABLE IF NOT EXISTS `CASCADE`.`COMPRAS` (
`idcompra` INT NOT NULL COMMENT '',
`articulo` VARCHAR(45) NOT NULL COMMENT '',
`precio` FLOAT NOT NULL COMMENT '',
`CUENTAS_id_cuenta` INT NOT NULL COMMENT '',
PRIMARY KEY (`idcompra`)  COMMENT '',
INDEX `fk_COMPRAS_CUENTAS_idx` (`CUENTAS_id_cuenta` ASC)  COMMENT '',
CONSTRAINT `fk_COMPRAS_CUENTAS`
FOREIGN KEY (`CUENTAS_id_cuenta`)
REFERENCES `CASCADE`.`CUENTAS` (`id_cuenta`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;

Introducimos los mismos registros que habiamos cargado anteriormente y probamos a eliminar un registro de clientes. Observaremos que se eliminarán todos los registros asociados a ese cliente.

Si actualizamos una tabla padre también observaremos que el resto de tablas se actualizan con ese identificador, por ejemplo:

<code class="sql"><span class="cm-keyword"><a class="cm-sql-doc" href="http://localhost/phpmyadmin/url.php?url=http%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.5%2Fen%2Fupdate.html&server=0&token=5aec5957455c9902941666c7e146e59f" target="mysql_doc">UPDATE</a></span> <span class="cm-variable-2">`cascade`</span>.<span class="cm-variable-2">`clientes`</span> <span class="cm-keyword"><a class="cm-sql-doc" href="http://localhost/phpmyadmin/url.php?url=http%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.5%2Fen%2Fset.html&server=0&token=5aec5957455c9902941666c7e146e59f" target="mysql_doc">SET</a></span> <span class="cm-variable-2">`dni`</span> = <span class="cm-string">'123456789'</span> <span class="cm-keyword">WHERE</span> <span class="cm-variable-2">`clientes`</span>.<span class="cm-variable-2">`dni`</span> = <span class="cm-number">1234567890</span>;</code>

DELETE FROM `cascade`.`cuentas` WHERE `cuentas`.`id_cuenta` = 10;

DELETE FROM `cascade`.`clientes` WHERE `clientes`.`dni` = 987654321

Desde MySQL Workbench se pueden gestionar estas acciones accediendo a las claves foráneas de la tabla.

Y desde PHPMyAdmin también se puede cambiar este comportamiento, aunque está un poco escondido. Primero hay que acceder a la estructura de la tabla y apretar un botóncito minúsculo que pone “vista de relaciones“.

Una vez dentro podremos escoger el comportamiento de las tablas en cascada.

Como se puede ver hay distintas opciones que nos faltarían por explicar que son SET NULL y RESTRICT que para el caso no sirven de mucho. SET NULL deja espacios vacios en lugar de eliminar o actualizar por completo los campos. Y RESTRICT es equivalente a no poner nada.

Desde la página oficial de documentación MySQL se puede ver una explicación detallada.