Integridad referencial
IMPORTANTE: para esta lección necesitas tu base de datos, donde tienes permisos para utilizar los comandos de definición de datos.
Cuando definimos claves ajenas en una tabla, le estamos diciendo al servidor de base de datos que "vigile" los valores que almacenamos en ellas. La integridad referencial es la propiedad de las claves ajenas que nos asegura que todas las referencias desde una hacia otra tabla son consistentes. Básicamente, que una clave ajena solo puede alojar valores nulos o los que estén previamente almacenados en la clave primaria a la que apuntan.
Por otro lado, no podemos dejar "datos huérfanos". Por ejemplo, si borramos una fila de la tabla ASIGNATURAS, ¿qué pasa con las referencias que pueda haber en IMPARTE?
Por lo general, esa operación que pretendemos hacer, borrar la fila de DGBD, sería invalidada por el propio motor de base de datos, el que vigila que se cumpla la integridad referencial. Si quisiéramos eliminar la asignatura de nuestra base de datos, tendríamos que borrar primero cualquier referencia a ella, es decir, tendríamos que eliminar las filas de IMPARTE donde aparezca el valor DGBD en la columna asignatura.
Pero también podríamos prever la situación y establecer una respuesta automática por parte del servidor de base de datos, por ejemplo, que sea él el que busque, encuentre y elimine automáticamente esas filas de IMPARTE para poder ejecutar el borrado definitivo de la asignatura. Resumiendo, cuando definimos una clave ajena podemos conseguir que se comporte como:
Si se borra una fila en la tabla apuntada por la clave ajena con un valor usado en dicha clave ajena:
RECHAZAR la operación de borrado, esta es la opción por defecto si no se añade nada a la definición de clave ajena
PROPAGAR la operación, de borrado
ANULAR el valor, modificarlo a null
Las mismas opciones tenemos si se trata de modificar valores de clave primaria que estén siendo usados en la clave ajena.
Para conseguir esos comportamientos, hay que añadir a la definición de clave ajena los modificadores apropiados:
FOREIGN KEY (columna) references nombreTabla (columnas) [ON DELETE {cascade | set null} ] [ON UPDATE {cascade | set null} ]
Es decir, on delete y on update, ambos son opcionales, y cada uno permiten uno de dos métodos. Si no se dice nada de cualquiera de ellos, el servidor rechazará las operaciones que potencialmente provoquen una inconsistencia referencial.
Integridad referencial
Antes de comenzar, reconstruyamos la base de datos:
-- selecciona tu base de datos
use zXXX;
drop table if exists imparte;
drop table if exists profesores;
drop table if exists asignaturas;
create table profesores (DNI varchar(10), nombre varchar(40), categoria char(4), ingreso date,
primary key (DNI));
create table asignaturas (codigo char(5), descripcion varchar(35), creditos decimal(3,1), creditosp decimal(3,1),
primary key (codigo));
create table imparte (dni varchar(10), asignatura char(5),
primary key (dni,asignatura),
foreign key (dni) references profesores (DNI),
foreign key (asignatura) references asignaturas (codigo));
insert into asignaturas select * from ejemplo.asignaturas;
insert into profesores select * from ejemplo.profesores;
-- la tabla imparte se queda vacía, sin filas
Insertando
La tabla IMPARTE relaciona profesores y asignaturas informando de qué profesores imparten qué asignatura:
IMPARTE ( dni : varchar(10), asignatura : char(5) )
Clave primaria: (dni, asignatura)
Clave ajena: dni → PROFESORES
Clave ajena: asignatura → ASIGNATURAS
La integridad referencial exige que el valor que tenga la columna dni exista en la tabla PROFESORES; de la misma manera cada código de asignatura deberá existir en la tabla ASIGNATURAS.
En este momento, los DNI almacenados en PROFESORES son:
y los códigos de ASIGNATURAS:
Inserta la información de que el profesor identificado como 55555555 imparte la asignatura identificada como AAA
insert into imparte (dni, asignatura)
values ('55555555','AAA');
El resultado es un error:
Cannot add or update a child row: a foreign key constraint fails (`xxx`.`imparte`, CONSTRAINT `imparte_ibfk_1` FOREIGN KEY (`dni`) REFERENCES `profesores` (`DNI`))
Inserta la información de que el profesor identificado como 21333444 imparte la asignatura identificada como DGBD (valores que sí existen)
insert into imparte (dni, asignatura) values ('21333444','DGBD');
select * from imparte;
Borrando
Sólo se podrán borrar aquellas filas (DELETE) que no estén siendo referenciadas, a través de ninguna clave ajena, desde otra tabla.
Recreamos nuestras tablas para comenzar con los valores de inicio:
-- selecciona tu base de datos
use zXXX;
drop table if exists imparte;
drop table if exists profesores;
drop table if exists asignaturas;
create table profesores (DNI varchar(10), nombre varchar(40), categoria char(4), ingreso date,
primary key (DNI));
create table asignaturas (codigo char(5), descripcion varchar(35), creditos decimal(3,1), creditosp decimal(3,1),
primary key (codigo));
create table imparte (dni varchar(10), asignatura char(5),
primary key (dni,asignatura),
foreign key (dni) references profesores (DNI),
foreign key (asignatura) references asignaturas (codigo));
insert into asignaturas select * from ejemplo.asignaturas;
insert into profesores select * from ejemplo.profesores;
insert into imparte select * from ejemplo.imparte;
select * from profesores
select * from imparte
select * from asignaturas
Por ejemplo la asignatura DGBD es impartida por los profesores con dni 21111222 y 21333444 (consulta la tabla del anterior resultado): no se podrá borrar la asignatura si antes no se eliminan las filas correspondientes a esa asignatura en la tabla imparte.
delete from asignaturas where codigo='DGBD';
Cannot delete or update a parent row: a foreign key constraint fails (`xxx`.`imparte`, CONSTRAINT `imparte_ibfk_2` FOREIGN KEY (`asignatura`) REFERENCES `asignaturas` (`codigo`))
Eliminando las referencias que nos impiden el borrado:
-- eliminamos las referencias en imparte
delete from imparte where asignatura='DGBD';
-- ahora ya puedo borrar la asignatura DGBD porque ya no hay
-- ninguna clave ajena que le esté haciendo referencia
delete from asignaturas where codigo='DGBD';
Modificando
Con UPDATE, en general, las claves ajenas generan las mismas restricciones de integridad referencial que las vistas para el DELETE salvo por la naturaleza de la operación: el UPDATE sólo generará problemas de integridad referencial si el dato a modificar es un valor de clave primaria que está siendo referenciada por alguna clave ajena.
La siguiente orden generaría un problema con la integridad referencial:
update asignaturas set codigo = 'BD1' where codigo = 'FBD';
/* Error de SQL (1451): Cannot delete or update a parent row: a foreign key constraint fails (`xxxx`.`imparte`, CONSTRAINT `FK_asig` FOREIGN KEY (`asignatura`) REFERENCES `asignaturas` (`codigo`)) */
Para poder realizar esta operación, será necesario insertar una nueva fila en asignaturas con el identificador BD1 y copiando el resto de los valores, después cambiar las referencias a FBD por BD1 y, por último, borrar la fila de FBD.
-- 1. nueva asignatura con los datos de la antigua
insert into asignaturas (codigo,descripcion,creditos,creditosp)
select 'BD1', descripcion,creditos,creditosp
from asignaturas
where codigo = 'FBD';
-- 2. modificación de imparte
update imparte set asignatura = 'BD1'
where asignatura = 'FBD';
-- 3. borrado de la asignatura antigua
delete from asignaturas where codigo = 'FBD';
Las opciones para mantener la integridad referencial
Ya se ha visto que el intento de borrar ciertas filas, o modificar valores de clave primaria, es rechazado por el SGBD si estas están siendo referenciadas por alguna clave ajena. El sistema está velando por la integridad de los datos almacenados.
No obstante, es posible automatizar y prever estas situaciones expresando en el esquema de la base de datos nuestra voluntad de propagar las operaciones de borrado de filas y de actualización de valores de clave primaria hasta donde haga falta. Las opciones posibles, para borrado (delete) y modificación (update) son
RECHAZAR la operación (opción por defecto, si no se dice nada este es el comportamiento de la clave ajena)
PROPAGAR la operación (cascade)
ANULAR, cambiar el valor de clave ajena a nulo (set null)
Antes de mostrar estas opciones, el siguiente ejemplo nos recuerda las restricciones que impone el exigir integridad referencial a las claves ajenas.
Recreamos nuestras tablas para comenzar con los valores de inicio:
-- selecciona tu base de datos
use zXXX;
drop table if exists imparte;
drop table if exists profesores;
drop table if exists asignaturas;
create table profesores (DNI varchar(10), nombre varchar(40), categoria char(4), ingreso date,
primary key (DNI));
create table asignaturas (codigo char(5), descripcion varchar(35), creditos decimal(3,1), creditosp decimal(3,1),
primary key (codigo));
create table imparte (dni varchar(10), asignatura char(5),
primary key (dni,asignatura),
foreign key (dni) references profesores (DNI),
foreign key (asignatura) references asignaturas (codigo));
insert into asignaturas select * from ejemplo.asignaturas;
insert into profesores select * from ejemplo.profesores;
insert into imparte select * from ejemplo.imparte;
Como no se ha dicho nada en las claves ajenas, la opción para mantener la integridad es —en todas ellas y para borrados y modificaciones— RECHAZAR la operación si el valor de clave ajena está siendo utilizado como referencia en otra tabla.
select * from profesores
select * from imparte
select * from asignaturas
delete from asignaturas where codigo = 'FBD';
La ejecución fallará porque hay profesores que imparten la asignatura FBD.
delete from asignaturas where codigo = 'HI';
Se cumple la orden porque nadie imparte HI.
On delete
Propagar
En ciertos sistemas de información es posible redefinir las restricciones de clave ajena para que no se den estos mensajes de error. Ello es posible mediante la cláusula ON DELETE al crear una tabla:
FOREIGN KEY (columna[,columna[, ...]]) REFERENCES tabla (clave primaria)
ON DELETE {CASCADE | SET NULL}
La acción a realizar ante el borrado de una fila que está siendo referenciada por alguna clave ajena puede ser el propagar la operacion (ON DELETE CASCADE) o anular (ON DELETE SET NULL), dependiendo de la decisión del diseñador de la base de datos.
Veamos ahora el efecto de utilizar la opción ON DELETE CASCADE.
Ejecuta
drop table if exists imparte;
delete from asignaturas;
delete from profesores;
insert into asignaturas (select * from ejemplo.asignaturas);
insert into profesores(select * from ejemplo.profesores);
create table imparte( dni varchar(10), asignatura char(5),
primary key (dni, asignatura),
foreign key (dni) references profesores(dni),
foreign key (asignatura) references asignaturas(codigo)
ON DELETE CASCADE
);
insert into imparte (select * from ejemplo.imparte);
select * from asignaturas;
select * from profesores;
select * from imparte;
El borrado de 'FBD' provoca borrados automáticos en IMPARTE y el borrado de 'HI' sigue sin generar problemas
delete from asignaturas where codigo = 'FBD';
delete from asignaturas where codigo = 'HI';
Sin embargo, en la CAj a PROFESORES no hemos definido método alguno para mantener la IR.
delete from profesores where dni = '21111222';
Falla porque este profesor imparte DGBD
delete from profesores where dni = '21222333';
Éxito ya que no imparte ninguna asignatura
Anular
Si la modificación de una clave ajena es ON DELETE SET NULL, la acción que llevará a cabo automáticamente el SGBD es la de poner NULOS en aquellos casos en que la integridad referencial se vea comprometida.
Esta definición tiene más dificultad de aplicación puesto que prevalecen las definiciones de VALOR NO NULO. Por ejemplo, es inútil utilizarla en IMPARTE.DNI ya que forma parte de la clave primaria y no admite nulos en ningún caso. Vamos a cambiar el esquema de IMPARTE.
drop table if exists imparte;
delete from asignaturas;
delete from profesores;
insert into profesores (select * from ejemplo.profesores);
insert into asignaturas (select * from ejemplo.asignaturas);
create table imparte (
ficha integer,
dni varchar(10), asignatura char(5),
primary key (ficha),
foreign key (dni) references profesores (dni) ON DELETE SET NULL,
foreign key (asignatura) references asignaturas (codigo) ); -- por defecto, RECHAZA para borrados y modificaciones en ASIGNATURAS
Las claves ajenas, ahora, permiten nulos.
insert into imparte values (1,'21111222','FBD');
insert into imparte values (2,'21111222','DGBD');
insert into imparte values (3,'21333444','PC');
delete from profesores where dni = '21111222';
On update
La definición ON UPDATE se rige por los mismos parámetros que la anterior, se puede elegir entre propagar o anular; o rechazar, si no definimos nada.
Nótese que se pueden hacer todas las combinaciones posibles en todas las claves ajenas, no necesariamente ha de ser todas propagar o todas anular, ni tampoco tienen por qué coincidir en la misma clave ajena.
drop table if exists imparte;
drop table if exists imparte;
delete from asignaturas;
delete from profesores;
insert into profesores (select * from ejemplo.profesores);
insert into asignaturas (select * from ejemplo.asignaturas);
create table imparte (
ficha integer,
dni varchar(10), asignatura char(5),
primary key (ficha),
foreign key (dni) references profesores (dni) ON UPDATE CASCADE,
foreign key (asignatura) references asignaturas (codigo) ON UPDATE SET NULL
);
insert into imparte values (1,'21111222','FBD');
insert into imparte values (2,'21111222','DGBD');
insert into imparte values (3,'21333444','PC');
update asignaturas set codigo = 'AAA' where codigo = 'FBD';
update profesores set dni = '33' where dni = '21111222';
Nótese que, en cualquier caso, estos automatismos solo son necesarios en una dirección, en borrados o modificaciones sobre la tabla referenciada, no en la tabla que aloja las claves ajenas:
delete from imparte where ficha=33;
Obviamente, se puede establecer cualquier combinación de métodos que queramos utilizar, como por ejemplo
drop table if exists imparte;
create table imparte(
ficha integer,
dni varchar(10), asignatura char(5),
primary key (ficha),
foreign key (dni) references profesores(dni) ON UPDATE CASCADE,
foreign key (asignatura) references asignaturas(codigo) ON DELETE SET NULL ON UPDATE CASCADE
);
En este ejemplo anterior se utiliza, además de PROPAGAR y ANULAR en ambas claves ajenas, RECHAZAR en IMPARTE.dni —simplemente, no se ha definido política ninguna para on delete—.