Claves ajenas

Las claves ajenas y la integridad referencial son uno de los mecanismos más potentes del modelo relacional para expresar relaciones entre los objetos del sistema de información. En la práctica es la herramienta que nos fuerza a almacenar datos fidedignos y confiables, es una garantía de la calidad de los datos.

create database probando;

use probando;

CREATE TABLE unidad (

 unidad char(1),

 nombre varchar(15) DEFAULT NULL,

 PRIMARY KEY (unidad)

);

INSERT INTO unidad VALUES

       ('a', null),

       ('c', 'centilitros'),

       ('g', 'gramos'),

       ('k', 'kilogramos'),

       ('l', 'litros'),

       ('u', 'unidad');

CREATE TABLE articulo (

 cod char(5),

 precio decimal(5,2) NOT NULL,

 unidad char(1),

 PRIMARY KEY (cod),

 FOREIGN KEY (unidad) REFERENCES unidad (unidad)

);

Vamos a fijarnos en la restricción de clave ajena (foreign key). Una clave ajena es una referencia a otra tabla. De momento quédate con que es una herramienta para enlazar esta tabla con otra diferente. Una o varias columnas que hacen referencia a una fila concreta en otra tabla. Por eso, las claves ajenas enlazan con claves primarias, las que identifican filas.

Probemos la clave ajena. Todas estas operaciones sí pueden hacerse:

INSERT INTO articulo VALUES ('00020',99.99,null); 

INSERT INTO articulo VALUES ('00022',99.99,'u'); 

INSERT INTO articulo VALUES ('00023',99.99,'u');

Vamos a insertar una fila en artículo. 

INSERT INTO articulo VALUES ('00024',99.99,'f');

Obtenemos un error porque hemos intentado dar un valor a la clave ajena no presente en la clave primaria de la tabla "unidad"

Garantizar que las referencias desde una tabla hacia la otra son consistentes hace que no podamos ejecutar la siguiente orden.

delete from unidad where unidad = 'u'

Ha fallado porque hay filas en ARTICULO con precisamente ese valor de clave ajena. Sin embargo otras filas de UNIDAD no tienen ese problema, el valor 'a' no está siendo utilizado en la tabla ARTICULO.

delete from unidad where unidad = 'a'

Otra consecuencia del uso de claves ajenas es que no podemos eliminar ciertas tablas.

drop table unidad;

La orden anterior no se ha podido llevar a cabo por la clave ajena de ARTICULO que le está haciendo referencia. Antes hay que resolver esas referencias, eliminarlas. Es tan simple como empezar destruyendo la tabla "articulo".

drop table articulo;

drop table unidad;

Lo que hemos visto es el cumplimiento de la integridad referencial, una propiedad fundamental del modelo relacional de datos, el que usa MariaDB. Esta propiedad —o regla— establece que una clave ajena debe mantener valores que podamos encontrar en su correspondiente clave primaria. Y si no es así, valores nulos en todas sus columnas.

Sin embargo MariaDB no cumple del todo con esta norma.

Vamos a comprobarlo con una clave primaria compuesta de 2 columnas. Sus propiedades siguen siendo las mismas: ni nulos ni duplicados. 

CREATE TABLE unidad (

 unidad char(1),

 variante integer,

 PRIMARY KEY (unidad,variante)

);

INSERT INTO unidad VALUES ('c',1);

Fíjate en la definición de clave primaria, ahora está compuesta de las columnas "unidad" y "variante". Para entendernos, la única fila que hemos insertado podemos decir que se identifica como "c1". 

Si definiéramos una clave ajena a esta tabla, hay que respetar número de columnas, tipos de datos y orden

CREATE TABLE articulo (

 cod char(5),

 u char(1),

 v integer,

 PRIMARY KEY (cod),

 FOREIGN KEY (u,v) REFERENCES unidad (unidad,variante)

);

Probando qué se puede almacenar en esta tabla:

INSERT INTO articulo VALUES ('00000','d',1);

INSERT INTO articulo VALUES ('00000','c',2);

INSERT INTO articulo VALUES ('00000','c',1);

INSERT INTO articulo VALUES ('00001','c',1);

INSERT INTO articulo VALUES ('00002',NULL,NULL);

Las dos primeras fallan porque ni "d1" ni "c2" los puedo encontrar en UNIDAD. Las tercera y cuarta inserciones sí se realizan, "c1" sí lo encuentra. Los artículos 0 y 1 tienen la misma unidad. 

También es correcta la última, permitimos nulos en las columnas "u" y "v" de ARTICULO, y el valor de clave ajena es completamente nulo, el valor nulo está en ambas columnas. Más o menos venimos a decir que "el artículo 2 no tiene unidad". De hecho, si consultamos obtenemos las dos filas que sí tienen unidad:

select * from articulo, unidad where u=unidad and v=variante;

Todo perfecto, excepto que MariaDB nos permite hacer la siguientes inserciones:

INSERT INTO articulo VALUES ('00003',NULL,1);

INSERT INTO articulo VALUES ('00004','c',NULL);

INSERT INTO articulo VALUES ('00005',NULL,2);

Según MariaDB esto no afecta a la integridad referencial. Obviamente seguimos obteniendo el mismo resultado de la consulta anterior.

select * from articulo, unidad where u=unidad and v=variante;

Pero esto no quita para que en ARTICULO mantengamos ciertos valores de clave ajena sin contrapartida en UNIDAD. Sinceramente, no nos gusta mucho que permita almacenar valores parcialmente nulos en claves ajenas. Pero es lo que hay.

En realidad MariaDB no implementa los métodos de verificación de los valores de clave ajena que se definieron en la revisión del lenguaje SQL:1999. Allí se concreta que hay 3 métodos definibles para el comportamiento de las claves ajenas: MATCH SIMPLE, MATCH PARTIAL y MATCH FULL. El último es que se ajusta a la definición original de integridad referencial. Muchos SGBDR, entre ellos MariaDB, simplemente adoptan MATCH SIMPLE, el más permisivo, e ignoran MATCH FULL. De ahí el comportamiento visto en nuestros ejemplos.