Integridad referencial

La integridad referencial se define como el conjunto de mecanismos para conservar y garantizar de forma automática la integridad de todos los datos de los sistemas de gestión de bases de datos. Define las reglas que claves primarias y claves foráneas deben cumplir.

Las tablas que forman una base de datos pueden ser de diferentes tipos, dependiendo de las relaciones que tengan con otras tablas.

Tabla referenciada: Su clave primaria es referenciada por una o más claves foráneas de la misma o diferente tabla

Tabla dependiente: Tiene al menos una clave foránea

Tabla independiente: No tiene ninguna clave primaria referenciada ni foránea

Tabla autorreferenciada: Su clave primaria está referenciada por una clave foránea definida en esta misma tabla

Al diseñar el modelo conceptual de una base de datos se definen las relaciones existentes entre las entidades. Durante el diseño a nivel físico del modelo relacional se definen físicamente las relaciones de forma que el gestor de bases de datos asegure la integridad de los datos de acuerdo a dichas relaciones. En estas relaciones se establecen las restricciones referenciales. Es decir, establecemos las acciones que el gestor debe tomar al efectuar las validaciones entre la clave primaria y la clave foránea referente como consecuencia de una operación "contra" la base de datos.

Las primeras restricciones que deberemos tener en cuenta son el requisito de que la clave primaria debe ser única y no puede contener valores nulos y que una clave foránea debe tener el mismo tipo de datos que la clave primaria a la que referencia.

Los sistemas gestores de bases de datos siguen una serie de reglas para soportar la integridad de los datos mediante la integridad referencial:

  • Insert
  • Delete
  • Update

Estas reglas se aplican al realizar las operaciones SQL con el mismo nombre. Dentro de cada una de las reglas existen unas opciones de integridad, impuestas por el SGBD en algunos casos:

  • Restrict
  • Set Null
  • Cascade

Estas operaciones se aplican en operaciones realizadas sobre claves primarias y foráneas. No todos los SGDB admiten todas las opciones en todas las reglas. MySQL emplea restrict sobre insert y update, y admite las tres en delete.

Opción RESTRICT

Con esta opción se impide que se propague la operación a otras tablas relacionadas con la afectada. Si la operación se aplica sobre:

  • La tabla referenciada: Esta opción sólo es aplicable a las operaciones (reglas de integridad) de actualización y borrado. No se permitirá actualizar o borrar una clave primaria que exista en la tabla dependiente como clave foránea.
  • La tabla dependiente: Esta opción sólo es aplicable a las operaciones (reglas de integridad) de actualización e inserción. Sólo se permitirá insertar o modificar una clave foránea si existe previamente en la tabla referenciada como clave primaria.

Opción SET NULL

Se aplica siempre sobre la clave foránea. Con esta opción se ponen a valor nulo los valores de las claves foráneas que coincidan con la clave primaria que va a ser modificada o borrada.

Opción CASCADE

Con esta opción se permite la propagación de la operación a las tablas relacionadas con la tabla afectada. Se aplica sobre las tablas dependientes, nunca sobre las tablas referenciadas. Esta opción se aplica sobre las operaciones (reglas de integridad) de actualización y borrado.

Cuando se actualiza o borra una fila de la tabla referenciada, se actualizan o borran de forma automática todas las filas de la tabla dependiente cuya clave foránea coincida con el valor de la clave primaria de la fila sobre la que se realizó la operación. Hay que tener "cuidado" con esta opción, ya que se pueden producir borrados masivos no deseados.

Regla INSERT

Sólo se aplica en las filas que se insertan en una tabla dependiente. Se aplica automáticamente, por lo que no se puede aplicar ninguna de las opciones de integridad. Cuando se inserta una fila en una tabla dependiente, la clave foránea debe ser:

  • Nula si está permitido en alguno de los atributos componentes de dicha clave
  • Igual a un valor de una clave primaria a la que referencia
  • Puede ser no única

Regla DELETE

En esta regla son aplicables todas las opciones de integridad vistas anteriormente. Se aplica en las operaciones de borrado efectuadas sobre una tabla referenciada. Al intentar borrar una fila de una tabla referenciada, se comprobarán todos las condiciones impuestas por la opción de integridad definida, y sólo se efectuará el borrado si dichas condiciones se cumplen. A continuación veremos las diferentes opciones que se aplican a esta regla.

Regla UPDATE

Se aplica en operaciones realizadas sobre tablas referenciadas y tablas dependientes. Si se trata de una tabla referenciada, se aplica automáticamente la opción RESTRICT, y no se puede aplicar ninguna de las otras opciones de integridad. Sin embargo, si se trata de una tabla dependiente, se pueden aplicar todas las opciones de integridad definidas.

DELETE RESTRICT

No se permite borrar una fila de una tabla referenciada si existen filas en una tabla dependiente de la anterior, cuya clave foránea coincida con el valor de la clave primaria de la fila que se quiere borrar.

Para poder realizar esta operación hay 2 opciones:

  • Borrar las filas dependientes y después la fila referenciada
  • Actualizar los valores de la clave foránea con otros diferentes a los de la clave primaria a borrar y después borra la fila referenciada

DELETE CASCADE

Al eliminar una fila de la tabla referenciada se produce un borrado en cadena en la tabla dependiente, es decir, se borran todas las filas dependientes (aquellas cuya clave foránea coincida con la clave primaria de la tabla a la que referencian). Hay que tener cuidado con esta opción ya que se pueden producir borrados masivos en las tablas implicadas.

DELETE SET NULL

Al borrar una fila de una tabla referenciada con una restricción referencial de este tipo se producen las siguientes acciones:

  • Borrado de la fila de la tabla referenciada
  • Se pone a nulo el valor de las claves foráneas de las filas dependientes

Una vez realizadas estas operaciones, las filas afectadas de la tabla dependiente no sufren validaciones de integridad hasta que el valor de su clave foránea se actualice con un valor que corresponda al de alguna clave primaria de la tabla referenciada.

Cuando una operación depende de varias restricciones referenciales falla si no se cumple alguna de ellas. Las opciones de integridad SET NULL y CASCADE no provocan fallo, pero RESTRICT sí que puede incumplirse. Por lo tanto, esta última opción es la que "decide" si la operación se realiza o no.

Nota: El campo

UPDATE RESTRICT

No se permite actualizar el valor de la clave primaria de una tabla referenciada mientras existan claves foráneas con el mismo valor en tablas dependientes.

Para realizar esta operación hay 2 opciones:

  • Borrar las filas dependientes con dicho valor de clave foránea
  • Actualizar el valor de las claves foráneas con un valor diferente al de la clave primaria y después actualizar el valor de dicha clave primaria

UPDATE CASCADE

Al actualizar el valor de una clave primaria de una tabla referenciada, se propaga el cambio a las claves foráneas de las tablas dependientes.

UPDATE SET NULL

Al actualizar la clave primaria de una fila referenciada, el funcionamiento es el mismo que el de la regla DELETE con la opción SET NULL, excepto que en lugar de borrar la fila de la tabla referenciada, se actualiza la clave primaria con un nuevo valor.

Por defecto se asumen las opciones RESTRICT tanto para actualización como para borrado. Si deseamos cambiarlas deberemos indicarlo al final de la especificación de clave extranjera:

FOREIGN KEY (campo)
REFERENCES tabla_externa (campo_externo)
[ON DELETE {CASCADE | SET NULL | NO ACTION
| RESTRICT}]
[ON UPDATE {CASCADE | SET NULL | NO ACTION
| RESTRICT}]

NO ACTION tiene el efecto de eliminar cualquier comprobación de integridad referencial y es similar a no definir clave extranjera. En MySQL 4.0.13, las claves autorreferenciadas no parecían poder emplear nada más que RESTRICT y NO ACTION en las modificaciones, aunque admiten todas las opcionestambién CASCADE en los borrados.

Ejemplo 1:

Cambiamos la definición de la clave extranjera de la tabla clientes. Como el nombre es interno, debemos averiguarlo pidiendo la estructura de la tabla. Suponemos que la clave extranjera está definida con CONSTRAINT `0_290` FOREIGN KEY (`departamento`) REFERENCES `departamentos` (`departamento`). Podeis insertar datos en MyCC para probar las nuevas restricciones.

SHOW CREATE TABLE clientes;
ALTER TABLE clientes DROP FOREIGN KEY 0_290;
ALTER TABLE clientes ADD FOREIGN KEY (departamento)
REFERENCES departamentos(departamento)
ON DELETE SET NULL ON UPDATE CASCADE;

Tipos de estructuras referenciales

Una estructura referencial es un conjunto de tablas y relaciones entre ellas de modo que cada tabla es referenciada o dependiente de otra u otras del mismo conjunto. Hay 3 tipos de estructuras:

  • Lineal
  • Cíclica
  • Cíclica-autorreferencial

Vamos a ver estas estructuras centrándonos en la regla DELETE. Una tabla está conectada por borrado con otra cuando el borrado de las filas de una implica el borrado de las filas de otra. Es decir, una tabla referenciada está conectada con una tabla dependiente, ya que ésta tiene una clave foránea que referencia a la clave primaria de la tabla referenciada.

En el gráfico, la tabla T2 es una tabla dependiente de la tabla T1, es decir, que la tabla T2 tiene una clave foránea que referencia a la clave primaria de la tabla T1. Se diría que la tabla T2 está conectada por borrado con la tabla T1.

ir1.jpg

Esta estructura está formada por un conjunto de tablas relacionadas entre sí sin cerrar ningún ciclo. Varias tablas relacionadas entre sí mediante una estructura lineal deben tener todas la misma regla DELETE definida. Por ejemplo:

el1.jpg
el2.jpg

En esta estructura, una o varias tablas acaban conectándose consigo mismas a través de las relaciones que tienen con otras tablas. Hay que tener en cuenta las siguientes precauciones a la hora de enfrentarse a una estructura de este tipo:

  • Una tabla no puede estar conectada por borrado consigo misma
  • El camino de borrado se debe bloquear con una opción SET NULL o RESTRICT

Con estas medidas se consigue evitar situaciones en las que le borrado de una fila de una tabla referenciada provoque el borrado masivo de filas de la tabla dependiente y éstas provoquen, a su vez, el borrado masivo de filas de la tabla referencial, con lo que se produce un borrado indeseado de las tablas.

ec1.jpg
ec2.jpg

Este tipo de estructuras se dan cuando una tabla se relaciona consigo misma directamente. Se crea un ciclo en el que interviene una sola tabla. En este tipo de estructuras la regla DELETE siempre tiene que tener la opción CASCADE, ya que de lo contrario se pueden dar resultados diferentes según el gestor de bases de datos, y no se puede permitir que una misma operación produzca resultados diferentes.

eca1.jpg

Ejemplo 2:

Vamos a coger el diseño y el paso a tablas de la base de datos de comercio electrónico, tal y como lo resolvimos en el ejercicio correspondiente, y vamos a crear las tablas correspondientes. Las tablas deberán ser de tipo InnoDB para poder tener claves extranjeras.

La solución propuesta incorpora antes de la creación de tablas un borrado "si existe". Es lo ideal para hacer pruebas. También incluye, detrás del tipo de tabla, la cláusula COMMENT, que sirve para explicar qué contiene una tabla.

DROP TABLE IF EXISTS categorias;
CREATE TABLE categorias (
id int NOT NULL auto_increment,
nombre varchar(50) NOT NULL,
descripcion varchar(200) NOT NULL,
PRIMARY KEY (id)
) TYPE=InnoDB COMMENT='Categorías de productos';

DROP TABLE IF EXISTS productos;
CREATE TABLE productos (
id int NOT NULL auto_increment,
idcategoria int NOT NULL,
nombre varchar(50) NOT NULL,
descripcion varchar(150) NOT NULL,
precio double NOT NULL,
enportada tinyint(1) NOT NULL,
importancia mediumint,
PRIMARY KEY (id),
INDEX (idcategoria),
FOREIGN KEY (idcategoria) REFERENCES categorias(id) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB COMMENT='productos de la tienda';

DROP TABLE IF EXISTS usuarios;
CREATE TABLE usuarios (
id varchar(15) NOT NULL,
passwd varchar(32) NOT NULL,
nombre varchar(25) NOT NULL,
apellidos varchar(50) NOT NULL,
email varchar(50) NOT NULL,
esadmin tinyint(1) NOT NULL,
PRIMARY KEY (id)
) TYPE=InnoDB COMMENT='Usuarios de la tienda';

DROP TABLE IF EXISTS pedidos;
CREATE TABLE pedidos (
id int NOT NULL auto_increment,
idusuario varchar(15),
fecha datetime NOT NULL,
total double NOT NULL,
finalizado tinyint(1) NOT NULL,
PRIMARY KEY (id),
INDEX (idusuario),
FOREIGN KEY (idusuario) REFERENCES usuarios(id) ON DELETE SET NULL ON UPDATE CASCADE
) TYPE=InnoDB COMMENT='Pedidos de los usuarios';

DROP TABLE IF EXISTS productos_pedidos;
CREATE TABLE productos_pedidos (
idpedido int NOT NULL,
idproducto int NOT NULL,
precio double NOT NULL,
cantidad mediumint NOT NULL,
PRIMARY KEY (idpedido,idproducto),
INDEX (idpedido),
FOREIGN KEY (idpedido) REFERENCES pedidos(id) ON DELETE CASCADE ON UPDATE CASCADE,
INDEX (idproducto),
FOREIGN KEY (idproducto) REFERENCES productos(id) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB COMMENT='Productos correspondientes a un pedido';

« MySQL 4 | Creación de bases de datos

Si no se indica lo contrario, el contenido de esta página se ofrece bajo Creative Commons Attribution-ShareAlike 3.0 License