Inicio‎ > ‎SQL‎ > ‎Lecciones SQL‎ > ‎

T04 Definición y manipulación de datos

Definición de datos


Create table

La definición de tablas es el primer paso en la creación de una base de datos. El conjunto de descripciones de tablas conforma el esquema de base de datos y representa a un sistema de información concreto.

Supongamos que vamos a implementar un esquema de base de datos relacional de profesores, asignaturas (sólo es un listado de profesores y asignaturas, sin relaciones entre ellos). En primer lugar debemos decidir cuáles son los atributos de cada uno de ellos y sus tipos de datos:


 PROFESORES  ASIGNATURAS
DNI: varchar(10),
nombre: varchar(40),
categoría: char(4),
ingreso: date
código: char(5),
descripción: varchar(35),
créditos: decimal(3,1),
créditosp: decimal(3,1)


Para cumplir con las restricciones del modelo relacional, además, debemos elegir las claves primarias adecuadas : DNI para profesores y código para asignaturas. Obviamente, la forma que tienen estas tablas ha sido una decisión nuestra como diseñadores de esta base de datos concreta, en otra situación hubiéramos, probablemente, decidido definir otros atributos y otras tablas.


La orden CREATE TABLE nos permite crear cada una de las tablas necesarias para nuestra base de datos:

CREATE TABLE nombreTabla ( {listaColumnas} [,{restricciones}] )

 
La lista de columnas, en su forma más sencilla, es un conjunto de expresiones (tantas como columnas deseemos, y separadas por comas) del tipo:

columna tipoDatos[,columna tipoDatos[, ...]]

La totalidad de tipos de datos que maneja MySQL, siendo la mayoría comunes con ligeras diferencias a cualquier motor de base de datos, se puede encontrar en http://dev.mysql.com/doc/refman/5.1/en/data-types.html.


Restricciones

Las restricciones son reglas, que normalmente se establecen en el momento de crear una tabla, para garantizar la integridad de los datos.

Básicamente, las restricciones obligan a cumplirse ciertas reglas cuando una fila es insertada, borrada o modificada, de forma que la operación se llevará a efecto sólo si se cumplen las restricciones definidas en la tabla.

Podemos contemplar los siguientes tipos de restricciones de integridad de datos:
  • NOT NULL: especifica que la columna no puede contener un valor nulo.
  • PRIMARY KEY: identifica de manera única a cada fila de la tabla mediante una o varias columas, estas columnas que forman la clave primaria no pueden tener valores nulos.
  • FOREIGN KEY: establece una relación entre una(s) columna(s) de la tabla y otra(s) columna(s) de la tabla referenciada, siendo esta última(s) columna(s) la PRIMARY KEY
  • UNIQUE: no permite duplicados; combinado con NOT NULL es la forma de definir una clave alternativa.
  • CHECK: especifica una condición que se debe evaluar a "cierto".

De las restricciones, sólo vamos a utilizar, de momento, la clave primaria, que puede contener tantas columnas como se necesiten:

PRIMARY KEY (columna[,columna[, ...]])

Las siguientes órdenes crean las tablas PROFESORES y 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,
foreign key (asignatura) references asignaturas);



Motores de MySQL

Lo dicho en la sección anterior 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.

Por tanto, y puesto que queremos garantizar las restricciones de clave ajena, las definiciones anteriores no nos valen:



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;

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;



Es más, 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.

Drop table

Si queremos borrar una tabla debemos ordenárselo al SGBD mediante la orden DROP TABLE:

DROP TABLE nombreTabla


Borra la tabla asignaturas

drop table asignaturas


Al utilizar esta orden también se eliminan los datos (las filas) que pudiera contener (en este caso, ninguna). Se puede borrar y crear la tabla tantas veces como queramos.



Información del catálogo

Recordemos que ejecutar DESC nombreTabla o DESCRIBE nombreTabla, muestra información sobre las columnas que componen la tabla, el orden interno de las mismas en la tabla, y sus tipos de datos. La sentecia DESCRIBE es, en realidad, un atajo para acceder a las tablas del catálogo mediante una sentencia select

En el momento de crear una restricción, además de especificar las reglas que se deben cumplir, podemos dar un nombre a la misma. Para establecer los nombres de las restricciones, se suele seguir el siguiente convenio: ser nombres descriptivos, que empiecen, por ejemplo, por

  • PK_ si se trata de una Primary Key
  • FK_ si se trata de una Foreign Key
  • C_ si se trata de una CHECK

(Obviamente, este convenio es eso, una recomendación, al sistema le da igual el nombre que elijamos)

No vamos a profundizar más en los nombres de las restricciones ni en la sintaxis para crearlos, pero sí que debemos saber que si no especificamos ningún nombre, el sistema le asignará un nombre único a cada restricción con un formato propio.



create table imparte (
dni varchar(10),
asignatura char(5),
constraint PK_imparte primary key (dni,asignatura),
constraint FK_imparte_profesores foreign key (dni) references profesores (dni),
constraint FK_imparte_asignaturas foreign key (asignatura) 
        references asignaturas (codigo)) engine=innodb;



Manipulación de datos

Insert

Para introducir datos nuevos en una base de datos vamos a utilizar la orden INSERT de SQL. Con la sintaxis que se muestra a continuación seremos capaces de introducir datos nuevos en cualquiera de las tablas que componen una determinada BD. En principio, veremos la expresión mínima de la orden, formada por dos cláusulas, INTO y VALUES.


INSERT INTO nombreTabla VALUES ( listaExpresiones )


Alta un nuevo profesor con los siguientes datos:
  • dni 55555555
  • nombre PATRICIO MARTÍNEZ,
  • categoría TU
  • fecha de incorporación 01/01/2000
insert into profesores
values ('55555555','PATRICIO MARTINEZ','TU','2000-1-1')


Alta un nuevo profesor con los siguientes datos:
  • dni 66
  • nombre ERNESTO PEREZ
  • categoría ASO
  • fecha de incorporación 01/01/1985

 
insert into profesores
values ('66','ERNESTO PEREZ','ASO','1985-1-1');




El resultado que devuelve una orden INSERT, será siempre el número de filas insertadas, en el caso de que la ejecución haya sido correcta. Para los casos en que la ejecución de la sentencia viole alguna restricción de la BD y por tanto, su ejecución no sea correcta, el resultado indicará cuál es la restricción violada. El SGBD, cada vez que insertamos un nuevo dato en una tabla, se encarga de verificar las restricciones activas, en nuestro caso las claves primarias, que como sabemos, no admiten valores duplicados, ni valores nulos.


Alta un nuevo profesor con los siguientes datos:

  • dni 66
  • nombre JUAN JUANÍTEZ
  • categoría XXX
  • fecha de incorporación 01/01/1905
insert into profesores
values ('66','JUAN JUANITEZ','XXX','1905-1-1')

A veces no nos interesa o no podemos darle valor a todas y cada una de las columnas, o lo vamos a hacer en un orden distinto al que tienen las columnas en el create table que la definió. Especificar una lista de columnas antes de VALUES permite decirle al sistema qué columnas van a tener valor y cuál es.



Da de alta a un profesor con DNI 88888888 y nombre ARMANDO SUÁREZ




insert into profesores (dni, nombre)

values ('88888888', 'ARMANDO SUAREZ');




El sistema intentará asignar a las columnas no indicadas el valor por defecto, si se ha definido, o  valor nulo. 

Es recomendable acostumbrarse a poner siempre las columnas a las que se va a dar valor, sean o no todas las de la tabla. Las razones que lo aconsejan son:
  • No habrá que fijarse en si se va a dar valor a todas o sólo a alguna de las columnas para acomodar la sintaxis de la sentencia INSERT.
  • Si por alguna razón se modifica la estructura de una tabla, es decir, se añaden columnas nuevas, y tenemos costumbre de no indicar las columnas cuando se inserta valor a todas, con la modificación dejarán de funcionar las sentencias que tuviéramos escritas.

Existe la posibilidad de hacer uso del valor NULL, siempre que la columna lo admita. Aunque se suele simplificar por "ausencia de valor", recuérdese que NULL significa realmente ignorancia (no sabemos si tiene valor ni, si lo tuviera, cuál es). En cualquier caso, si la columna los admite se puede especificar en la orden de inserción:




insert into profesores (dni,nombre,categoría,ingreso)
values ('88888888','ARMANDO SUAREZ', NULL, NULL);




En realidad el efecto es el mismo que el de la orden anterior propuesta (ejecutarla ahora provocaría un error por duplicado en clave primaria).


Igualmente, el cambio de orden de las columnas se debe corresponder con la posición exacta del valor a asignar:



insert into profesores (categoria, dni, nombre)
values (NULL, '88888888','ARMANDO SUAREZ');




Inserción de más de una fila

Supongamos que en la BD Ejemplo existiera una tabla llamada OPTATIVAS que contuviera los códigos y los créditos de aquellas asignaturas de carácter optativo.

Vamos a crear dicha tabla, eligiendo como clave primaria el código de la asignatura y poniendo además otra restricción, que todas las filas tengan un valor no nulo en la columna créditos



create table optativas (
asignatura char (5),
creditos decimal(3,1) not null,
primary key (asignatura)) engine=innodb;



Existe la posibilidad de insertar el resultado de una consulta en lugar de indicar la lista concreta de valores a insertar. Esto nos permite insertar varias filas en una tabla con una sola operación, en concreto, tantas filas como tuplas devuelva la orden SELECT.

INSERT INTO nombreTabla [ ( listaColumnas ) ] consulta

Supongamos que serán optativas todas las asignaturas que tengan menos de 9 créditos. Se trata de introducir los códigos de dichas asignaturas en la tabla OPTATIVAS. En este caso, como ya tenemos las asignaturas en la tabla ASIGNATURAS, tenemos dos opciones. Una opción es, hacer la SELECT e ir haciendo las INSERT una a una, copiando los datos de las filas obtenidas. Otra opción es insertar en una sola operación el resultado de la SELECT en la tabla OPTATIVAS.


insert into optativas (asignatura, creditos)

select codigo, creditos from asignaturas where creditos < 9;



El resultado de la orden SELECT deberá ser coherente en cantidad de columnas y tipos de datos. Las siguientes órdenes provocarán errores de compilación:


insert into optativas (asignatura)
select codigo, creditos from asignaturas where creditos < 9;

insert into optativas (asignatura,creditos)
select codigo from asignaturas where creditos < 9;

insert into optativas (asignatura,creditos)
select dni,ingreso from profesores;




Este caso ya es diferente, hay que asegurarse de que el resto de columnas permiten la inserción:


insert into optativas (asignatura) select codigo from asignaturas where creditos < 9



La restricción NOT NULL sobre la columna creditos impide que se realice la inserción de filas, para asegurar la integridad de los datos, evitando que se pongan valores nulos en esa columna.


Delete

Antes de comenzar, limpiemos la base de datos


drop table if exists optativas;
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)) engine=innodb;

create table asignaturas (
codigo char(5),
descripcion varchar(35),
creditos decimal(3,1),
creditosp decimal(3,1),
primary key (codigo)) engine=innodb;

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 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 DELETE nos permite borrar las filas contenidas en una tabla.

DELETE [FROM] nombreTabla [WHERE condición]

Borrar todas las filas de la tabla IMPARTE

-- para ver lo que hay ahora
select * from imparte;

dni asignatura
21111222 DGBD
21111222 FBD
21333444 PC



delete from imparte;


-- para ver el resultado
select * from imparte;

dni asignatura

0 filas seleccionadas



Borra todas las asignaturas de menos de 5 créditos


delete from asignaturas where creditos < 5;



Para borrar filas de varias tablas habrá que ejecutar tantas sentencias DELETE como de tablas queramos borrar.

(Nota: en MySQL se puede borrar sobre varias tablas pero no se puede asegurar que otros SGBD lo permitan también)



Integridad referencial

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 integridad referencial es la propiedad de las claves ajenas que nos asegura que todas las referencias desde una hacia otra tabla son consistentes.

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:

 dni
 21111222
 21222333
 21333444

y los códigos de ASIGNATURAS:

 codigo
 DGBD
 FBD
 FP
 HI
 PC



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');

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;

 dni
asignatura
 21333444 
 DGBD

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 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';


Update

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 from asignaturas;

 codigo
creditos
 HI
 4.5
 FBD  6
 DGBD  6
 PC  6
 PC  9




update asignaturas set creditos = 0;


select codigo,creditos from asignaturas;


 codigo
creditos
 HI
 0
 FBD  0
 DGBD  0
 PC  0
 PC  0


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;



 codigo
creditos  creditosp
 HI
 4  2
 FBD  4  2
 DGBD  4  2
 PC  4  2
 PC  4  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:


Modifique la fecha de ingreso a 1 de enero de 2003 sólo a aquellos profesores cuya categoría sea TEU.


update profesores
set ingreso='01/01/2003'
where categoria = 'TEU';




Existe la posibilidad de modificar la información contenida en una tabla asignando como nuevo valor o valores, el resultado de una consulta.

El resultado de la consulta puede asignarse a una única columna o a una lista de columnas. En el primer caso, la sentencia SELECT sólo devolverá un valor (una fila y una columna) el cual debe coincidir en tipo de dato y longitud con el tipo de dato y longitud de la columna a la cual asignamos el valor.

Es importante que nos aseguremos de que la subconsulta devuelve un único valor y que éste sea consistente con el tipo de dato esperado.



update imparte 
set asignatura='BDA',
    dni = (select dni from profesores)
where asignatura like '%BD%';

ERROR:
la subconsulta de una sola fila devuelve más de una fila



Finalmente, podemos combinar todo lo visto en una única sentencia.

select * from asignaturas;

 codigo
 descripcion
creditos  creditosp
 HI
 HISTORIA DE LA INFORMATICA  4  2
 FBD  FUNDAMENTOS DE LAS BASES DE DATOS  4  2
 DGBD  DISEÑO Y GESTION DE BASES DE DATOS  4  2
 PC  PROGRAMACION CONCURRENTE  4  2
 FP  FUNDAMENTOS DE LA PROGRAMACION  4  2



update asignaturas
set creditos = 5,
(descripcion, creditosp) = (select nombre, 3 from profesores where dni = '21333444')
where codigo like '%BD%';



select * from asignaturas;

 codigo
 descripcion
creditos  creditosp
 HI
 HISTORIA DE LA INFORMATICA  4  2
 FBD  RAFAEL ROMERO  5  3
 DGBD  RAFAEL ROMERO  5  3
 PC  PROGRAMACION CONCURRENTE  4  2
 FP  FUNDAMENTOS DE LA PROGRAMACION  4  2


NOTA: hemos utilizado una constante en la subconsulta (el valor 3)


Integridad referencial

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';


select * from imparte;

dni asignatura
21111222 DGBD
21111222 FBD
21333444 PC


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';


select * from asignaturas;

 codigo
 descripcion
creditos  creditosp
 HI
 HISTORIA DE LA INFORMATICA  4  2
 FBD  RAFAEL ROMERO  5  3
 DGBD  RAFAEL ROMERO  5  3
 PC  PROGRAMACION CONCURRENTE  4  2
 FP  FUNDAMENTOS DE LA PROGRAMACION  4  2
 BD1  RAFAEL ROMERO
 5  3




-- 2. modificación de imparte
update imparte set asignatura = 'BD1' where asignatura = 'FBD';


select * from imparte;

dni asignatura
21111222 DGBD
21111222 BD1
21333444 PC




-- 3. borrado de la asignatura antigua
delete from asignaturas where codigo = 'FBD';


select * from asignaturas;

 codigo
 descripcion
creditos  creditosp
 HI
 HISTORIA DE LA INFORMATICA  4  2
 DGBD  RAFAEL ROMERO  5  3
 PC  PROGRAMACION CONCURRENTE  4  2
 FP  FUNDAMENTOS DE LA PROGRAMACION  4  2
 BD1  RAFAEL ROMERO
 5  3


MySQL y delete y update

Es importante comprender que cada producto se acerca y se aleja del estándar de SQL en función de sus objetivos y de su estado de desarrollo. Sin ir más lejos, en MySQL es posible realizar borrados y modificaciones de filas de varias tablas en una única orden, cosa que otros SGBD (Oracle por ejemplo) no permiten.

Por otro lado, la siguiente orden funciona en otros SGBD pero MySQL no permite modificar una tabla a partir de una subconsulta de la misma tabla; si es necesario hay que crear una tabla temporal donde almacenar primero el valor y utilizarlo después en el update. Si se accede a la base de datos a través de un lenguaje de programación, Php por ejemplo, ya sería más normal almacenar el valor en una variable y hacer la modificación en base al mismo.


Modica la fecha de ingreso de los profesores con categoría TEU para que coincida con la del profesor con DNI 21333444


select * from profesores;

 dni
nombre  categoria  ingreso
 21111222
 EVA GOMEZ  TEU
 2003-01-01
 21222333  MANUEL PALOMAR  TEU  2003-01-01
 21333444
 RAFAEL ROMERO  ASO6  1992-06-16




update profesores
set ingreso = (select ingreso from profesores where dni='21333444')
where categoria = 'TEU';


De esta orden se debería obtener:

 dni
nombre  categoria  ingreso
 21111222
 EVA GOMEZ  TEU
 1992-06-16
 21222333  MANUEL PALOMAR  TEU  1992-06-16
 21333444
 RAFAEL ROMERO  ASO6  1992-06-16

Sin embargo, MySQL devuelve un error con el mensaje, más o menos, "no se puede hacer un update de la misma tabla que se está consultando). Esto también ocurre con delete. La solución consiste en "engañar" a MySQL con una consulta temporal:


update profesores
set ingreso = (select ingreso
               from (select ingreso
                     from profesores
                     where dni='21333444') ttemp

              )
where categoria = 'TEU';


En realidad no es que estemos "engañando" a MySQL; como se puede ver en el tema de Vistas y tablas temporales, lo que estamos haciendo es generar una tabla temporal (ttemp) donde se copian los datos de PROFESORES que nos interesan.

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. 

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

codigodescripcioncreditoscreditosp
DGBDDISEÑO Y GESTION DE BASES DE DATOS6.03.0
FBDFUNDAMENTOS DE LAS BASES DE DATOS6.01.5
FPFUNDAMENTOS DE LA PROGRAMACION9.04.5
HIHISTORIA DE LA INFORMATICA4.5
PCPROGRAMACION CONCURRENTE6.01.5


dninombrecategoriaingreso
21111222EVA GOMEZTEU1993-10-01
21222333MANUEL PALOMARTEU1989-06-16
21333444RAFAEL ROMEROASO61992-06-16


dniasignatura
21111222DGBD
21111222FBD
21333444PC


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 

codigodescripcioncreditoscreditosp
DGBDDISEÑO Y GESTION DE BASES DE DATOS6.03.0
FBDFUNDAMENTOS DE LAS BASES DE DATOS6.01.5
FPFUNDAMENTOS DE LA PROGRAMACION9.04.5
PCPROGRAMACION CONCURRENTE6.01.5


dninombrecategoriaingreso
21111222EVA GOMEZTEU1993-10-01
21222333MANUEL PALOMARTEU1989-06-16
21333444RAFAEL ROMEROASO61992-06-16


dniasignatura
21111222DGBD
21111222FBD
21333444PC



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;


codigodescripcioncreditoscreditosp
DGBDDISEÑO Y GESTION DE BASES DE DATOS6.03.0
FBDFUNDAMENTOS DE LAS BASES DE DATOS6.01.5
FPFUNDAMENTOS DE LA PROGRAMACION9.04.5
HIHISTORIA DE LA INFORMATICA4.5
PCPROGRAMACION CONCURRENTE6.01.5


dninombrecategoriaingreso
21111222EVA GOMEZTEU1993-10-01
21222333MANUEL PALOMARTEU1989-06-16
21333444RAFAEL ROMEROASO61992-06-16


dniasignatura
21111222DGBD
21111222FBD
21333444PC


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';

codigodescripcioncreditoscreditosp
DGBDDISEÑO Y GESTION DE BASES DE DATOS6.03.0
FPFUNDAMENTOS DE LA PROGRAMACION9.04.5
PCPROGRAMACION CONCURRENTE6.01.5


dninombrecategoriaingreso
21111222EVA GOMEZTEU1993-10-01
21222333MANUEL PALOMARTEU1989-06-16
21333444RAFAEL ROMEROASO61992-06-16


dniasignatura
21111222DGBD
21333444PC


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


codigodescripcioncreditoscreditosp
DGBDDISEÑO Y GESTION DE BASES DE DATOS6.03.0
FPFUNDAMENTOS DE LA PROGRAMACION9.04.5
PCPROGRAMACION CONCURRENTE6.01.5


dninombrecategoriaingreso
21111222EVA GOMEZTEU1993-10-01
21333444RAFAEL ROMEROASO61992-06-16


dniasignatura
21111222DGBD
21333444PC




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;

codigodescripcioncreditoscreditosp
DGBDDISEÑO Y GESTION DE BASES DE DATOS6.03.0
FBDFUNDAMENTOS DE LAS BASES DE DATOS6.01.5
FPFUNDAMENTOS DE LA PROGRAMACION9.04.5
HIHISTORIA DE LA INFORMATICA4.5
PCPROGRAMACION CONCURRENTE6.01.5


dninombrecategoriaingreso
21111222EVA GOMEZTEU1993-10-01
21222333MANUEL PALOMARTEU1989-06-16
21333444RAFAEL ROMEROASO61992-06-16


fichadniasignatura
121111222DGBD
221111222FBD
321333444PC



delete from profesores where dni = '21111222';

codigodescripcioncreditoscreditosp
DGBDDISEÑO Y GESTION DE BASES DE DATOS6.03.0
FBDFUNDAMENTOS DE LAS BASES DE DATOS6.01.5
FPFUNDAMENTOS DE LA PROGRAMACION9.04.5
HIHISTORIA DE LA INFORMATICA4.5
PCPROGRAMACION CONCURRENTE6.01.5


dninombrecategoriaingreso
21222333MANUEL PALOMARTEU1989-06-16
21333444RAFAEL ROMEROASO61992-06-16


fichadniasignatura
1
DGBD
2
FBD
321333444PC




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;


codigodescripcioncreditoscreditosp
DGBDDISEÑO Y GESTION DE BASES DE DATOS6.03.0
FBDFUNDAMENTOS DE LAS BASES DE DATOS6.01.5
FPFUNDAMENTOS DE LA PROGRAMACION9.04.5
HIHISTORIA DE LA INFORMATICA4.5
PCPROGRAMACION CONCURRENTE6.01.5


dninombrecategoriaingreso
21111222EVA GOMEZTEU1993-10-01
21222333MANUEL PALOMARTEU1989-06-16
21333444RAFAEL ROMEROASO61992-06-16


dniasignatura
21111222DGBD
21111222FBD
21333444PC



update asignaturas set codigo = 'AAA' where codigo = 'FBD';
update profesores set dni = '33' where dni = '21111222';

codigodescripcioncreditoscreditosp
DGBDDISEÑO Y GESTION DE BASES DE DATOS6.03.0
AAAFUNDAMENTOS DE LAS BASES DE DATOS6.01.5
FPFUNDAMENTOS DE LA PROGRAMACION9.04.5
HIHISTORIA DE LA INFORMATICA4.5
PCPROGRAMACION CONCURRENTE6.01.5


dninombrecategoriaingreso
33EVA GOMEZTEU1993-10-01
21222333MANUEL PALOMARTEU1989-06-16
21333444RAFAEL ROMEROASO61992-06-16


dniasignatura
33DGBD
33AAA
21333444PC


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.