Cómo usar restricciones de clave extranjera de MySQL
Características de las restricciones de clave extranjera:
Algunas características importantes de la restricción de clave extranjera se explican a continuación.
- El tipo de datos de la clave extranjera que se usa en la tabla infantil debe ser el mismo con el tipo de datos de la clave principal que se usa en la tabla principal para referir la clave extranjera.
- Cualquier columna de índice o múltiples columnas puede referenciarse como una clave extranjera solo para la tabla innoDB.
- Referencias Se requieren privilegios o al menos uno de los privilegios de las declaraciones Seleccionar, Insertar, Actualizar y Eliminar para crear una clave extranjera.
- Se puede crear una clave extranjera de dos maneras. Uno mediante el uso de la declaración Crear y otra utilizando la declaración alter.
Requisito previo:
Antes de crear una restricción de clave extranjera, debe crear una base de datos y una tabla principal con la clave principal. Supongamos que el nombre de la base de datos es 'biblioteca'Y contiene dos tablas de padres llamadas'libros' y 'prestatario'. Hacer una conexión con el servidor mySQL usando el mysql cliente y ejecute las siguientes declaraciones SQL para crear la base de datos y las tablas.
Crear biblioteca de bases de datos;Usar biblioteca;
Crear libros de mesa (
ID int no nulo auto_increment,
Título Varchar (50) No NULL,
Autor Varchar (50) No NULL,
editor Varchar (50) no nulo,
Clave principal (ID)
) Motor = innodb;
Crear prestatarios de tabla (
ID VARCHAR (50) No NULL,
Nombre Varchar (50) No nulo,
dirección varchar (50) no nula,
Envíe un correo electrónico a Varchar (50) no nulo,
Clave principal (ID)
) Motor = innodb;
Definir la restricción de clave extranjera utilizando la declaración Crear
Crear una tabla llamada 'book_borrow_info'con restricciones de clave extranjera ejecutando la siguiente declaración. Aquí el book_id el campo es un clave externa para esta tabla y cada valor de este campo debe existir en el identificación campo de libros mesa. libros es la mesa principal y book_borrow_info es la mesa de los niños. También se establecen dos restricciones con la clave extranjera aquí. Estos son Eliminar la cascada y Actualizar Cascade. Eso significa que si alguna clave primaria se eliminará o se actualizará de la tabla principal, entonces se eliminarán los registros correspondientes relacionados con la tabla infantil relacionada con la clave externa o la clave extranjera.
Crear tabla book_borrow_info (prestado_id varchar (50),
book_id int,
prestatario_date fecha no nula,
fecha de return_date no nulo,
estado varchar (15) no nulo,
Índice par_ind (book_id),
Clave primaria (prestado_id, préstamo_date),
Libros de referencias de la clave extranjera (Book_id) (ID)
En la cascada de eliminación
En la actualización de la cascada
) Motor = innodb;
Ahora, ejecute las siguientes declaraciones SQL para insertar algunos registros en ambas tablas. La primera declaración de inserción insertará cuatro registros en libros mesa. Los cuatro valores de identificación campo de libros La tabla será 1, 2, 3 y 4 para el atributo de auto-incremento. La segunda declaración de inserción insertará cuatro registros en book_borrow_info basado en el identificación valor de libros mesa.
Insertar en los valores de los libros(NULL, 'Matar a un ruiseñor', 'Harper Lee', 'Grand Central Publishing'),
(Null, 'cien años de soledad', 'García Márquez', 'Lutfi Ozkok'),
(Nulo, 'un pasaje a la India', 'Forster, E.METRO.',' Biblioteca de imágenes BBC Hulton '),
(NULL, 'Hombre invisible', 'Ralph Ellison', 'Encyclopædia Britannica, incio.');
Insertar en valores de book_borrow_info
('123490', 1, '2020-02-15', '2020-02-25', 'devuelto'),
('157643', 2, '2020-03-31', '2020-03-10', 'pendiente'),
('174562', 4, '2020-04-04', '2020-04-24', 'prestado'),
('146788', 3, '2020-04-10', '2020-01-20', 'prestado');
Si intenta insertar un valor en el campo de clave extranjera de la tabla infantil que no existe en el campo clave primario de la tabla principal, MySQL generará un error. La siguiente instrucción SQL generará un error porque la tabla principal, libros no contiene ningún valor de identificación 10.
Insertar en valores de book_borrow_info('195684', 10, '2020-04-15', '2020-04-30', 'devuelto');
Después de ejecutar la siguiente declaración de eliminación, cuando el cuarto registro se eliminará del libros tabla luego los registros relacionados del book_borrow_info La tabla se eliminará automáticamente para la restricción de clave extranjera.
Eliminar de los libros donde id = 4;Seleccionar * de los libros;
Seleccionar * de book_borrow_info;
Definir la restricción de clave extranjera utilizando la declaración alter
Al principio, inserte algunos registros en prestatarios tabla y esta tabla se definirá como tabla principal en la siguiente ALTERAR declaración.
Insertar en los valores de los prestatarios('123490', 'Patrick Wood', '34 West Street Lancaster La14 9zh ',' [email protected] '),
('157643', 'Ezra Martin', '10 The Grove Birmingham B98 1eu ',' [email protected] '),
('174562', 'John Innes Archie', '55 Main Road Liverpool L2 3od ',' [email protected] '),
('146788', 'Frederick Hanson', '85 Highfield Road Shrewsbury SY46 3ME ',' [email protected] ');
Ejecutar lo siguiente ALTERAR declaración para establecer otra restricción de clave extranjera para book_borrow_info mesa para hacer la relación con prestatarios mesa. Aquí, pedir prestado_id se define como una clave extranjera para book_borrow_info mesa.
Alter tabla book_borrow_info Agregar restricción FK_BORROWER
REFERENCIA DE LA CLAVE EXTRANJERO (LA PROREST_ID) REFERENCIAS PROBRITOS (ID) EN RESTERENCIA DE LA CASCADA DELECHE CASCADA;
Ahora, inserte un registro en book_borrow_info con válido pedir prestado_id valor que existe en identificación campo de prestatarios mesa. 157643 El valor existe en la tabla de los prestatarios y la siguiente declaración de inserción se ejecutará correctamente.
Insertar en valores de book_borrow_info('157643', 1, '2020-03-10', '2020-03-20', 'devuelto');
La siguiente declaración de inserción generará un mensaje de error porque el valor de ID 195680 no existe en la mesa de los prestatarios.
Insertar en valores de book_borrow_info('195680', 1, '2020-04-15', '2020-04-30', 'devuelto');
Conclusión:
Definir las restricciones de clave extranjera correctamente es una tarea muy importante para crear una base de datos relacional y administrar datos entre las tablas de manera apropiada. Conocer los usos de las restricciones de clave extranjera es muy esencial para los diseñadores de bases de datos. Espero que este artículo ayude a los nuevos diseñadores de bases de datos a comprender el concepto de restricciones de clave extranjera y aplicarlas adecuadamente en sus tareas.