Constraints & Cascading en MySQL

Administrar la integridad de los datos a través del código de la aplicación le añade complejidad innecesaria al código, además de tener sus riesgos. A pesar de ser cuidadosos, bajo algunas condiciones podemos llegar quebrar la integridad referencial.

Un ejemplo clásico es el de registros "huérfanos". Esto ocurre cuando eliminas de una tabla "padre" un registro y por alguna razón los "hijos" no son eliminados. Esto también puede ocurrir si actualizas el valor de un campo de la tabla "padre" y no actualizas los registros "hijos".

Estos problemas de integridad referencial son tratados por las bases de datos con CONSTRAINTs (restricciones), más específicamente FOREIGN KEY CONTRAINTs.

Con MySQL 5, utilizando el motor de almacenamiento InnoDB no debes preocuparte por controlar la integridad referencial de los datos, pues la base de datos lo hará por ti (así es como debe ser).

Así, si tienes que realizar un sitio con cuentas de usuarios y órdenes de compras, tienes dos tablas como las siguientes:

CREATE TABLE usuarios (
id_usuario BIGINT(22) NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
nombre VARCHAR(50) NOT NULL,
...
PRIMARY KEY (id_usuario)
) ENGINE=INNODB;

CREATE TABLE ordenes (
id_orden BIGINT(22) NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
id_usuario BIGINT(22) NOT NULL DEFAULT '0' COMMENT 'Foreign Key',
fecha TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
...
PRIMARY KEY (id_orden),
KEY `key_id_usuario` (`id_usuario`)
) ENGINE=INNODB;

Se ve claramente que tienes una relación de uno a mucho entre la tabla usuarios (como tabla "padre") y la tabla ordenes (como tabla "hijo"). En este punto es cuando puedes agregar datos "huérfanos" (insertar en la tabla ordenes a usuarios que no existen) y no recibir ningún tipo de error ni advertencia. Para solucionar esto, debes agregar una FOREIGN KEY CONTRAINT:

ALTER TABLE `ordenes`
ADD CONSTRAINT `ordenes_constraint`
FOREIGN KEY (`id_usuario`)
REFERENCES `usuarios` (`id_usuario`)
ON DELETE CASCADE
ON UPDATE CASCADE;

La constraint previene el ingreso de datos "huérfanos":

INSERT INTO ordenes SET id_usuario = -1;
-- Error Code : 1452
-- Cannot add or update a child row: a foreign key constraint fails (`sistrenes/ordenes`, CONSTRAINT `ordenes_constraint` FOREIGN KEY (`id_usuario`) REFERENCES `usuarios` (`id_usuario`) ON DELETE CASCADE ON UPDATE CASCADE)

Lo mismo, si agregas a un registro "padre" algunos "hijos", mira lo que ocurre en la tabla "hijo":

INSERT INTO usuarios (id_usuario,nombre) VALUES (1,'Eustaquio')

INSERT INTO ordenes (id_orden,id_usuario) VALUES (1,1);
INSERT INTO ordenes (id_orden,id_usuario) VALUES (2,1);
INSERT INTO ordenes (id_orden,id_usuario) VALUES (3,1);

DELETE FROM usuarios WHERE id_usuario = 1;
-- (1 row(s)affected)
SELECT * FROM ordenes;
-- (0 row(s)returned)

Vía: Parkside Web Development

Posted in: database,mysql,sql