Inicio‎ > ‎SQL‎ > ‎SQL (english)‎ > ‎

T04 Data Definition and Manipulation

 
 

Data definiton

Create table

The definition of tables is the first step in the building of a database. The set of table descriptions makes up the schema of the database and it is representing a specific information system

Supposing we want to implement a relational database schema for teachers, subjects (just a listing of teachers and subjects, without relationship between them). Firstly, we must decide the attributes of each table and the data type of these attributes:

 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)


Besides, in order to fulfill the restrictions of the relational model, we must choose the appropriate primary keys: DNI for profesores and código for asignaturas. Obviously, the way we build these tables is our decision as database designers. Maybe, in other situation, the definition of the tables could be different.


The statement CREATE TABLE allows us to create each table we need for our database:

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

 
The list of columns, in its simplest form, is a set of expressions (as many as columns we want, and separated with commans) like:

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

All the types managed by MySQL, being most of them common between database engines except slight differences, can be found in http://dev.mysql.com/doc/refman/5.0/es/column-types.html .

Restrictions

Restrictions are rules, usually established in the moment of the creation of a table, to guarantee the integrity of data.

Basically, restrictions oblide to follow certain rules when a row is inserted, deleted or updated, and the operation will be performed only if the restrictions of the table are fulfilled.

We have the following types of restrictions for data integrity:
  • NOT NULL: specifies that the column must have a value.
  • PRIMARY KEY: identifies each row uniquely using one or more columns, the columns that are part of a primary key can not have null value.
  • FOREIGN KEY: establishes a relation between one/more column(s) of a table and another column(s) of the referenced table, being this/these last column(s) the PRIMARY KEY.
  • CHECK: specifies a condition that must be evaluated as "true" .

 

                    From these restrictions, only the one referring the primary key, containing as many columns as necessary, will be used:

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

The following sentences create PROFESORES and ASIGNATURAS tables


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



Engines of MySQL

What was shown in previous section is the standard of SQL and its syntax is admitted by most of the commercial DBMS

However, MySQL is a peculiar product that offers various storing and management options in order to offer alternatives to improve performance and data integrity. In particular, we can choose between tables  MyISAM and InnoDB.

If we want to keep referential integrity between our tables is necessary to specify the engine InnoDB. In a standard instalation of MySQL, if we don´t say anything, the creation of the table is done using MyISAM and it has a very important effect: if we define foreign keys, the system ignores them and does not review the referential integrity.

Therefore, and due to the fact that we want to preserve foreign key restrictions, the previous definition are not valid for us:



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;



Furthermore, all the involved tables in a foreign key must be defined in this engine, not only the one with the foreign key but also the one being referred, and it is compulsory to indicate the column or columns that belongs to the primary key in this second engine.

Drop table

If we want to delete a table, we must order it to the DBMS with the statement DROP TABLE:

DROP TABLE nombreTabla


Delete the table asignaturas

drop table asignaturas


When using this statement, the data or rows that the table contains are also deleted. It is possible to delete and create a table as many times as we want.



Catalog information

Remember that executing DESC nombreTabla or DESCRIBE nombreTabla, all the information related to the columns of the table must be shown, the internal order of them in the table, and their data types.

In the moment of creating a restriction, apart from specifying the rules that must be fulfilled, we can give it a name. To establish the names of the restrictions, the following agreement is used: being descriptive names, starting with, for example,
  • PK_ in case of Primary Key
  • FK_ in case of Foreign Key
  • C_ in case of CHECK

(Obviously, this agreement is only a recommendation)

We are not going to study in depth the name of the restrictions nor the syntax to create them, but we need to know that if we don't assign them a name, the DBMS will do.

The statement DESC is a shortcut of a select order that accesses the data dictionary.


Data manipulation

Insert


In order to insert new data in a database we will use the statement INSERT of SQL. With the syntax shown next we will be able to add new data in each table of a database. At first, we will see the minumum statement syntax, formed by INTO and VALUES clauses.

INSERT INTO nombreTabla VALUES ( listaExpresiones )


Add a new teacher with the following data:
  • 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')


Add a new teacher with the following data:
  • 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');




The result of the statement INSERT, will always be the number of added rows, in case of a properly execution. For the cases in the statement breaking any restriction of the database, and therefore, the execution is not correct, the result will indicate which restriction has been broken. The DBMS, each time we add a new data in a table, is in charge of checking the active restrictions, in our case, the primary keys, that as we already know, do not admit nulls or duplicates.


Add a new teacher with the following data:

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

Sometimes we do not want or we can't have values for all the columns, or we want to use a different order that the one defined in the table. Specifying a list of columns before the VALUES allows to indicate the system which columns will have a value and which is this value.


Add a new teacher with  DNI 88888888 and nombre ARMANDO SUÁREZ




insert into profesores (dni, nombre)

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




The system will try to assign to the non indicate columns a default value, or in case it was defined, a null value. 

It is advisable to indicate always the columns with values, being all of them or not part of the table. The reason are:
  • There is no need to take into account if we are giving values to all of the columns or only some of them in the INSERT statement.
  • If the structure of the table is modified by any reason, for example new columns are added, and we usually do not indicate the name of the columns, the statement won't work anymore
There exists the possibility of using NULL value, when the column admits it. Although it is usually simplified as "absence of value", remember that NULL means unkown data (we don`t know if it has a value and in case it has some we don't know it). In any case, if the column admits them it can be specified in the INSERT sentence:



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




Actually, the effect is the same as in the previous statement (executing this sentence will provoke an error due to duplicity in the primary key).


Equally, a change in the order of the columns must correspond with the exact position of the value to be assigned:



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




Adding more than one row

 

Suppose that in the BD Ejemplo there exists a table called OPTATIVAS with the code and credits of the optative subjects.
We will create this table, choosing the code of the subject as primary key and adding the restriction that all the rows must have a value in the credits column.

 



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



There exists the possibility to insert the result of a column instead of indicating a list of values to be inserted. This allows us to insert more than one row in a table in a unique operation, specifically, as many rows as tuples are returned in the SELECT statement.

INSERT INTO nombreTabla [ ( listaColumnas ) ] consulta

 

 

 

Suppose that all the subjects with less than 9 credits are optative. We need to insert the code of these subjects in the table OPTATIVAS. In this case, as we have the table ASIGNATURAS, we have to possible options. One option is performing the SELECT and introducing one by one the subjects using INSERT, copying the data of the rows obtained. The other option is inserting the result of the SELECT in a unique operation in the table OPTATIVAS. 

 


insert into optativas (asignatura, creditos)

select codigo, creditos from asignaturas where creditos < 9;



The result fo the SELECT statement must be coherent with the number of columns and the data types. The following statement will provoke compiling errors:


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;




This case is different, because we need to assure that the rest of the columns allows this insertion:


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


The restriction NOT NULL  over the credit columns prevent to perform the insertion of rows, in order to assure the integrity of the data, avoiding having nulls in this column.


Delete

Before starting, we need to clean the database


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



The DELETE sentece allows us to delete the rows of the table.

DELETE [FROM] nombreTabla [WHERE condición]

Delete all the rows from IMPARTE table

-- to see what we have in the table now
select * from imparte;

dni asignatura
21111222 DGBD
21111222 FBD
21333444 PC



delete from imparte;


--to see the result 
select * from imparte;

dni asignatura

0 rows selected

Delete all the subjects with less than 5 credits

delete from asignaturas where creditos < 5;



In order to delete rows of various tables we need to execute as many DELETE statements as rows we want to delete.

(Note: in MySQL is possible to delete over various tables but it can be assure that other DBMS allows that also)



Referential integrity 

Before starting, we will rebuild the database:


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




The referential integrity is a property of all the foreign keys in order to assure that all the references from one table to another are consistent.

The table imparte relates teachers and subjects informing what teachers teach what subject:

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


The referential integrity requires that the value of the column dni exists in the table PROFESORES, as well as the code of the subject must exists in the table ASIGNATURAS.

In this moment, the stored DNI's in PROFESORES are:
 dni
 21111222
 21222333
 21333444

and the codes in ASIGNATURAS:

 codigo
 DGBD
 FBD
 FP
 HI
 PC



Insert the information of the teacher identified as 55555555 that teach the subject identified as 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`))



Insert the information of the teacher identified as 21333444 that teach the subject identified as DGBD (these are values that exist)


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


select * from imparte;

 dni
asignatura
 21333444 
 DGBD

Only that rows are not not being referenced can be deleted.

For example, the subject DGBD is teached by the teachers with dni 21111222 and 21333444: it is not possible to delete the subject if the references of this subject are not previously deleted in 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`))



We delete the references that prevent the delete of the subject:


-- we delete the refences of imparte

delete from imparte where asignatura='DGBD';

-- now you can delete the subject DGBD
-- because there is no foreign key being referred

delete from asignaturas where codigo='DGBD';


Update

Before starting, we need to rebuild the database:


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




The statement UPDATE will allow us to modify the information stored in a table.

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

It is not possible to modify more than one table in a single sentence. To modify the values of various tables it is necessary to perform as many UPDATE sentences as tables. (NOTE:the same as with delete ).


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


When we want to modify more than one column, the list of columns with the new value will be indicated separated with commas:


Modify the credits of the subjects to 4, and the practical credits to 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



In case a condition is indicated, only the rows that fulfill the condition/s will be updated:


Modify the date of ingreso to 1 de enero de 2003 only for those teacher with TEU category.


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




There exists the possibility of modifying the information stored in a table assigning as new value/s the result of a query.

The result of the query can be assign to one single column or a list of columns. In the first case, the SELECT statement will only return one value that must match with the data type of the column being assigned.


It is important to assure that the subselect returns only one value and this value matches with the expected data type.


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

ERROR:
the subquery returns more than one row



Finally, we can combine everything in just one statement.

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


NOTE: we have used a constant in the subquery (the value 3)
 
 
 

Referential integrity 

 
 
In general, the foreign keys generate the same integrity restrictions of the ones shown for DELETE, except for the ones related with the specific characteristics of UPDATE: the UPDATE only generates problems of referential integrity if the data that is going to be modified is a value of the primary key in the table being referenced by any foreign key.

The following statement will provoke a problem with referential integrity.


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


select * from imparte;

dni asignatura
21111222 DGBD
21111222 FBD
21333444 PC


In order to perform this operation, it will be necessary to insert a new row in the table asignatures with the identifier BD1 and copy the rest of the values, after that, change the references of FBD with BD1 and finally delete FBD.



-- 1. new subject with the data of the all one
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. modification of imparte
update imparte set asignatura = 'BD1' where asignatura = 'FBD';


select * from imparte;

dni asignatura
21111222 DGBD
21111222 BD1
21333444 PC




-- 3. deleting the all subject
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 and delete and update

It is important to understand that every product, depending on its objectives, is closer or more distant to the SQL standard. For example, in MySQL, it is possible to perform deletes and updatings of the rows of various tables in a single statement, and this is not possible in other DBMS.

On the other hand, the following statement will work in others DBMS but not in MySQL, which is not allowing updating a table from a subquery of the same table, we need to use a temporal table to perform this type of operation. If we access to the database using a programming language, PHP for example, it would be more common to store the value in a variable and modifying the table with this.

Modify the date of enrollment of the teachers with TEU category in order to have the same as the teacher with 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';


Form this statement we must obtain:

 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

However, MySQL returns a compiling error.


Options of the referential integrity

In previous sessions, some practical exercises with referential integrity have been done, and we saw that sometimes deleting is rejected by the DBMS if these are being referenced by a foreighn key.

Next example reminds us the restrictions imposed by the referential integrity of foreign keys.

-- use your database

select '****************    Preparing the BD MiEjemplo...' action from dual;

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),
constraint fk_imparte_profe foreign key (dni) references PROFESORES (dni),
constraint fk_imparte_asig foreign key (asignatura) references ASIGNATURAS (codigo)) engine=innodb;

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



select '****************    estado de BD INICIAL' estado from dual;

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

select 'acción';
select '-------------------------------------------------';
select '>>delete from asignaturas2 where codigo = \'FBD\'' ;
delete from asignaturas where codigo = 'FBD';
select 'porqué';
select '-------------------------------------------------';
select '****************    Hay profesores que imparten la asignatura FBD';

select 'acción';
select '-------------------------------------------------';
select '>>delete from asignaturas where codigo = \'HI\'';
delete from asignaturas where codigo = 'HI';
select 'porqué';
select '-------------------------------------------------';
select '****************    Nadie imparte HI y se puede borrar';

select '****************    estado de la BD FINAL' estado from dual;

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


We will obtain the following result (summarized):


**************** INITIAL DB state 

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


action
----------------------------------------------
>>delete from asignaturas where codigo = 'FBD'

Error starting at line 37 in command:
delete from asignaturas where codigo = 'FBD'
Error at Command Line:37 Column:0
Error report:
SQL Error: Cannot delete or update a parent row: a foreign key constraint fails (`miejemplo/imparte`, CONSTRAINT `fk_imparte_asig` FOREIGN KEY (`asignatura`) REFERENCES `asignaturas` (`codigo`))

why
------------------------------------------------------
**************** There are teachers of FBD

action
---------------------------------------------
>>delete from asignaturas where codigo = 'HI'  

delete from asignaturas where codigo = 'HI' succeeded.

why
------------------------------------------------------
**************** No one teaches HI and it can be deleted


**************** FINAL DB state

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

Cascade


In certain systems it is possible to redefine the restrictions the foreign key in order to avoid the error messages. This is possible using the clause ON DELETE when creating the table:

FOREIGN KEY (columna[,columna[, ...]]) REFERENCES tabla (primary key)
ON DELETE {CASCADE | SET NULL}


The action to perfom when we want to delete a row being referenced by a foreign key can be the propagation of the operation (ON DELETE CASCADE) or set null (ON DELETE SET NULL), depending on the decision of the DB designer.

Now we will see the effect of using the option 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),
constraint fk_imparte_profe foreign key (dni) references profesores(dni),
constraint fk_imparte_asig 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


Deleting FBD has the effect of automatically propagating the operation to IMPARTE and deleting HI has also no problem.

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


Nevertheless, FK to PROFESORES has no method to maintain RI.

delete from profesores where dni = '21111222';
Fails because this teacher is associated to DGBD.

delete from profesores where dni = '21222333';
Success because he has no lecture.


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






Set Null


If the modification of a foreign key is ON DELETE SET NULL, the action that will be automatically performed is setting NULLS in those cases where the referential integrity is being compromised.

This definition has a more difficult application due to the fact that NULL value definitions prevails. For example, it is useless using it in IMPARTE.DNI since this is part of a primary key that does not admit null values in any case.

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),
constraint fk_imparte_profe foreign key (dni) references profesores (dni) ON DELETE SET NULL,
constraint fk_imparte_asig foreign key (asignatura) references asignaturas (codigo) ) engine=innodb;

Now, foreign keys allow NULL.

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



fichadniasignatura
1
DGBD
2
FBD
321333444PC



On update

ON UPDATE definition follows the same rules than ON DELETE, it could be chosen both CASCADE or SET NULL (or reject, if no definition is given).

Noticed that all combinatios are possible in all foreign keys, and even not the same for deleting and updating in a given FK.


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),
constraint fk_imparte_profe foreign key (dni) references profesores(dni)
ON DELETE CASCADE ON UPDATE CASCADE ,
constraint fk_imparte_asig 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



Obviously, any combination of methods can be used,

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;

In the previous example, REJECT, CASCADE and SET NULL were defined among the two foreign keys.
Comments