Integridad Referencial

Integridad referencial @ Club Desarrolladores

Integridad referencial Publicado por Gastón el 24/04/2009 en MySQL - Nivel Intermedio

Resúmen Artículo destinado a introducir los principios de la integridad referencial para bases de datos y en particular su uso en MySQL, más precisamente en tablas INNOdb. El artículo se basa en dos ejemplos detallados (realmente detallados) para su fácil entendimiento; ejemplos basados en casos reales (en dos módulos de este mismo sitio). Si bien el artículo es plenamente práctico, intento incluir algunos conceptos teóricos para alivianar un poco su lectura y llegar mejor a buen puerto.

Tabla de contenidos Integridad Referencial para una Base de Datos Consistente Relación uno a muchos Inconsistencias Integridad Referencial Relación muchos a muchos Inconsistencias Integridad Referencial

Integridad Referencial para una Base de Datos Consistente La situaciones que vamos a estudiar son muy sencillas y comunes. Se tienen entidades que ofrecen algún tipo de relación lógica entre sus cardinalidades, y se desea automatizar la actualización de las mismas manteniendo la base de datosconsistente.

Relación uno a muchos En este caso estudiaremos el típico caso de una ocurrencia de entidad A pertenece a una ocurrencia de la entidad B. Se trata de una relación de uno a muchos o N a N, o maestro/detalle. Por ejemplo, en este mismo módulo de artículos se da ésta situación: este artículo pertenece a la categoría MySQL y la categoría MySQL puede tener muchos artículos en su haber. En este tipo de relaciones, luego de realizar el módelo conceptual, obtenemos el modelo lógico el cual resulta en la siguiente definición de tablas (solo se mostrarán las claves primarias y foraneas, obviando cualquier otro tipo de columnas):

Página 1/8

Integridad referencial @ Club Desarrolladores CREATE TABLE categorias ( id_categoria integer not null primary key, categoria varchar(30) not null ); CREATE TABLE articulos ( id_articulo integer not null primary key, id_categoria integer null, articulo varchar(60) not null, texto text not null );

En la definición de la tabla articulos decimos que un artículo no debe necesariamente pertenecer a una categoría (puede ser null su referencia a categoria). Veamos un poco de teoría. Unaclave primaria se define como una o más columnas que identifican unívocamente una fila en nuestra misma tabla. En nuestro ejemplo tenemos id_categoría en la tabla categorias e id_articulo en la tabla articulos. Como se puede ver en el código éstas se definen con las palabras claves primary key. Una clave foranea se define como una o más columnas que identifican unívocamente una fila en otra tabla. Es decir, una clave foranea referencia a una clave primaria de una tabla foranea. En el ejemplo anterior sería id_categoria de la tabla articulos, que haría referencia a id_categoria de la tabla categorias. Como se puede ver, el motor de base de datos no sabe aún de esta referencia (ni de cómo tratarla), a eso lo veremos a continuación. Pero antes notemos un detalle muy importante, en la definición de tablas anterior no especificamos que tipo de tabla son, y MySQL soporta integridad referencial en tablas INNOdb, no así en tablas MyISAM. Con el siguiente código podremos reparar tal situación y convertir nuestras tablas a INNOdb: ALTER TABLE categorias TYPE=INNODB ALTER TABLE articulos TYPE=INNODB

Inconsistencias Una inconsistencia es una situación que se presenta cuando una clave foranea referencia a una clave primaria inexistente en la tabla referenciada. Lo explicaré con un ejemplo. Supongamos que tenemos dos categorías y tres artículos:

INSERT INTO categorias (id_categoria, categoria) VALUES (1, 'MySQL'), (2, 'Microsoft SQL Server'); INSERT (1, 1, (2, 1, (3, 2,

INTO articulos (id_articulo, id_categoria, articulo, texto) VALUES 'Tipos de tablas en MySQL', 'Contenido del artículo...'), 'Procedimientos almacenados', 'Contenido del artículo...'), 'Tablas autoreferenciadas', 'Contenido del artículo...');

Como se puede ver tenemos estas referencias: (1) (1) 'Tipos de tablas en MySQL' -------> (1) 'MySQL' (2) (1) 'Procedimientos almacenados' -------> (1) 'MySQL' (3) (2) 'Tablas autoreferenciadas' -------> (2) 'Microsoft SQL Server'

Nota: Los número encerrados entre paréntesis indican las claves primarias (y foraneas en el caso de la primer columna) de las tablas en cuestión.

Página 2/8

Integridad referencial @ Club Desarrolladores

Y ahora por algún motivo eliminamos de la tabla categorias la categoría de MySQL (1). ¿Qué sucede con los artículos que correspondían a esta categoría (1) y (2)?... ¡Quedan huérfanos! apuntarían a claves incorrectas y nuestra base de datos se volvería inconsistente. Cuando digo que apuntarían a claves incorrectas se pueden ver dos posibles situaciones: 1 - Se elimina (1) MySQL de la tabla categorias, los articulos que apuntaban a (1) MySQL seguirían apuntando a este, pero ¡éste ya no existe! (1) (1) 'Tipos de tablas en MySQL' -------> (???) (2) (1) 'Procedimientos almacenados' -------> (???) (3) (2) 'Tablas autoreferenciadas' -------> (2) 'Microsoft SQL Server'

2 - Se elimina (1) MySQL de la tabla categorias, y se agrega una nueva categoría a esta tabla (1) 'PostgreSQL' (esto es totalmente válido, se cumple la restricción de clave primaria en categorias). Ahora tenemos que: (1) (1) 'Tipos de tablas en MySQL' -------> (1) 'PostgreSQL' (2) (1) 'Procedimientos almacenados' -------> (1) 'PostgreSQL' (3) (2) 'Tablas autoreferenciadas' -------> (2) 'Microsoft SQL Server'

Noten la primera ocurrencia de relación: (1) (1) 'Tipos de tablas en MySQL' -------> (1) 'PostgreSQL'... creo que habla por si misma.

Integridad Referencial Estas situaciones son muy comunes y, aunque pueden resolverse mediante el lenguaje de programación cliente, es mejor dejar a la propia base de datos mantener la consistencia. Esto se debe a que mantener la consistencia desde un lenguaje como PHP o C++ por ejemplo representaría tener que realizar una consulta extra luego de cada UPDATE o DELETE en la tabla referenciada, lo que implicaría una perdida de legibilidad y de performance. Por ejemplo si la intención es eliminar la categoría de MySQL, deberemos también actualizar la tabla articulos para que la consistencia se mantenga entre ambas tablas, por lo que deberemos actualizar aquellas filas cuya columna id_categoria sea igual a 1.

DELETE FROM categorias WHERE id_categoria = 1; UPDATE articulos SET id_categoria = NULL WHERE id_categoria = 1;

Y si la intención es modificar el id_categoria de 'MySQL' a 3 también deberíamos hacer lo propio en aquellas filas de tabla articulos que referencian a MySQL. UPDATE categorias SET id_categoria = 3 WHERE id_categoria = 1; UPDATE articulos SET id_categoria = 3 WHERE id_categoria = 1;

Si por algún motivo olvidamos esto nuestra base de datos quedará inconsistente, o lo que es lo mismo, incoherente tal como se explicó anteriormente. Ahora por fin, relacionaremos las tablas. He aquí la sintaxis de la sentencia ALTER TABLE que creará la restricción de clave ajena (o foránea) y por ende la integridad referencial para estas dos tablas: ALTER TABLE articulos ADD FOREIGN KEY(id_categoria) REFERENCES categorias(id_categoria) ON DELETE SET NULL ON UPDATE CASCADE

Página 3/8

Integridad referencial @ Club Desarrolladores

Esto se puede leer asi: Alterar la tabla articulos agregando una restricción a la clave foranea id_categoria (de la tabla articulos) que referencia a id_categoria (de la tabla categorias), cuando se elimine alguna categoría (y por consiguiente su id_categoria en la tabla categorias) setear NULL a id_categoria (de la tabla articulos) y cuando se modifique alguna categoria (se modifique id_categoria de la tabla categorias) aplicar esa modificación a todos los articulos (a todos los id_categoria de la tabla articulos). Lealo nuevamente si tiene dudas, igualmente aqui hay un ejemplo esclarecedor. Por ejemplo si hacemos: DELETE FROM categorias WHERE id_categoria = 1;

automáticamente se le dará el valor NULL a todas las tuplas de articulos cuyo id_categoria sea igual a 1. Quedaria asi: (1) (NULL) 'Tipos de tablas en MySQL' -------> NULL (2) (NULL) 'Procedimientos almacenados' -------> NULL (3) (2) 'Tablas autoreferenciadas' -------> (2) 'Microsoft SQL Server'

Y si en lugar de la eliminación anterior hacemos una actualización como ésta: UPDATE categorias SET id_categoria = 3 WHERE id_categoria = 1;

automáticamente se le dará el valor 3 a todas las tuplas de articulos cuyo id_categoria sea igual a 1. Nos quedaria asi: (1) (3) 'Tipos de tablas en MySQL' -------> (3) 'MySQL' (2) (3) 'Procedimientos almacenados' -------> (3) 'MySQL' (3) (2) 'Tablas autoreferenciadas' -------> (2) 'Microsoft SQL Server'

¿Fácil no? una solución elegante y eficiente. Y que sucede si hubiera definido la restricción de la siguiente manera: ALTER TABLE articulos ADD FOREIGN KEY(id_categoria) REFERENCES categorias(id_categoria) ON DELETE CASCADE ON UPDATE CASCADE

¡CASCADE tanto en DELETE como en UPDATE! A eso lo veremos a continuación cuando expliquemos integridad referencial en una relación de muchos a muchos (dé vuelta la página!).

Relación muchos a muchos Ahora que entendimos como funciona la integridad referencial en relaciones de uno a muchos, vamos a ver un caso que también se ve mucho en la hora de desarrollar sistemas. Siguiendo con el ejemplo anterior tomemos como caso de estudio el módulo de foros del club. En este módulo tenemos temas y categorias (aunque yo trate de llamarla grupos para que suene novedoso). Un tema puede pertenecer a muchas categorias y una categoría puede tener muchos temas. Es una relación muchos a muchos o N a N que puede representarse de la siguiente manera:

Página 4/8

Integridad referencial @ Club Desarrolladores CREATE TABLE categorias ( id_categoria integer not null primary key, categoria varchar(30) not null ) ENGINE = INNODB; CREATE TABLE temas ( id_tema integer not null primary key, tema varchar(60) not null, texto text not null ) ENGINE = INNODB;

Esta vez explicitamos que el tipo de tabla que usaremos será INNOdb. Y también creamos la tabla que servirá de relación entre las anteriores: CREATE TABLE categorias_temas ( id_categoria integer not null, id_tema integer not null, primary key(id_categoria, id_tema) ) ENGINE = INNODB;

Puede verse que esta última tabla tiene una clave primaria compuesta por dos columnas, e individualmente cada una de estas columnas será clave foránea de las tablas anteriores según corresponda como veremos a continuación luego de explicar los casos de inconsistencias que podriamos resultar.

Inconsistencias Supongamos que tenemos estos datos cargados:

INSERT INTO categorias (id_categoria, categoria) VALUES (1, 'MySQL'), (2, 'Microsoft SQL Server'); INSERT INTO temas (id_tema, tema, texto) VALUES (1, '¿Tipos de tabla MySQL, que son?', 'Contenido del tema...'), (2, 'No me sale esta consulta', 'Contenido del tema...'), (3, 'Como lograr el login?!', 'Contenido del tema...'); INSERT INTO categorias_temas (id_categoria, id_tema) VALUES (1, 1), (1, 2), (2, 2), (2, 3);

En forma tabular veriamos algo asi: (1) (1) (2) (2)

'MySQL' ---> (1)(1) ---> (1) '¿Tipos de tabla MySQL, que son?' 'MySQL' ---> (1)(2) ---> (2) 'No me sale esta consulta' 'Microsoft SQL Server' ---> (2)(2) ---> (2) 'No me sale esta consulta' 'Microsoft SQL Server' ---> (2)(3) ---> (3) 'Como lograr el login?!'

Empecemos a armar un poco de lio. Eliminemos la categoría (1) MySQL y veamos que sucede.

Página 5/8

Integridad referencial @ Club Desarrolladores (NULL) ---> (1)(1) (NULL) ---> (1)(2) (2) 'Microsoft SQL (2) 'Microsoft SQL

---> (1) '¿Tipos de tabla MySQL, que son?' ---> (2) 'No me sale esta consulta' Server' ---> (2)(2) ---> (2) 'No me sale esta consulta' Server' ---> (2)(3) ---> (3) 'Como lograr el login?!'

La tabla de relaciones no se actualiza y sigue indicando que tanto los temas (1) como (2) pertenecen a la categoría (1) la cual no existe! Olvidemos la maldad anterior, y en lugar de eliminar la categoría MySQL eliminaremos el tema (3): (1) (1) (2) (2)

'MySQL' ---> (1)(1) ---> (1) '¿Tipos de tabla MySQL, que son?' 'MySQL' ---> (1)(2) ---> (2) 'No me sale esta consulta' 'Microsoft SQL Server' ---> (2)(2) ---> (2) 'No me sale esta consulta' 'Microsoft SQL Server' ---> (2)(3) ---> (NULL)

Ahora tenemos otro problema, aunque muy similar. La categoría 2 cuenta con dos temas en su haber, uno es el tema (2) y el otro el tema (3)... claro, (3) no existe! Lo mismo sucedería si modificáramos en lugar de eliminar. Imaginemos que modificamos la categoria (1) MySQL a (3) y que modificamos el tema (1) a (4), esto es lo que obtendriamos: (3) (3) (2) (2)

'MySQL' ---> (1)(1) ---> (4) '¿Tipos de tabla MySQL, que son?' 'MySQL' ---> (1)(2) ---> (2) 'No me sale esta consulta' 'Microsoft SQL Server' ---> (2)(2) ---> (2) 'No me sale esta consulta' 'Microsoft SQL Server' ---> (2)(3) ---> (3) 'Como lograr el login?!'

¿Se dan cuenta no? La clave está en ver que la tabla que relaciona (categorias_temas) no se actualiza automáticamente y por lo tanto saltan todas las incoherencias posibles. Cualquiera diría que ésta es una base de datos inconsistente.

Integridad Referencial Cortemos por lo sano, apliquemos integridad referencial a estas tablas alterando la que contiene las claves foráneas, es decir:

ALTER TABLE categorias_temas ADD FOREIGN KEY(id_categoria) REFERENCES categorias(id_categoria) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE categorias_temas ADD FOREIGN KEY(id_tema) REFERENCES temas(id_tema) ON DELETE CASCADE ON UPDATE CASCADE;

Como puede verse hay que alterar la tabla dos veces, porque tenemos dos claves foráneas a las cuales restringir. Lo voy a traducir al castellano (o español... mejor al criollo): Alterar la tabla categorias_temas agregando una restricción a la clave foranea id_categoria (de la tabla categorias_temas) que referencia a id_categoria (de la tabla categorias), cuando se elimine alguna categoría (y por consiguiente su id_categoria en la tabla categorias) eliminar todas las tuplas de categorias_temas que tengan id_categoria igual al id_categoria de la categoría eliminada y cuando se modifique alguna categoria (se modifique id_categoria de la tabla categorias) aplicar esa modificación a todas las tuplas de categorias_temas (a todos los id_categoria de la tabla categorias_temas).

Página 6/8

Integridad referencial @ Club Desarrolladores

Luego... Alterar la tabla categorias_temas agregando una restricción a la clave foranea id_tema (de la tabla categorias_temas) que referencia a id_tema (de la tabla temas), cuando se elimine algun tema (y por consiguiente su id_tema en la tabla temas) eliminar todas las tuplas de categorias_temas que tengan id_tema igual al id_tema del tema eliminado y cuando se modifique algun tema (se modifique id_tema de la tabla temas) aplicar esa modificación a todas las tuplas de categorias_temas (a todos los id_tema de la tabla categorias_temas). ¿Difícil de leer? Imagínese escribirlo... Como ejemplo, porque no voy a dejarlo con dudas, vamos a tomar el caso del que hablamos hace poco. Teniendo esto cargado: (1) (1) (2) (2)

'MySQL' ---> (1)(1) ---> (1) '¿Tipos de tabla MySQL, que son?' 'MySQL' ---> (1)(2) ---> (2) 'No me sale esta consulta' 'Microsoft SQL Server' ---> (2)(2) ---> (2) 'No me sale esta consulta' 'Microsoft SQL Server' ---> (2)(3) ---> (3) 'Como lograr el login?!'

Imaginemos que modificamos la categoria (1) MySQL a (3) y que modificamos el tema (1) a (4), esto es lo que obtendriamos:

(3) (3) (2) (2)

'MySQL' ---> (3)(4) ---> (4) '¿Tipos de tabla MySQL, que son?' 'MySQL' ---> (3)(2) ---> (2) 'No me sale esta consulta' 'Microsoft SQL Server' ---> (2)(2) ---> (2) 'No me sale esta consulta' 'Microsoft SQL Server' ---> (2)(3) ---> (3) 'Como lograr el login?!'

Ahora disponemos de una base de datos consistente. Todo es lógico, no perdemos relaciones, las mantenemos coherentes en todo momento y lo mejor es que esto se realiza en la base de datos, con sus ventajas antes mencionadas y de una manera muy fácil de declarar... ¿Será por eso que SQL se trata de un paradigma declarativo? Falta un solo detalle por comentar. En MySQL existe tres opciones más además de SET NULL y CASCADE a saber: NO ACTION y RESTRICT: Funcionan igual, no permiten actualización alguna (UPDATE o DELETE) rechazando el cambio que intentó propagarse. SET DEFAULT: A la fecha no está implementado, debería funcionar de forma similar a SET NULL solo que asignaría el valor declarado por defecto al crear las columas de las tablas foráneas. Y esto es todo por ahora. Espero que le den un buen uso a este artículo que acabo de escribir, ahora mismo escribo la descripción (sería como el prólogo, dicen los que saben que se escriben al final del libro) y lo publico para que puedan leerlo. No se olviden de votarlo y comentar, si bien no lo hago para recibir feedback es motivador saber que la gente lee lo que uno escribe con tanto esfuerzo.

Sobre el autor Gastón tiene 28 años, vive en Argentina / Santa Fe / Santa Fe y su ocupación es Desarrollador de aplicaciones web. Ha publicado 31 artículos en clubdesarrolladores con un promedio de valoración de 7.80 puntos. Puedes visitar su sitio web en http://www.clubdesarrolladores.com

Página 7/8

Integridad referencial @ Club Desarrolladores

Descargado de Club Desarrolladores - Visitanos en http://www.clubdesarrolladores.com

Página 8/8