P03 Modificar, claves ajenas y filtros adicionales

Más manipulación de datos: Update, modificando valores

Update forma parte de la órdenes clásicas de manipulación de datos: select, insert, delete y update. Simplemente, por cuestiones de equilibrio de contenidos la hemos dejado para esta sesión.

Antes de comenzar, reconstruyamos la base de datos: 

delete from imparte;

delete from profesores; 

delete from asignaturas; 

insert into asignaturas (codigo, descripcion, creditos, creditosp) values ('HI', 'HISTORIA DE LA INFORMATICA', 4.5, NULL); 

insert into asignaturas (codigo, descripcion, creditos, creditosp) values ('FBD', 'FUNDAMENTOS DE LAS BASES DE DATOS', 6.0, 1.5); 

insert into asignaturas (codigo, descripcion, creditos, creditosp) values ('DGBD', 'DISEÑO Y GESTION DE BASES DE DATOS', 6.0, 3.0); 

insert into asignaturas (codigo, descripcion, creditos, creditosp) values ('PC', 'PROGRAMACION CONCURRENTE', 6.0, 1.5); 

insert into asignaturas (codigo, descripcion, creditos, creditosp) values ('FP', 'FUNDAMENTOS DE LA PROGRAMACION', 9.0, 4.5); 

insert into profesores (dni, nombre, categoria, ingreso) values ('21111222','EVA GOMEZ','TEU','1993-10-01'); 

insert into profesores (dni, nombre, categoria, ingreso) values ('21222333','MANUEL PALOMAR','TEU','1989-06-16'); 

insert into profesores (dni, nombre, categoria, ingreso) values ('21333444','RAFAEL ROMERO','ASO6','1992-06-16'); 

insert into imparte (dni, asignatura) values ('21111222','FBD'); 

insert into imparte (dni, asignatura) values ('21111222','DGBD'); 

insert into imparte (dni, asignatura) values ('21333444','PC'); 

La sentencia UPDATE nos permite modificar la información contenida en una tabla. 

UPDATE nombreTabla [aliasTabla] 

SET { {columna=expresion | columna=subconsulta} | listaColumnas=subconsulta} 

[ WHERE condición] 

No se pueden modificar varias tablas a la vez en una misma sentencia. Para modificar los valores de varias tablas varias habrá que ejecutar tantas sentencias UPDATE como tablas queramos modificar. (NOTA: al igual que con delete MySQL sí proporciona sintaxis específica para poder hacerlo pero otros SGBD no).

select codigo,creditos,creditosp from asignaturas; 

update asignaturas set creditos = 0;

select codigo,creditos from asignaturas; 

Cuando se desea modificar más de una columna se indicará la lista de columnas y valores separadas por comas: 

Modifica los créditos de las asignaturas a 4, y los créditos prácticos a 2. 

update asignaturas set creditos=4, creditosp=2; 

En el caso de que se indique alguna condición, se modificarán sólo aquellas filas de la tabla que cumplan la condición o condiciones impuestas: 

select * from profesores;

Clave ajena

Una clave ajena es una columna o columnas en una tabla que almacena valores que hacen referencia a datos en otras tablas. Una clave ajena es una referencia, no sirve para identificar, por lo tanto admite duplicados y nulos. Por otro lado, la clave ajena siempre tiene la misma estructura y tipos de datos que la clave primaria de la tabla a la que está apuntando.

PROFESORES ( DNI varchar(10), nombre varchar(40), categoria char(4), ingreso date )

Clave primaria (DNI)

ASIGNATURAS ( codigo char(5), descripcion varchar(35), creditos decimal(3,1), creditosp decimal(3,1) )

Clave primaria (codigo)

IMPARTE ( dni : varchar(10), asignatura : char(5) ) 

Clave primaria: (dni, asignatura) 

Clave ajena: dni → PROFESORES 

Clave ajena: asignatura → ASIGNATURAS 

Pero lo más importante de las restricciones de clave ajena es que permiten garantizar la integridad referencial de los datos almacenados en nuestra base de datos. Más adelante explicaremos qué es esto pero antes debemos crear la base de datos de ejemplo.

Motores de MySQL

Lo visto hasta ahora constituye el estándar de SQL y es la sintaxis admitida en los SGBD de mayor penetración comercial.

Sin embargo, MySQL es un producto peculiar ya que ofrece varias opciones de almacenamiento y gestión de las tablas en aras de ofrecer alternativas que mejoren el rendimiento o la integridad de datos. En particular podemos elegir entre tablas MyISAM e InnoDB.

Si queremos mantener integridad referencial entre nuestras tablas es imprescindible que especifiquemos que queremos usar el motor InnoDB. En una instalación estándar de MySQL, si no decimos nada, la creación de una tabla se hace por defecto en MyISAM y tiene un efecto importantísimo: si definimos claves ajenas, el sistema las ignora y no revisa la integridad referencial.

Todas las tablas implicadas en una clave ajena han de estar definidas en este motor, tanto la que aloja a la clave ajena como a la que hace refencia, y es obligatorio indicar la columna o columnas que constituyen la clave primaria en esta segunda. Aunque todavía no hemos definido clave ajena ninguna a continuación sí lo vamos a hacer, y puesto que van a hacer referencia a PROFESORES y ASIGNATURAS estas tablas también deben almacenarse como INNODB.

Por tanto, y puesto que queremos garantizar las restricciones de clave ajena, hemos de completar las definiciones manejadas hasta ahora. Antes de comenzar, reconstruyamos la base de datos: 

drop table if exists imparte; 

-- "if exists" comprueba primero si existe tal tabla antes de eliminarla 

-- se utiliza para evitar el aviso o error de "tabla no encontrada"

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)) engine=innodb; 

create table asignaturas ( 

codigo char(5), 

descripcion varchar(35), 

creditos decimal(3,1), 

creditosp decimal(3,1),

coordina varchar(10), 

primary key (codigo),

foreign key (coordina) references profesores(DNI) ) engine=innodb; 

insert into profesores (dni, nombre, categoria, ingreso) values ('21111222','EVA GOMEZ','TEU','1993-10-01');

insert into profesores (dni, nombre, categoria, ingreso) values ('21222333','MANUEL PALOMAR','TEU','1989-06-16');

insert into profesores (dni, nombre, categoria, ingreso) values ('21333444','RAFAEL ROMERO','ASO6','1992-06-16');

insert into asignaturas (codigo, descripcion, creditos, creditosp, coordina)

values ('HI', 'HISTORIA DE LA INFORMATICA', 4.5, NULL, NULL);

insert into asignaturas (codigo, descripcion, creditos, creditosp, coordina) 

values ('FBD', 'FUNDAMENTOS DE LAS BASES DE DATOS', 6.0, 1.5, '21111222');

insert into asignaturas (codigo, descripcion, creditos, creditosp, coordina) 

values ('DGBD', 'DISEÑO Y GESTION DE BASES DE DATOS', 6.0, 3.0, '21111222');

insert into asignaturas (codigo, descripcion, creditos, creditosp, coordina) 

values ('PC', 'PROGRAMACION CONCURRENTE', 6.0, 1.5, NULL);

insert into asignaturas (codigo, descripcion, creditos, creditosp, coordina) 

values ('FP', 'FUNDAMENTOS DE LA PROGRAMACION', 9.0, 4.5, NULL);

Integridad referencial

La integridad referencial es la propiedad de las claves ajenas que nos asegura que todas las referencias desde una hacia otra tabla son consistentes. Más o menos se formula como que "una clave ajena o es totalmente nula o contiene valores almacenados previamente en la clave primaria a la que apunta".

Lo que se consigue con esto es que, por ejemplo, no digamos que una asignatura está siendo coordinada por un profesor que no existe. Por ejemplo, el sistema no nos va a permitir hacer la siguiente inserción:

insert into asignaturas (codigo, descripcion, creditos, creditosp, coordina)

values ('FP2', 'FUNDAMENTOS DE LA PROGRAMACIÓN 2', 6.0, 3.0, '55555666');

Puesto que en PROFESORES no hay ninguna fila con ese DNI, la orden no tiene otro efecto que mostrar un mensaje de error.

Cannot add or update a child row: a foreign key constraint fails (`xxx`.`asignatura`, CONSTRAINT ... FOREIGN KEY (`coordina`) REFERENCES `profesores` (`DNI`))

Ahora vamos a crear IMPARTE. 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 

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; 

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 21111222 imparte la asignatura identificada como DGBD  

insert into imparte (dni, asignatura) values ('21111222','DGBD');

select * from imparte;

IR y delete

La integridad referencial también evita que se eliminen datos que están siendo usados en alguna clave ajena. Sólo se podrán borrar aquellas filas que no estén siendo referenciadas, a través de ninguna clave ajena, desde otra tabla. Por ejemplo, la asignatura DGBD es impartida por el profesor con dni 21333444: no se podrá borrar la asignatura si antes no se eliminan las filas correspondientes a esa asignatura en la tabla imparte.

Desde otro punto de vista, si el sistema nos permitiera eliminar la fila de ASIGNATURAS ('DGBD', 'DISEÑO Y GESTION DE BASES DE DATOS', 6.0, 3.0, '21111222'), en IMPARTE estaríamos diciendo que Eva Gómez está impartiendo una asignatura que no sabemos cuál es. Esto es lo que evita la integridad referencial.

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`))

Lo que sí podemos hacer es borrar primero esa fila de IMPARTE y después la asignatura:

Eliminando las referencias que nos impiden el borrado: 

-- eliminamos las referencias en imparte

delete from imparte where asignatura='DGBD';

-- ahora sí puedo borrar la asignatura DGBD

-- porque ya no hay ninguna clave ajena que le esté haciendo referencia

delete from asignaturas where codigo='DGBD';

IR y update

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.

Vamos primero a poblar la tabla IMPARTE:

insert into imparte (dni, asignatura) values ('21111222','FBD');

insert into imparte (dni, asignatura) values ('21111222','DGBD');

insert into imparte (dni, asignatura) values ('21333444','PC'); 

select * from imparte; 

La siguiente orden generaría un problema de integridad referencial.

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

--¡ERROR!

Si pudiéramos cambiar el identificador 'FBD' por 'BD1', en IMPARTE seguiría la información de que '21111222' imparte 'FBD', asignatura que ya no existiría. Por eso el sistema no nos deja, en principio, hacer directamente esta operación. Para poder realizarla necesitamos 3 pasos consistentes en 

1. en ASIGNATURAS, insertar una nueva fila en asignaturas con el identificador BD1 y copiando el resto de los valores 

2. en IMPARTE, cambiar las referencias a FBD por BD1

3. en ASIGNATURAS, eliminar la fila de FBD 

-- 1. nueva asignatura con los datos de la antigua 

insert into asignaturas (codigo,descripcion,creditos,creditosp,coordina) 

values ('BD1', 'FUNDAMENTOS DE LAS BASES DE DATOS', 6.0, 1.5, '21111222'); 

select * from asignaturas; 

-- 2. modificación de imparte 

update imparte set asignatura = 'BD1' where asignatura = 'FBD'; 

select * from imparte; 

-- 3. borrado de la asignatura antigua 

delete from asignaturas where codigo = 'FBD'; 

select * from asignaturas; 

Modificando una definición de clave ajena

La modificación de la estructura de una tabla ya creada para añadir una clave ajena o modificar alguna existe, necesitará del uso del operador ALTER TABLE como ya se explicó en la sesión anterior.

La siguiente instrucción permite añadir una nueva clave ajena a una tabla ya creada (incluso con datos, siempre que los datos ya existentes sean compatibles con las restricciones de la clave ajena):

    alter table imparte add foreign key (dni) references profesores (dni);

Por otra parte, para modificar o eliminar la definición de la clave ajena es necesario conocer el nombre que se le ha dado a esa restricción. Consultando la estructura de la tabla, bien con HeidiSQL / DBeaver directamente, o bien con el comando SHOW CREATE TABLE podemos conocer el nombre de la clave. En este caso queremos eliminar la clave ajena anterior cuyo nombre interno es 'imparte_fk_2asignatura':

    alter table imparte drop foreign key imparte_fk_2asignatura;

Expresiones de selección de filas

Rangos

Expresiones del tipo 10 <= x <= 100 se pueden construir utilizando el operador de construcción de rangos BETWEEN. La sintaxis de tal subexpresión de la cláusula where es la siguiente:

expresión [NOT] BETWEEN expresión AND expresión 

Créditos y descripción de las asignaturas cuyo número de créditos está entre 5 y 8. 

select creditos, descripcion 

from asignaturas 

where creditos between 5 and 8;

Código de las asignaturas de 'Bases de Datos' 

select codigo from asignaturas where descripcion like '%BASES DE DATOS%' 

Información adicional

En esta página puedes encontrar contenidos adicionales que ampliarán algunos de los conceptos mostrados en esta lección.