Las opciones para mantener la integridad referencialYa
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.
-- selecciona tu base de datos
-- use zXXX
select '**************** Preparando la BD MiEjemplo...' acción from dual;
drop table if exists imparte;
drop table if exists asignaturas;
drop table if exists profesores;
create table profesores
( dni varchar(10), nombre varchar(40), categoria char(4), ingreso date, primary key (dni)) engine=innodb;
create table asignaturas
( codigo char(5), descripcion varchar(35), creditos decimal(3,1), creditosp decimal(3,1),
primary key (codigo)) engine=innodb;
insert into profesores (select * from ejemplo.profesores);
insert into asignaturas (select * from ejemplo.asignaturas);
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)) engine=innodb;
insert into imparte (select * from ejemplo.imparte);
select * from asignaturas;
select * from profesores;
select * from imparte;
|
**************** estado de la BD INICIAL
codigo |
descripcion |
creditos |
creditosp |
DGBD |
DISEÑO Y GESTION DE BASES DE DATOS |
6.0 |
3.0 |
FBD |
FUNDAMENTOS DE LAS BASES DE DATOS |
6.0 |
1.5 |
FP |
FUNDAMENTOS DE LA PROGRAMACION |
9.0 |
4.5 |
HI |
HISTORIA DE LA INFORMATICA |
4.5 |
|
PC |
PROGRAMACION CONCURRENTE |
6.0 |
1.5 |
dni |
nombre |
categoria |
ingreso |
21111222 |
EVA GOMEZ |
TEU |
1993-10-01 |
21222333 |
MANUEL PALOMAR |
TEU |
1989-06-16 |
21333444 |
RAFAEL ROMERO |
ASO6 |
1992-06-16 |
dni |
asignatura |
21111222 |
DGBD |
21111222 |
FBD |
21333444 |
PC |
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
codigo |
descripcion |
creditos |
creditosp |
DGBD |
DISEÑO Y GESTION DE BASES DE DATOS |
6.0 |
3.0 |
FBD |
FUNDAMENTOS DE LAS BASES DE DATOS |
6.0 |
1.5 |
FP |
FUNDAMENTOS DE LA PROGRAMACION |
9.0 |
4.5 |
PC |
PROGRAMACION CONCURRENTE |
6.0 |
1.5 |
dni |
nombre |
categoria |
ingreso |
21111222 |
EVA GOMEZ |
TEU |
1993-10-01 |
21222333 |
MANUEL PALOMAR |
TEU |
1989-06-16 |
21333444 |
RAFAEL ROMERO |
ASO6 |
1992-06-16 |
dni |
asignatura |
21111222 |
DGBD |
21111222 |
FBD |
21333444 |
PC |
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;
codigo |
descripcion |
creditos |
creditosp |
DGBD |
DISEÑO Y GESTION DE BASES DE DATOS |
6.0 |
3.0 |
FBD |
FUNDAMENTOS DE LAS BASES DE DATOS |
6.0 |
1.5 |
FP |
FUNDAMENTOS DE LA PROGRAMACION |
9.0 |
4.5 |
HI |
HISTORIA DE LA INFORMATICA |
4.5 |
|
PC |
PROGRAMACION CONCURRENTE |
6.0 |
1.5 |
dni |
nombre |
categoria |
ingreso |
21111222 |
EVA GOMEZ |
TEU |
1993-10-01 |
21222333 |
MANUEL PALOMAR |
TEU |
1989-06-16 |
21333444 |
RAFAEL ROMERO |
ASO6 |
1992-06-16 |
dni |
asignatura |
21111222 |
DGBD |
21111222 |
FBD |
21333444 |
PC |
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';
codigo |
descripcion |
creditos |
creditosp |
DGBD |
DISEÑO Y GESTION DE BASES DE DATOS |
6.0 |
3.0 |
FP |
FUNDAMENTOS DE LA PROGRAMACION |
9.0 |
4.5 |
PC |
PROGRAMACION CONCURRENTE |
6.0 |
1.5 |
dni |
nombre |
categoria |
ingreso |
21111222 |
EVA GOMEZ |
TEU |
1993-10-01 |
21222333 |
MANUEL PALOMAR |
TEU |
1989-06-16 |
21333444 |
RAFAEL ROMERO |
ASO6 |
1992-06-16 |
dni |
asignatura |
21111222 |
DGBD |
21333444 |
PC |
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
codigo |
descripcion |
creditos |
creditosp |
DGBD |
DISEÑO Y GESTION DE BASES DE DATOS |
6.0 |
3.0 |
FP |
FUNDAMENTOS DE LA PROGRAMACION |
9.0 |
4.5 |
PC |
PROGRAMACION CONCURRENTE |
6.0 |
1.5 |
dni |
nombre |
categoria |
ingreso |
21111222 |
EVA GOMEZ |
TEU |
1993-10-01 |
21333444 |
RAFAEL ROMERO |
ASO6 |
1992-06-16 |
dni |
asignatura |
21111222 |
DGBD |
21333444 |
PC |
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) ) engine=innodb;
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');
select * from asignaturas;
select * from profesores;
select * from imparte;
codigo |
descripcion |
creditos |
creditosp |
DGBD |
DISEÑO Y GESTION DE BASES DE DATOS |
6.0 |
3.0 |
FBD |
FUNDAMENTOS DE LAS BASES DE DATOS |
6.0 |
1.5 |
FP |
FUNDAMENTOS DE LA PROGRAMACION |
9.0 |
4.5 |
HI |
HISTORIA DE LA INFORMATICA |
4.5 |
|
PC |
PROGRAMACION CONCURRENTE |
6.0 |
1.5 |
dni |
nombre |
categoria |
ingreso |
21111222 |
EVA GOMEZ |
TEU |
1993-10-01 |
21222333 |
MANUEL PALOMAR |
TEU |
1989-06-16 |
21333444 |
RAFAEL ROMERO |
ASO6 |
1992-06-16 |
ficha |
dni |
asignatura |
1 |
21111222 |
DGBD |
2 |
21111222 |
FBD |
3 |
21333444 |
PC |
delete from profesores where dni = '21111222';
codigo |
descripcion |
creditos |
creditosp |
DGBD |
DISEÑO Y GESTION DE BASES DE DATOS |
6.0 |
3.0 |
FBD |
FUNDAMENTOS DE LAS BASES DE DATOS |
6.0 |
1.5 |
FP |
FUNDAMENTOS DE LA PROGRAMACION |
9.0 |
4.5 |
HI |
HISTORIA DE LA INFORMATICA |
4.5 |
|
PC |
PROGRAMACION CONCURRENTE |
6.0 |
1.5 |
dni |
nombre |
categoria |
ingreso |
21222333 |
MANUEL PALOMAR |
TEU |
1989-06-16 |
21333444 |
RAFAEL ROMERO |
ASO6 |
1992-06-16 |
ficha |
dni |
asignatura |
1 |
|
DGBD |
2 |
|
FBD |
3 |
21333444 |
PC |
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;
codigo |
descripcion |
creditos |
creditosp |
DGBD |
DISEÑO Y GESTION DE BASES DE DATOS |
6.0 |
3.0 |
FBD |
FUNDAMENTOS DE LAS BASES DE DATOS |
6.0 |
1.5 |
FP |
FUNDAMENTOS DE LA PROGRAMACION |
9.0 |
4.5 |
HI |
HISTORIA DE LA INFORMATICA |
4.5 |
|
PC |
PROGRAMACION CONCURRENTE |
6.0 |
1.5 |
dni |
nombre |
categoria |
ingreso |
21111222 |
EVA GOMEZ |
TEU |
1993-10-01 |
21222333 |
MANUEL PALOMAR |
TEU |
1989-06-16 |
21333444 |
RAFAEL ROMERO |
ASO6 |
1992-06-16 |
dni |
asignatura |
21111222 |
DGBD |
21111222 |
FBD |
21333444 |
PC |
update asignaturas set codigo = 'AAA' where codigo = 'FBD';
update profesores set dni = '33' where dni = '21111222';
codigo |
descripcion |
creditos |
creditosp |
DGBD |
DISEÑO Y GESTION DE BASES DE DATOS |
6.0 |
3.0 |
AAA |
FUNDAMENTOS DE LAS BASES DE DATOS |
6.0 |
1.5 |
FP |
FUNDAMENTOS DE LA PROGRAMACION |
9.0 |
4.5 |
HI |
HISTORIA DE LA INFORMATICA |
4.5 |
|
PC |
PROGRAMACION CONCURRENTE |
6.0 |
1.5 |
dni |
nombre |
categoria |
ingreso |
33 |
EVA GOMEZ |
TEU |
1993-10-01 |
21222333 |
MANUEL PALOMAR |
TEU |
1989-06-16 |
21333444 |
RAFAEL ROMERO |
ASO6 |
1992-06-16 |
dni |
asignatura |
33 |
DGBD |
33 |
AAA |
21333444 |
PC |
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.
|