Explicación detallada de las restricciones de clave externa en la base de datos MySQL
Cualquiera que haya desarrollado una pequeña aplicación web basada en bases 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 la otra para procesar 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 serio desafío, sino que la mayor dificultad es que debemos mantener su integridad a nivel de 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 diversas relaciones entre 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 restringir las claves externas. manejado de una manera muy sencilla. 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. 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.
Cuándo usar restricciones de clave externa
Para ser honesto, cuando se usan tablas InnoDB en MySQL, no es necesario usar restricciones de clave externa. Sin embargo, para usar restricciones de clave externa. En algunos casos, la función en este caso se explicará en detalle a través del código del ejemplo mencionado anteriormente. 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 separar las filas de datos (es decir, comentario) Corresponde a una publicación de blog específica.
Aquí está el código SQL básico para crear una tabla MyISAM de muestra:
DROP TABLE IF EXISTS `test`.`blogs`;
CREATE TABLE `test`.`blogs` (` id` INT(10) AUTO_INCREMENT SIN FIRMAR,
`title` TEXTO,
`content` TEXTO,
`author` VARCHAR(45) DEFAULT NULL,
p>
PRIROSE KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `test`.`comments`;
CREAR TABLA ` prueba`.`comentarios` (`id` INT(10) UNSIGNED AUTO_INCREMENT,
`blog_id` INT(10) UNSIGNED DEFAULT NULL,
` comentario` TEXTO,
`autor` VARCHAR(45) DEFAULT NULL,
PRIROSE KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Arriba, acabamos de definir dos tablas MyISAM que forman la capa de datos de la aplicación de blogs. 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 sobre 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 relativamente 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.
Actualizar y mantener 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 un comando SQL de la siguiente manera: INSERT INTO blogs (id, título, contenido, autor) VALUES (NULL, 'Título de la primera entrada del blog', 'Contenido de la primera entrada de blog', 'Ian')
INSERT INTO comments (id, blog_id, comentario, autor) VALUES (NULL, 1, 'Comentando la primera entrada de blog', 'Susan Norton'), (NULL, 1 , 'Comentando la primera entrada del blog', 'Rose Wilson')
El código anterior en realidad simula los comentarios de los lectores Susan y Rose en nuestra primera entrada del 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 comandos 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, porque el primer blog El contenido del elemento de datos se ha actualizado, por lo que 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 característica. Por lo tanto, en la parte posterior usaremos restricciones de clave externa para recrear el código de ejemplo anterior.
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 tablas 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, puedes usar un código SQL como este:
DROP TABLE IF EXISTS `test`.`blogs`;
CREAR TABLE `test`.`blogs` (`id ` INT(10) AUTO_INCREMENT SIN FIRMAR,
`title` TEXT,
`content` TEXT,
`author` VARCHAR(45) DEFAULT NULL,
PRIROSE KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `test`.`comments`;
CREAR TABLA `test`.`comments` (`id` INT(10) UNSIGNED AUTO_INCREMENT,
`blog_id` INT(10) UNSIGNED DEFAULT NULL,
`comment` TEXT,
p>
`author` VARCHAR(45) DEFAULT NULL,
PRIROSE KEY (`id`),
KEY `blog_ind` (`blog_id` ),
RESTRICCIÓN `comments_ibfk_1` CLAVE EXTRANJERA (`blog_id`) REFERENCIAS `blogs` (`id`) EN CASCADA DE ACTUALIZACIÓN) MOTOR=InnoDB DEFAULT CHARSET=utf8;
El código Aquí es lo mismo que el código anterior. En comparación, una diferencia obvia 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:
CONSTRAINT `comments_ibfk_1` CLAVE EXTRANJERA (`blog_id`) REFERENCIAS `blogs` (`id`) EN ACTUALIZAR CASCADA
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 también debe actualizarse. En otras palabras
En otras palabras, lo que se hace aquí es permitir que MySQL mantenga la integridad de la base de datos en cascada, lo que significa que cuando se actualiza un blog, los comentarios conectados a él también deben reflejar inmediatamente el cambio. Lo importante es que la implementación de esta funcionalidad no se realiza 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, de la siguiente manera:
"UPDATE blogs SET id = 2. title. = '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 actualizar los comentarios tabla, 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 más código para profundizar aún más el conocimiento de esta conveniente. característica de base de datos.