Referential integrity

When we define foreign keys in a table, we are telling the database server to "watch" the values we store in them. Referential integrity is the property of foreign keys that ensures that all references from one foreign key to another table are consistent. Basically, a foreign key can only hold null values or values that are previously stored in the primary key they point to.

In general, the operation we intend to perform, deleting the DGBD row, would be invalidated by the database engine itself, which monitors the referential integrity. If we wanted to delete the subject from our database, we would first have to delete any reference to it, that is, we would have to delete the IMPARTE rows where the DGBD value appears in the subject column.

But we could also foresee the situation and establish an automatic response from the database server, for example, that it would automatically search, find and delete those rows of IMPARTE to be able to execute the definitive deletion of the subject. In short, when we define a foreign key, we can make it behave as follows:

If a row is deleted in the table pointed to by the foreign key with a value used in that foreign key:

• REJECT the delete operation, this is the default option if nothing is added to the foreign key definition.

• CASCADE the deletion operation

• SET NULL the value, modify it to null.

The same options are available for modifying primary key values that are being used in the foreign key.

To achieve these behaviours, appropriate modifiers must be added to the foreign key definition:

FOREIGN KEY (column) references tableName (columns) [ON DELETE {cascade | set null} ] [ON UPDATE {cascade | set null} ]

That is, on delete and on update are both optional, and each allow one of two methods. If nothing is said about either of them, the server will reject operations that potentially cause referential inconsistency.

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.

Insert the information of the teacher identified as 55555555 that teach the subject identified as AAA

insert into imparte (dni, asignatura)

values ('55555555','AAA');

Error:

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

Deleting

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

Updating

For UPDATE, 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';

/* Error de SQL (1451): Cannot delete or update a parent row: a foreign key constraint fails (`xxxx`.`imparte`, CONSTRAINT `FK_asig` FOREIGN KEY (`asignatura`) REFERENCES `asignaturas` (`codigo`)) */

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.

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.

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.  Las opciones posibles, para borrado (delete) y modificación (update) son

RECHAZAR la operación (opción por defecto, si no se dice nada este es el comportamiento de la clave ajena)

PROPAGAR la operación (cascade)

ANULAR, cambiar el valor de clave ajena a nulo (set null)

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

Como no se ha dicho nada en las claves ajenas, la opción para mantener la integridad es —en todas ellas y para borrados y modificaciones— RECHAZAR la operación si el valor de clave ajena está siendo utilizado como referencia en otra tabla.

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.

Ejecuta

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 

);

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

select * from asignaturas;

select * from profesores;

select * from imparte;

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

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.

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

foreign key (dni) references profesores (dni) ON DELETE SET NULL,

foreign key (asignatura) references asignaturas (codigo) ); -- por defecto, RECHAZA para borrados y modificaciones en ASIGNATURAS

Now foreign keys allow nulls.

insert into imparte values (1,'21111222','FBD');

insert into imparte values (2,'21111222','DGBD');

insert into imparte values (3,'21333444','PC');

delete from profesores where dni = '21111222';

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;

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 UPDATE CASCADE,

foreign key (asignatura) references asignaturas (codigo) ON UPDATE SET NULL

);

insert into imparte values (1,'21111222','FBD');

insert into imparte values (2,'21111222','DGBD');

insert into imparte values (3,'21333444','PC');

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

update profesores set dni = '33' where dni = '21111222'; 

Obviously, any combination of methods can be used,

create table imparte( 

ficha integer, 

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

primary key (ficha),

foreign key (dni) references profesores(dni) ON UPDATE CASCADE,

foreign key (asignatura) references asignaturas(codigo) ON DELETE SET NULL ON UPDATE CASCADE 

);

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

Nótese que, en cualquier caso, estos automatismos solo son necesarios en una dirección, en borrados o modificaciones sobre la tabla referenciada, no en la tabla que aloja las claves ajenas:

delete from imparte where ficha=3;