Integridad referencial

Como nos hemos enfocado en la consulta de datos vamos a cambiar de contexto. Si recordamos una de las cuatro tareas generales de los registros es la de borrar o eliminar registros de una tabla.

La sintaxis para borrar registros es:

-- Sintaxis general

DELETE FROM tabla WHERE expresión;

-- Con expresión me refiero a que puede
-- existir una o muchas condiciones si 
-- se utiliza AND / OR. 
-- Una condición sería 
-- columna operador valor;

-- Sintaxis donde la expresión 
-- es una condición más específica

DELETE FROM tabla WHERE columna operador valor;

-- Ejemplo de borrado de un usuario

DELETE FROM usuarios WHERE id = 4;

En el ejemplo anterior hay una consulta donde se borra un usuario de una base de datos. Hasta ahí todo bien pero ¿qué pasa si el usuario tiene datos asociadas? Vuelvo a tomar la base de datos tutoriales como referencia – incluyo el esquema -.

Esquema de la Base de Datos - tutoriales -.

En este caso el usuario tiene publicaciones asociadas como podemos ver.

SELECT usuarios.id, usuarios.nombre, publicaciones.titulo
FROM usuarios, publicaciones
WHERE usuarios.id = publicaciones.usuario_id 
AND usuarios.id = 4;

Datos asociados

Si ejecutamos una consulta después de borrar el usuario vemos que las publicaciones siguen estando asociadas al usuario que tiene el id número 4 aunque el registro del usuario ya no existe en la base de datos.

-- Seleccionar el título y la columna usuario_id
-- de la tabla publicaciones y mostrar el id y el
-- nombre del usuario que tiene asociado. 

SELECT usuarios.id, publicaciones.usuario_id, 
usuarios.nombre, publicaciones.titulo
FROM usuarios RIGHT JOIN publicaciones
ON usuarios.id = publicaciones.usuario_id
WHERE publicaciones.usuario_id = 4;

Inconsistencia en la base de datos

Lo más probable es que no queramos que suceda este comportamiento. Entonces ¿qué hacemos?

¿Borramos las publicaciones al borrar el usuario? ¿asignamos el valor NULL en la columna usuario_id de la tabla publicaciones? ¿Detenemos la consulta para asegurarnos de que no haya publicaciones asociadas antes de borrar al usuario?

Podemos hacer cualquiera de las opciones pero antes vamos a ver qué es – de manera general – la integridad referencial y cómo puedes implementarla en tus bases de datos.

Integridad Referencial

«La integridad referencial es un concepto de bases de datos que asegura que la relación entre las tablas permanezca consistente. Cuando una tabla tiene un llave foránea a otra tabla, el concepto de integridad referencial afirma que no puedes agregar un registro a la tabla que contiene la llave foránea a menos que exista un registro correspondiente en la tabla vinculada.» – vía databases.about.com

Para implementar este concepto en nuestras bases de datos es necesario modificar las tablas para que tengan ciertas restricciones en columnas específicas.

La sintaxis para agregar restricciones a las columnas es:

ALTER TABLE tabla
ADD CONSTRAINT nombre_restriccion
FOREIGN KEY (columna) REFERENCES tabla (columna)
ON DELETE tipo_restriccion
ON UPDATE tipo_restriccion;

Las restricciones que podemos utilizar son las siguientes:
RESTRICT – NO ACTION – , CASCADE, SET NULL.

RESTRICT / NO ACTION
No realiza la acción.

Si quieres borrar un registro de la tabla a la cuál hace referencia la restricción, puedes elegir una de las dos opciones siguientes:

  1. Eliminar los registros de la tabla que tiene la restricción.
  2. Asociar los registros de la tabla que tiene la restricción con otros registros de la tabla a la cuál hace referencia.

Esto lo tienes que hacer ANTES de poderlo borrar.

Si quieres crear o actualizar un registro en la tabla que tiene la restricción – o restricciones – revisa que exista un registro en la(s) tabla(s) con la(s) cual(es) está asociada.

Regresando al ejemplo de los usuarios con las publicaciones:

Si quieres borrar un usuario puedes elegir una de las dos opciones siguientes:

  1. Borrar las publicaciones asociadas con el usuario y después borras el usuario.
  2. Asociar las publicaciones a otro usuario – actualizar el valor de la llave foránea con el valor de una llave primaria existente en la tabla relacionada – y después borras el usuario.

Si vas a asociar las publicaciones a otro usuario – actualizar -verifica que el valor que utilices en la llave foránea sea válido – que exista el registro en la tabla de usuarios con el valor como llave primaria -.

Si quieres crear una publicación verifica que el valor que utilices en la llave foránea sea válido – que exista el registro en la tabla de usuarios con el valor como llave primaria -.

«NO ACTION: Una plabra clave de SQL estándar. En MySQL es equivalente a RESTRICT» – MySQL.

-- Modificar la tabla publicaciones
-- para que se comporte diferente
-- si un usuario se borra. 

-- RESTRICT

ALTER TABLE publicaciones
ADD CONSTRAINT restrict_usuario_id
FOREIGN KEY (usuario_id) REFERENCES usuarios (id)
ON DELETE RESTRICT
ON UPDATE RESTRICT;

-- NO ACTION

ALTER TABLE publicaciones
ADD CONSTRAINT no_action_usuario_id
FOREIGN KEY (usuario_id) REFERENCES usuarios (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION;

Si queremos borrar el usuario con el id = 4 después de agregar este tipo de restricción a la tabla publicaciones esto es lo que veríamos.

Integridad referencial restrict no action

Si queremos modificar el valor que tiene la columna usuario_id en las publicaciones que tienen el valor 4 pero el valor que queremos asignar «-1000» no existe – no hay un usuario en la tabla de usuarios con el id -1000 – esto es lo que sucede.

Actualizar - Integridad referencial restrict no action

Si queremos crear una publicación asignando el valor -1000″ a la columna usuario_id aún sabiendo que el usuario no existe – no hay un usuario en la tabla de usuarios con el id -1000 – esto es lo que sucede.

Crear - Integridad referencial restrict no action

CASCADE
Borra los registros asociados al borrar el registro principal.

-- Modificar la tabla publicaciones
-- para que se comporte diferente
-- si un usuario se borra. 

-- CASCADE 

ALTER TABLE publicaciones
ADD CONSTRAINT cascade_usuario_id
FOREIGN KEY (usuario_id) REFERENCES usuarios (id)
ON DELETE CASCADE
ON UPDATE CASCADE;

En este caso hay que tener más cuidado. Regresando al ejemplo, si borramos el usuario con el id = 4 se borran todas las publicaciones que están asociadas con este usuario.

Borrar en cascada - Integridad referencial

Al igual que con RESTRICT – NO ACTION – esta restricción no permite actualizar el valor de una llave foránea por el valor de una llave primaria que no existe.

Crear registro - integridad referencial - cascade

CASCADE tampoco permite crear una publicación con un usuario que no existe.

Actualizar registro - integridad referencial - cascade

SET NULL
Al eliminar el registro, sustituye el valor de la llave foránea asociada por NULL.

Si se elige esta opción hay que verificar que la columna de la llave foránea permita NULL.

-- Modificar la tabla publicaciones
-- para que se comporte diferente
-- si un usuario se borra. 

-- SET NULL

ALTER TABLE publicaciones
ADD CONSTRAINT null_usuario_id
FOREIGN KEY (usuario_id) REFERENCES usuarios (id)
ON DELETE SET NULL
ON UPDATE SET NULL;

Al borrar el usuario con el id = 4 podemos ver que las publicaciones que tenía asociadas siguen almacenadas en la base de datos pero con el valor NULL en la columna de la llave foránea.

Integridad referencial SET NULL

Al igual que las restricciones anteriores, SET NULL no permite actualizar el valor de una llave foránea por el valor de una llave primaria que no existe.

INSERT - Integridad referencial - SET NULL

SET NULL tampoco permite crear una publicación con un usuario que no existe.

UPDATE - Integridad referencial - SET NULL

Recordar:
En orden de riesgo las restricciones son:

CASCADE: Borra los registros asociados.
SET NULL: Asigna NULL a la llave foránea.
RESTRICT – NO ACTION -: Detiene la consulta. El proceso – borrado, asociar con otro registro o dejar como NULL la llave foránea – se tiene que hacer manual.

Una misma llave foránea puede tener una restricción distinta si se actualiza o se borra el registro asociado.
Se puede tener una restricción por cada llave foránea que se tenga en la tabla.

-- Agregar restricciones distintas en la tabla publicaciones
-- Al borrar un usuario se borran las publicaciones en cascada
-- Si el usuario actualiza su id 
-- se asigna NULL a la llave foránea en publicaciones 
-- Al borrar un nivel se borran las publicaciones en cascada
-- Si se modifica el id del nivel
-- se asigna NULL a la llave foránea en publicaciones 

ALTER TABLE publicaciones
ADD CONSTRAINT cascade_usuario_id
FOREIGN KEY (usuario_id) REFERENCES usuarios (id)
ON DELETE CASCADE
ON UPDATE SET NULL,
ADD CONSTRAINT cascade_nivel_id
FOREIGN KEY (nivel_id) REFERENCES niveles (id)
ON DELETE CASCADE
ON UPDATE SET NULL;

Nota:
Para poder utilizar las restricciones – de claves foráneas – es necesario:

Revisar que las tablas utilicen el motor de almacenamiento InnoDB.

En caso de tener datos en las tablas no dejar las llaves foráneas sin asignar – no pueden ser NULL a menos que se quiera utilizar la restricción SET NULL -.

Para finalizar:

Si todavía no tienes una base de datos hecha considera utilizar alguna de estas restricciones para tu próximo proyecto – aún cuando le pienses dejar la tarea al lenguaje de programación o framework que utilices para desarrollar tu aplicación – .

Si ya tienes una base de datos hecha y no definiste este comportamiento, expórtala, realiza las restricciones que creas convenientes en la copia, prueba como se comporta y si te funciona, implementa las restricciones en tu base de datos de producción – recuerda respaldar antes de modificar tus bases de datos -.