P06 Opciones de integridad referencial

Las opciones para mantener la integridad referencial

Ya se ha practicado con la integridad referencial en sesiones anteriores y se ha visto que el intento de borrar ciertas filas es rechazado por el SGBD si éstas 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. Antes de mostrar estas opciones, el siguiente ejemplo nos recuerda las restricciones que impone el exigir integridad referencial a las claves ajenas.

**************** estado de la BD INICIAL

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.

select * from asignaturas;

select * from profesores;

select * from imparte;;

**************** estado de la BD FINAL

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.

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

) engine=innodb;

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

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;

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)

ON DELETE CASCADE ON UPDATE CASCADE ,

foreign key (asignatura) references asignaturas(codigo)

ON DELETE CASCADE ON UPDATE CASCADE

) engine=innodb;

insert into imparte (select * from ejemplo.imparte);

select * from asignaturas;

select * from profesores;

select * from imparte;

update asignaturas set codigo = 'AAA' where codigo = 'FBD';

update profesores set dni = '33' where dni = '21111222';

Obviamente, se puede establecer cualquier combinación de métodos que queramos utilizar, como por ejemplo

create table imparte( 

dni varchar(10), 

asignatura char(5),

primary key (dni, asignatura),

foreign key (dni) references profesores(dni)

ON UPDATE CASCADE ,

foreign key (asignatura) references asignaturas(codigo)

ON DELETE SET NULL ON UPDATE CASCADE

) engine=innodb;

En el ejemplo anterior se utilizan RECHAZAR, PROPAGAR Y ANULAR entre las dos claves ajenas de la tabla.