Inicio‎ > ‎SQL‎ > ‎Lliçons SQL‎ > ‎

T04 Definició i manipulació de dades


Definició de dades

Create table

La definició de taules és el primer pas en la creació d’una base de dades. El conjunt de descripcions de taules conforma l’esquema de base de dades i representa a un sistema d’informació concret.

Suposem que implementarem un esquema de base de dades relacionals de professors, assignatures (només és un llistat de professors i assignatures, sense relacions entre ells). En primer lloc hem de decidir quins són els atributs de cada un d’ells i els seus tipus de dades:

 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)


Per a complir amb les restriccions del model relacional, a més, hem de triar les claus primàries adequades : DNI per a profesores i codi per a asignaturas. Òbviament, la forma que tenen aquestes taules ha sigut una decisió nostra com a dissenyadors d’aquesta base de dades concreta, en una altra situació haguérem, probablement, decidit definir altres atributs i altres taules.


L’orde CREATE TABLE ens permet crear cada una de les taules necessàries per a la nostra base de dades:

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

 
La llista de columnes, en la seua forma més senzilla, és un conjunt d’expressions (tantes com a columnes desitgem, i separades per comes) del tipus:

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

La totalitat de tipus de dades que maneja MySQL, sent la majoria comuna amb lleugeres diferències a qualsevol motor de base de dades, es pot trobar en
http://dev.mysql.com/doc/refman/5.0/es/column-types.html .

Restriccions

Les restriccions són regles, que normalment s’estableixen en el moment de crear una taula, per a garantir la integritat de les dades.
 
Bàsicament, les restriccions obliguen a complir-se certes regles quan una fila és inserida, esborrada o modificada, de manera que l’operació es portarà a efecte només si es compleixen les restriccions definides en la taula.

Podem contemplar els següents tipus de restriccions d’integritat de dades:
  • NOT NULL: especifica que la columna no pot contenir un valor nul.
  • PRIMARY KEY: identifica de manera única a cada fila de la taula mitjançant una o diverses columnes, aquestes columnes que formen la clau primària no poden tenir valors nuls.
  • FOREIGN KEY: estableix una relació entre una/unes columna/columnes de la taula i una altra/altres columna/columnes de la taula referenciada, sent aquesta última/últimes columna/columnes la PRIMARY KEY
  • UNIQUE: no permet duplicats; combinat amb NOT NULL és la forma de definir una clau alternativa
  • CHECK: especifica una condició que s’ha d’avaluar a "cert".

De les restriccions, només utilitzarem, de moment, la clau primària, que pot contenir tantes columnes com es necessiten:

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

Les següents ordes creen les taules PROFESORES i 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);



Motors de MySQL

Allò que s’ha dit en la secció anterior constitueix l’estàndard de SQL i és la sintaxi admesa en els SGBD de major penetració comercial.

No obstant, MySQL és un producte peculiar ja que ofereix diverses opcions d’emmagatzemament i gestió de les taules en aras d’oferir alternatives que milloren el rendiment o la integritat de dades. En particular podem triar entre taules MyISAM i InnoDB.

Si volem mantenir integritat referencial entre les nostres taules és imprescindible que especifiquem que volem usar el motor InnoDB. En una instal·lació estàndard de MySQL, si no diem res, la creació d’una taula es fa per defecte en MyISAM i té un efecte importantíssim: si definim claus forànees, el sistema les ignora i no revisa la integritat referencial.

Per tant, i com que volem garantir les restriccions de clau forànea, les definicions anteriors no ens 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;



És més, totes les taules implicades en una clau forànea han d’estar definides en aquest motor, tant la que allotja a la clau forànea com a la que fa refencia, i és obligatori indicar la columna o columnes que constitueixen la clau primària en aquesta segona.

Drop table

Si volem esborrar una taula hem d’ordenar-li’l al SGBD mitjançant l’orde DROP TABLE:

DROP TABLE nombreTabla


Esborra la taula asignaturas

drop table asignaturas


En utilitzar aquesta orde també s’eliminen les dades (les files) que poguera contenir (en aquest cas, cap). Es pot esborrar i crear la taula tantes vegades com vullguem.



Informació del catàleg

Recordem que executar DESC nombreTabla o DESCRIBE nombreTabla, mostra informació sobre les columnes que componen la taula, l’orde intern de les mateixes en la taula, i els seus tipus de dades.

En el moment de crear una restricció, a més d’especificar les regles que s’han de complir, podem donar un nom a la mateixa. Per a establir els noms de les restriccions, se sol seguir el conveni següent: ser noms descriptius, que comencen, per exemple, per
  • PK_ si es tracta d'una Primary Key
  • FK_ si es tracta d'una Foreign Key
  • C_ si es tracta d'una CHECK
(Òbviament, aquest conveni és això, una recomanació, al sistema li dóna igual el nom que triem)

No aprofundirem més en els noms de les restriccions ni en la sintaxi per a crear-los, però sí que hem de saber que si no especifiquem cap nom, el sistema li assignarà un nom únic a cada restricció amb un format propi.

La sentència DESCRIU és, en realitat, un drecera per a accedir a les taules del catàleg mitjançant una sentència select.
 

Manipulació de dades

Insert


Per a introduir dades noves en una base de dades utilitzarem l’orde INSERT de SQL. Amb la sintaxi que es mostra a continuació serem capaços d’introduir dades noves en qualsevol de les taules que componen una determinada BD. En principi, veurem l’expressió mínima de l’orde, formada per dues clàusules , INTO i VALUES.

INSERT INTO nombreTabla VALUES ( listaExpresiones )


Alta un nou professor amb les següents dades:
  • dni 55555555
  • nom PATRICIO MARTÍNEZ,
  • categoria TU
  • data d'incorporació 01/01/2000
insert into profesores
values ('55555555','PATRICIO MARTINEZ','TU','2000-1-1')


Alta un nou professor amb les següents dades:
  • dni 66
  • nom ERNESTO PEREZ
  • categoria ASO
  • data d'incorporació 01/01/1985

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




El resultat que torna una orde INSERT, serà sempre el nombre de files inserides, en el cas que l’execució haja sigut correcta. Per als casos en què l’execució de la sentència viole alguna restricció de la BD i per tant, la seua execució no siga correcta, el resultat indicarà quina és la restricció violada. El SGBD, cada vegada que inserim una nou dada en una taula, s’encarrega de verificar les restriccions actives, en el nostre cas les claus primàries, que com sabem, no admeten valors duplicats, ni valors nuls.


Alta un nou profesor amb les següents dades:

  • dni 66
  • nom JUAN JUANÍTEZ
  • categoria XXX
  • data d'incorporació 01/01/1905
insert into profesores
values ('66','JUAN JUANITEZ','XXX','1905-1-1')

A vegades no ens interessa o no podem donar-li valor a totes i cada una de les columnes, o ho anem a fer en un orde distint a què tenen les columnes en el create table que la va definir. Especificar una llista de columnes abans de VALUES permet dir-li al sistema quines columnes tindran valor i quin és.


Dóna d’alta a un professor amb DNI 88888888 i nom ARMANDO SUÁREZ




insert into profesores (dni, nombre)

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




El sistema intentarà assignar a les columnes no indicades el valor per defecte, si s’ha definit, o valor nul.

És recomanable acostumar-se a posar sempre les columnes a què es va a donar valor, siguen o no totes les de la taula. Les raons que ho aconsellen són:

  • No caldrà fixar-se en si es va a donar valor a totes o només a alguna de les columnes per a acomodar la sintaxi de la sentència INSERT
  • Si per alguna raó es modifica l’estructura d’una taula, és a dir, s’afigen columnes noves, i tenim costum de no indicar les columnes quan s’insereix valor a totes, amb la modificació deixaran de funcionar les sentències que tinguérem escrites.
Hi ha la possibilitat de fer ús del valor NULL, sempre que la columna l'admeta. Encara que se sol simplificar per "absència de valor", recorde’s que NULL significa realment ignorància (no sabem si té valor ni, si el tinguera, quin és). En tot cas, si la columna l'admet es pot especificar en l’orde d’inserció:



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




En realitat l’efecte és el mateix que el de l’orde anterior proposta (executar-la ara provocaria un error per duplicat en clau primària).


Igualment, el canvi d’orde de les columnes s’ha de correspondre amb la posició exacta del valor a assignar:



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




Inserció de més d'una fila

Suposem que en la BD Ejemplo existira una taula anomenada OPTATIVES que continguera els codis i els crèdits d’aquelles assignatures de caràcter optatiu.

Crearem la taula, triant com a clau primària el codi de l’assignatura i posant a més una altra restricció, que totes les files tinguen un valor no nul en la columna creditos



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



Hi ha la possibilitat d’inserir el resultat d’una consulta en compte d’indicar la llista concreta de valors a inserir. Açò ens permet inserir unes quantes files en una taula amb una sola operació, en concret, tantes files com tuples torne l’orde SELECT.

INSERT INTO nombreTabla [ ( listaColumnas ) ] consulta

Suposem que seran optatives totes les assignatures que tinguen menys de 9 crèdits. Es tracta d’introduir els codis de les assignatures en la taula OPTATIVAS. En aquest cas, com ja tenim les assignatures en la taula ASIGNATURAS, tenim dues opcions. Una opció és, fer la SELECT i anar fent les INSERT una a una, copiant les dades de les files obtingudes. Una altra opció és inserir en una sola operació el resultat de la SELECT en la taula OPTATIVAS.


insert into optativas (asignatura, creditos)

select codigo, creditos from asignaturas where creditos < 9;



El resultat de l’orde SELECT haurà de ser coherent en quantitat de columnes i tipus de dades. Les següents ordes provocaran errors de compilació:


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;




Aquest cas ja és diferent, cal assegurar-se de que la resta de columnes permeten la inserció:


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



La restricció NOT NULL sobre la columna creditos impedeix que es realitze la inserció de files, per a assegurar la integritat de les dades, evitant que es posen valors nuls en aquesta columna.


Delete

Abans de començar, netegem la base de dades


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 sentència DELETE ens permet esborrar les files contingudes en una taula.

DELETE [FROM] nombreTabla [WHERE condición]

Esborra totes les files de la taula IMPARTE

-- per a veure el qui hi ha ara
select * from imparte;

dni asignatura
21111222 DGBD
21111222 FBD
21333444 PC



delete from imparte;


-- per a veure el resultat
select * from imparte;

dni asignatura

0 filas seleccionadas



Esborra totes les assignatures de menys de 5 crèdits


delete from asignaturas where creditos < 5;



Per a esborrar files d’unes quantes taules caldrà executar tantes sentències DELETE com de taules vullguem esborrar.

(Nota: en MySQL es pot esborrar sobre unes quantes taules però no es pot assegurar que altres SGBD ho permeten també)



Integritat referencial

Abans de començar, reconstruïm la base de dades:


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 integritat referencial és la propietat de les claus alienes que ens assegura que totes les referències des d’una cap a una altra taula són consistents.

La taula imparte relaciona professors i assignatures informant de quins professors imparteixen quina assignatura:

    IMPARTE ( dni : varchar(10), asignatura : char(5) )
    Clave primaria: (dni, asignatura)
    Clave ajena: dni → PROFESORES
    Clave ajena: asignatura → ASIGNATURAS


La integritat referencial exigeix que el valor que tinga la columna dni existisca en la taula PROFESORES; de la mateixa manera cada codi d’assignatura haurà d’existir en la taula ASIGNATURAS.

En aquest moment, els DNI emmagatzemats en PROFESORES són:

 dni
 21111222
 21222333
 21333444

i els codis de ASIGNATURAS:

 codigo
 DGBD
 FBD
 FP
 HI
 PC



Insereix la informació que el professor identificat com 55555555 imparteix l’assignatura identificada com 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`))



Insereix la informació que el professor identificat com 21333444 imparteix l’assignatura identificada com DGBD (valors que sí que existeixen)


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


select * from imparte;

 dni
asignatura
 21333444 
 DGBD

Només es podran esborrar aquelles files que no estiguen sent referenciades, a través de cap clau forànea, des d’una altra taula.

Per exemple l’assignatura DGBD és impartida pels professors amb dni 21111222 i 21333444 (consulta la taula de l’anterior resultat): no es podrà esborrar l’assignatura si abans no s’eliminen les files corresponents a aquesta assignatura en la taula 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`))



Eliminant les referències que ens impedeixen l’esborrament:


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

Abans de començar, reconstruïm la base de dades:



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 sentència UPDATE ens permet modificar la informació continguda en una taula.

UPDATE nombreTabla [aliasTabla]
SET { {columna=expresion | columna=subconsulta} | listaColumnas=subconsulta}
[WHERE condición]

No es poden modificar unes quantes taules al mateix temps en una mateixa sentència. Per a modificar els valors d’unes quantes taules diverses caldrà executar tantes sentències UPDATE com a taules vullguem modificar. (NOTA: igual que amb delete MySQL sí que proporciona sintaxi específica per a poder fer-ho però altres 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


Quan es desitja modificar més d’una columna s’indicarà la llista de columnes i valors separats per comes:


Modifica els crèdits de les assignatures a 4, i els crèdits pràctics 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 cas que s’indique alguna condició, es modificaran només aquelles files de la taula que complisquen la condició o condicions imposades:


Modifique la data d’ingrés a 1 de gener de 2003 només a aquells professors la categoria dels quals siga TEU.


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




Hi ha la possibilitat de modificar la informació continguda en una taula assignant com nou valor o valors, el resultat d’una consulta.

El resultat de la consulta pot assignar-se a una única columna o a una llista de columnes. En el primer cas, la sentència SELECT només tornarà un valor (una fila i una columna) el qual ha de coincidir en tipus de dada i longitud amb el tipus de dada i longitud de la columna a la qual assignem el valor.


És important que ens assegurem de que la subconsulta torna un únic valor i que aquest siga consistent amb el tipus de dada esperat.


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



Finalment, podem combinar tot allò que s’ha vist en una única sentència.

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: hem utilitzat una constant en la subconsulta (el valor 3)

Integritat referencial

En general, les claus forànees generen les mateixes restriccions d’integritat referencial que les vistes per al DELETE excepte per la naturalesa de l’operació: l’UPDATE només generarà problemes d’integritat referencial si la dada a modificar és un valor de clau primària que està sent referenciada per alguna clau forànea.

La següent orde generaria un problema amb la integritat referencial.


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


select * from imparte;

dni asignatura
21111222 DGBD
21111222 FBD
21333444 PC


Per a poder realitzar aquesta operació, serà necessari inserir una nova fila en assignatures amb l’identificador BD1 i copiant la resta dels valors, després canviar les referències a FBD per BD1 i, finalment, esborrar la fila de FBD.



-- 1. nova assignatura amb les dades de l'antiga
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ó de imparte
update imparte set asignatura = 'BD1' where asignatura = 'FBD';


select * from imparte;

dni asignatura
21111222 DGBD
21111222 BD1
21333444 PC




-- 3. esborrament de l'assignatura antiga
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 i delete i update

És important comprendre que cada producte s’acosta i s’allunya de l’estàndard de SQL en funció dels seus objectius i del seu estat de desenrotllament. Sense anar més lluny, en MySQL és possible realitzar esborraments i modificacions de files d’unes quantes taules en una única orde, cosa que altres SGBD (Oracle per exemple) no permeten.

D’altra banda, la següent orde funciona en altres SGBD però MySQL no permet modificar una taula a partir d’una subconsulta de la mateixa taula; si és necessari cal crear una taula temporal on emmagatzemar primer el valor i utilitzar-lo després en l’update. Si s’accedeix a la base de dades a través d’un llenguatge de programació, Php per exemple, ja seria més normal emmagatzemar el valor en una variable i fer la modificació basant-se en el mateix.
 
 
 
Modifica la data d’ingrés dels professors amb categoria TEU perquè coincidisca amb la del professor amb 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';


D’aquesta orde s’hauria d’obtenir:

 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

No obstant, MySQL torna un error de compilació.






Les opcions per a mantenir la integritat referencial


Ja s’ha practicat amb la integritat referencial en sessions anteriors i s’ha vist que l’intent d’esborrar certes files és rebutjat pel SGBD si aquestes estan sent referenciades per alguna clau forànea. El sistema està vetlant per la integritat dels dades emmagatzemades.
 
No obstant, és possible automatitzar i preveure aquestes situacions expressant en l’esquema de la base de dades nostra voluntat de propagar les operacions d’esborrament de files i d’actualització de valors de clau primària fins on faça falta. Abans de mostrar aquestes opcions, el següent exemple ens recorda les restriccions que imposa l’exigir integritat referencial a les claus forànees. 

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



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

L’execució fallarà perquè hi ha professors que imparteixen l’assignatura FBD.



delete from asignaturas where codigo = 'HI';

Es compleix l’orde perquè ningú imparteix HI.




select * from asignaturas;
select * from profesores;
select * from imparte;;

**************** estat 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 certs sistemes d’informació és possible redefinir les restriccions de clau forànea perquè no es donen aquests missatges d’error. Això és possible mitjançant la clàusula ON DELETE en crear una taula: 

FOREIGN KEY (columna[,columna[, ...]]) REFERENCES tabla (clave primària)
ON DELETE {CASCADE | SET NULL}
 

L’acció a realitzar davant de l’esborrament d’una fila que està sent referenciada per alguna clau forànea pot ser el propagar l’operació (ON DELETE CASCADE) o anul·lar (ON DELETE SET NULL), depenent de la decisió del dissenyador de la base de dades. 

Vegem ara l’efecte d’utilitzar l’opció 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


L’esborrament de FBD provoca esborraments automàtics en IMPARTEIX i l’esborrament d’HI segueix sense generar problemes 

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


No obstant això, en la CAj a PROFESSORS no hem definit cap mètode per a mantenir la IR. 

delete from profesores where dni = '21111222';
Falla perquè aquest professor imparteix DGBD 

delete from profesores where dni = '21222333';
Èxit ja que no imparteix cap assignatura 


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




Anul·lar


Si la modificació d’una clau forànea és ON DELETE SET NULL, l’acció que durà a terme automàticament el SGBD és la de posar NULS en aquells casos en què la integritat referencial es veja compromesa. 

Aquesta definició té més dificultat d’aplicació ja que prevalen les definicions de VALOR NO NUL. Per exemple, és inútil utilitzar-la en IMPARTE.DNI ja que forma part de la clau primària i no admet nuls en cap cas. Canviarem l’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;


Les claus forànees, ara, permeten nuls. 

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ó ON UPDATE es regeix pels mateixos paràmetres que l’anterior, es pot triar entre propagar o anul·lar (o rebutjar, si no definim res). 

Note’s que es poden fer totes les combinacions possibles en totes les claus forànees, no necessàriament ha de ser totes propagar o totes anul·lar, ni tampoc tenen per què coincidir en la mateixa clau forànea. 


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


Òbviament, es pot establir qualsevol combinació de mètodes que vulguem utilitzar, com per exemple

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 l'exemple anterior s'utilitzen REBUTJAR, PROPAGAR I ANUL·LAR entre les dues claus alienes de la taula.