-
Este artículo presentará a los lectores las restricciones de clave externa en MySQL. A lo largo de la introducción de este artículo, descubrirá que es fácil activar actualizaciones en cascada de los datos de la tabla secundaria al actualizar filas de datos en la tabla principal.
Este artículo presentará a los lectores las restricciones de clave externa en MySQL. A lo largo de la introducción de este artículo, descubrirá que es fácil activar actualizaciones en cascada de los datos de la tabla secundaria al actualizar filas de datos en la tabla principal.
1. Introducción
Cualquiera que haya desarrollado una pequeña aplicación web basada en una base de datos utilizando MySQL sabe que crear, recuperar, actualizar y eliminar tablas en una base de datos relacional son procesos relativamente simples. En teoría, siempre que domines el uso de las sentencias SQL más comunes y estés familiarizado con el lenguaje de scripting del lado del servidor que elijas usar, es suficiente para manejar las diversas operaciones requeridas en las tablas MySQL, especialmente cuando usas el rápido MyISAM. motor de base de datos cuando. Pero incluso en los casos más simples, las cosas son más complicadas de lo que pensamos. A continuación utilizamos un ejemplo típico para ilustrar. Suponga que tiene un blog que actualiza casi todos los días y que el sitio permite a los visitantes comentar sus publicaciones.
En este caso, nuestro esquema de base de datos debe incluir al menos dos tablas MyISAM, una para almacenar las publicaciones de su blog y otra para manejar los comentarios de los visitantes. Obviamente, existe una relación de uno a muchos entre estas dos tablas, por lo que debemos definir una clave externa en la segunda tabla para que se pueda mantener la integridad de la base de datos cuando se actualizan o eliminan filas de datos.
Para una aplicación como la anterior, mantener la integridad de las dos tablas no solo es un desafío serio, sino que la mayor dificultad es que tenemos que mantener su integridad a nivel de la aplicación. Este es el enfoque adoptado durante el desarrollo de la mayoría de los proyectos web que no requieren el uso de transacciones porque las tablas MyISAM proporcionan un rendimiento excelente.
Por supuesto, esto también tiene un costo. Como dije antes, la aplicación debe mantener la integridad y coherencia de la base de datos, lo que significa implementar una lógica de programación más compleja para manejar las relaciones entre varias tablas. Aunque el acceso a la base de datos se puede simplificar mediante el uso de capas de abstracción y módulos ORM, a medida que aumenta la cantidad de tablas de datos requeridas por una aplicación, la lógica requerida para manejarlas sin duda se volverá más compleja.
Entonces, para MySQL, ¿existe algún método de procesamiento de claves externas a nivel de base de datos que ayude a mantener la integridad de la base de datos? Afortunadamente, la respuesta es sí. MySQL también puede admitir tablas InnoDB, lo que nos permite usar una forma muy simple de manejar las restricciones de claves externas. Esta característica nos permite activar ciertas acciones, como actualizar y eliminar ciertas filas de datos en la tabla para mantener relaciones predefinidas.
Todo tiene pros y contras, y la principal desventaja de usar tablas InnoDB es que son más lentas que MyISAM, especialmente en aplicaciones a gran escala donde se deben consultar muchas tablas. Afortunadamente, la tabla MyISAM en la versión más reciente de MySQL también admite restricciones de clave externa.
Este artículo presentará cómo aplicar restricciones de clave externa a tablas InnoDB. Además, usaremos una clase abstracta MySQL simple basada en PHP para crear el código de muestra relevante. Por supuesto, también puede usar su otro lenguaje del lado del servidor favorito; Ahora comenzamos a presentar cómo aplicar restricciones de clave externa a MySQL.
2. Cuándo utilizar restricciones de clave externa
Para ser honesto, cuando se usan tablas InnoDB en MySQL, no necesariamente es necesario usar restricciones de clave externa. Sin embargo, para las restricciones de clave externa en ciertas situaciones, usaremos el código del ejemplo mencionado anteriormente para explicarlo en detalle. Incluye dos tablas MyISAM, que se utilizan para almacenar publicaciones y comentarios de blogs.
Al definir el esquema de la base de datos, necesitamos establecer una relación de uno a muchos entre las dos tablas creando una clave externa en la tabla donde se almacenan los comentarios para asignar las filas de datos (es decir, comentarios) a un artículo de blog específico. Aquí está el código SQL básico para crear una tabla MyISAM de muestra:
SOLTAR TABLA SI EXISTE `prueba`.`blogs`;
CREAR TABLA `prueba`.`blogs` (
`id` INT(10) AUTO_INCREMENT SIN FIRMAR,
`título` TEXTO,
TEXTO `contenido`,
`autor` VARCHAR(45) POR DEFECTO NULO,
CLAVE DE PRIMERA (`id`)
) MOTOR = MyISAM CONJUNTO DE CARACTERES PREDETERMINADO = utf8;
SOLTAR TABLA SI EXISTE `prueba`.`comentarios`;
CREAR TABLA `prueba`.`comentarios` (
`id` INT(10) AUTO_INCREMENT SIN FIRMAR,
`blog_id` INT(10) NULO POR DEFECTO SIN FIRMAR,
`comentario` TEXTO,
`autor` VARCHAR(45) POR DEFECTO NULO,
CLAVE DE PRIMERA (`id`)
) MOTOR = MyISAM CONJUNTO DE CARACTERES PREDETERMINADO = utf8;
Arriba, acabamos de definir dos tablas MyISAM, que forman la capa de datos de la aplicación del blog. Como puede ver, la primera tabla se llama blogs y consta de algunos campos obvios, que se utilizan para almacenar el ID, el título y el contenido de cada publicación del blog y, finalmente, el autor. La segunda tabla se denomina comentarios y se utiliza para almacenar comentarios relacionados con cada publicación del blog. Utiliza el ID de la publicación del blog como clave externa para establecer una relación de uno a muchos.
Hasta ahora, nuestro trabajo ha sido fácil porque sólo hemos creado dos tablas MyISAM simples. A continuación, lo que queremos hacer es completar estas tablas con algunos registros para demostrar mejor lo que se debe hacer en la otra tabla cuando se elimina una entrada en la primera tabla.
3. Actualice las publicaciones del blog y mantenga la integridad de la base de datos.
En la parte anterior, creamos dos tablas MyISAM para que sirvan como capa de datos de la aplicación del blog. Por supuesto, la introducción anterior sigue siendo muy simple y debemos discutirla más a fondo. Para hacer esto, llenaremos estas tablas con algunos registros usando comandos SQL de la siguiente manera:
INSERTAR EN blogs (id, título, contenido, autor) VALORES (NULL, 'Título de la primera entrada del blog', 'Contenido de la primera entrada del blog', 'Ian')
INSERTAR EN comentarios (id, blog_id, comentario, autor) VALORES (NULL, 1, 'Comentando la primera entrada del blog', 'Susan Norton'), (NULL, 1, 'Comentando la primera entrada del blog', 'Rose Wilson') encima del El código en realidad simula la situación en la que los lectores Susan y Rose comentaron en nuestra primera publicación de blog. Supongamos que ahora queremos actualizar el primer blog con otra publicación. Por supuesto, esta situación es posible.
En este caso, para mantener la coherencia de la base de datos, la tabla de comentarios también debe actualizarse en consecuencia, ya sea manualmente o mediante una aplicación que procese la capa de datos. Para este ejemplo, usaremos un comando SQL para completar la actualización de la siguiente manera:
ACTUALIZAR blogs SET id = 2, título = 'Título de la primera entrada del blog', contenido = 'Contenido de la primera entrada del blog', autor = 'John Doe' WHERE id = 1
ACTUALIZAR comentarios SET blog_id = 2 WHERE blod_id = 1 Como se mencionó anteriormente, debido a que el contenido del elemento de datos del primer blog se actualizó, la tabla de comentarios también debe reflejar este cambio. Por supuesto, en realidad, esta operación de actualización debe completarse en la capa de aplicación en lugar de manualmente, lo que significa que esta lógica debe implementarse utilizando un lenguaje del lado del servidor.
Para completar esta operación, PHP puede utilizar un subproceso simple, pero de hecho, si se utilizan restricciones de clave externa, la operación de actualización de la tabla de comentarios se puede delegar a la base de datos.
Como se mencionó anteriormente en el artículo, las tablas MySQL de InnoDB brindan soporte perfecto para esta funcionalidad. Por lo tanto, en la parte posterior usaremos restricciones de clave externa para recrear el código de ejemplo anterior.
4. Actualización en cascada de la base de datos.
A continuación, reestructuraremos el código de ejemplo anterior utilizando restricciones de clave externa y una tabla InnoDB (en lugar del tipo MyISAM predeterminado). Para hacer esto, primero redefina las dos tablas de muestra para que puedan usar un motor de base de datos específico. Para hacer esto, puede usar código SQL como el siguiente:
SOLTAR TABLA SI EXISTE `prueba`.`blogs`;
CREAR TABLA `prueba`.`blogs` (
`id` INT(10) AUTO_INCREMENT SIN FIRMAR,
`título` TEXTO,
TEXTO `contenido`,
`autor` VARCHAR(45) POR DEFECTO NULO,
CLAVE DE PRIMERA (`id`)
) MOTOR = InnoDB JUEGO DE CARACTERES PREDETERMINADO = utf8;
SOLTAR TABLA SI EXISTE `prueba`.`comentarios`;
CREAR TABLA `prueba`.`comentarios` (
`id` INT(10) AUTO_INCREMENT SIN FIRMAR,
`blog_id` INT(10) NULO POR DEFECTO SIN FIRMAR,
`comentario` TEXTO,
`autor` VARCHAR(45) POR DEFECTO NULO,
CLAVE DE PRIMERA (`id`),
CLAVE `blog_ind` (`blog_id`),
RESTRICCIÓN `comments_ibfk_1` CLAVE EXTRANJERA (`blog_id`) REFERENCIAS `blogs` (`id`) EN ACTUALIZACIÓN EN CASCADA
) ENGINE=InnoDB DEFAULT CHARSET=utf8; una diferencia obvia entre el código aquí y el código anterior es que estas dos tablas ahora usan el motor de almacenamiento InnoDB, por lo que pueden admitir restricciones de clave externa. Además, también debemos prestar atención al código que define la tabla de comentarios:
RESTRICCIÓN `comments_ibfk_1` CLAVE EXTRANJERA (`blog_id`) REFERENCIAS `blogs` (`id`) EN CASCADA DE ACTUALIZACIÓN De hecho, esta declaración notifica a MySQLMySQL que cuando se actualiza la tabla de blogs, el valor de la clave externa blog_id en la tabla de comentarios debe también ser actualizado. En otras palabras, lo que se hace aquí es permitir que MySQL mantenga la integridad de la base de datos en cascada. Esto significa que cuando se actualiza un blog, los comentarios conectados a él también deben reflejar inmediatamente este cambio. No se hace en la capa de aplicación.
Se han definido las dos tablas MySQL de ejemplo. Ahora, actualizar estas dos tablas es tan simple como ejecutar una instrucción UPDATE, como se muestra a continuación:
"ACTUALIZAR blogs SET id = 2, título = 'Título de la primera entrada del blog', contenido = 'Contenido de la primera entrada del blog', autor = 'John Doe' WHERE id = 1" Como se mencionó anteriormente, no necesitamos actualice la tabla de comentarios, porque MySQL lo maneja todo automáticamente. Además, puede hacer que MySQL no haga nada al intentar actualizar una fila en la tabla de blogs eliminando la parte "ON UPDATE" de la consulta o especificando "NO ACTION" y "RESTRICT". Por supuesto, también puede dejar que MySQL haga otras cosas, que se presentarán en artículos posteriores.
A través de la introducción anterior, creo que todos tienen una comprensión clara de cómo usar restricciones de clave externa junto con tablas InnoDB en MySQL. Por supuesto, también puede escribir código inmediato para profundizar aún más su comprensión de esta conveniente función de base de datos.
5. Resumen
En este artículo, detallamos los conceptos básicos del uso de restricciones de clave externa con tablas InnoDB en MySQL. Como puede ver en los ejemplos de este artículo, es fácil activar actualizaciones en cascada para los elementos de datos de la tabla secundaria cuando se actualiza el contenido de la tabla principal, y también ilustra cómo las aplicaciones que manejan la capa de datos pueden quedar exentas de la necesidad. para hacerlo. Por supuesto, también podemos proporcionar el mismo efecto en cascada cuando la tabla principal elimina filas de datos, lo que explicaremos en un artículo posterior.