Características de las restricciones de clave externa:
Algunas características importantes de la restricción de clave externa se explican a continuación.
- El tipo de datos de la clave externa que se utiliza en la tabla secundaria debe ser el mismo que el tipo de datos de la clave principal que se utiliza en la tabla principal para hacer referencia a la clave externa.
- Se puede hacer referencia a cualquier columna de índice o varias columnas como clave externa solo para la tabla InnoDB.
- Se requieren privilegios de referencia o al menos uno de los privilegios de las declaraciones SELECT, INSERT, UPDATE y DELETE para crear una clave externa.
- Una clave externa se puede crear de dos formas. Uno usando la instrucción CREATE y otro usando la instrucción ALTER.
Requisito previo:
Antes de crear una restricción de clave externa, 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 principales llamadas' libros ' y ' prestatario '. Haga una conexión con el servidor MySQL usando el mysql cliente y ejecute las siguientes instrucciones SQL para crear la base de datos y las tablas.
CREAR BASE DE DATOS Biblioteca;
USAR Biblioteca;
CREAR MESA libros(
identificación EN T NO NULO AUTOINCREMENTO ,
título varchar (50) NO NULO ,
autor varchar (50) NO NULO ,
editor varchar (50) NO NULO ,
CLAVE PRIMARIA (identificación)
) MOTOR = INNODB ;
CREAR MESA prestatarios(
identificación VARCHAR (50) NO NULO ,
nombre varchar (50) NO NULO ,
Dirección varchar (50) NO NULO ,
Email varchar (50) NO NULO ,
CLAVE PRIMARIA (identificación)
) MOTOR = INNODB ;
Definir restricción de clave externa mediante la instrucción CREATE
Cree una tabla llamada ' book_borrow_info 'Con restricciones de clave externa 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 tabla principal y book_borrow_info es la tabla secundaria. Aquí también se establecen dos restricciones con la clave externa. Estos son BORRAR CASCADA y ACTUALIZAR CASCADA . Eso significa que si cualquier clave principal se eliminará o actualizará de la tabla principal, se eliminarán los registros correspondientes relacionados con la tabla secundaria relacionada con la clave externa o se actualizará la clave externa.
CREAR MESA book_borrow_info(
préstamo_id VARCHAR (50),
book_id EN T ,
pedir prestado FECHA NO NULO ,
Fecha de regreso FECHA NO NULO ,
estado VARCHAR (15) NO NULO ,
ÍNDICE par_ind(book_id),
CLAVE PRIMARIA (préstamo_id,pedir prestado),
CLAVE EXTERNA (book_id) REFERENCIAS libros(identificación)
SOBRE ELIMINAR CASCADA
SOBRE ACTUALIZAR CASCADA
) MOTOR = INNODB ;
Ahora, ejecute las siguientes instrucciones SQL para insertar algunos registros en ambas tablas. La primera instrucción INSERT 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 incremento automático. La segunda instrucción INSERT insertará cuatro registros en book_borrow_info basado en el identificación valor de libros mesa.
INSERTAR DENTRO libros VALORES
( NULO , 'Matar a un ruiseñor', 'Harper Lee', 'Grand Central Publishing'),
( NULO , 'Cien Años de Soledad', 'Garcia Marquez', 'Lutfi Ozkok'),
( NULO , 'Un pasaje a la India', 'Forster, E.M.', 'Biblioteca de imágenes de BBC Hulton'),
( NULO , 'Hombre invisible', 'Ralph Ellison', 'Encyclopædia Britannica, Inc.');
INSERTAR DENTRO book_borrow_info VALORES
('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 externa de la tabla secundaria que no existe en el campo de clave principal 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 DENTRO book_borrow_info VALORES('195684', 10, '2020-04-15', '2020-04-30', 'Devuelto');
Después de ejecutar la siguiente instrucción DELETE, cuando el cuarto registro se eliminará del libros tabla, luego los registros relacionados de la book_borrow_info La tabla se eliminará automáticamente para la restricción de clave externa.
ELIMINAR DE libros DÓNDE identificación= 4;SELECCIONE * de libros;
SELECCIONE * de book_borrow_info;
Definir restricción de clave externa mediante la instrucción ALTER
Primero, inserte algunos registros en prestatarios tabla y esta tabla se definirá como tabla principal en la siguiente LA EDAD declaración.
INSERTAR DENTRO prestatarios VALORES('123490', 'Patrick Wood', '34 West Street LANCASTER LA14 9ZH ', '[correo electrónico protegido]'),
('157643', 'Ezra Martin', '10 The Grove BIRMINGHAM B98 1EU ', '[correo electrónico protegido]'),
('174562', 'John Innes Archie', '55 Carretera principal LIVERPOOL L2 3OD ', '[correo electrónico protegido]'),
('146788', 'Frederick Hanson', '85 Highfield Road SHREWSBURY SY46 3ME ', '[correo electrónico protegido]');
Ejecute lo siguiente LA EDAD declaración para establecer otra restricción de clave externa para book_borrow_info mesa para hacer la relación con prestatarios mesa. Aquí, préstamo_id se define como una clave externa para book_borrow_info mesa.
ALTERAR LA TABLA book_borrow_info AÑADIR RESTRICCIÓN fk_borrowerCLAVE EXTERNA(préstamo_id)REFERENCIAS prestatarios(identificación)EN BORRAR CASCADA EN ACTUALIZAR RESTRICT;
Ahora, inserte un registro en book_borrow_info con valido préstamo_id valor que existe en identificación campo de prestatarios mesa. 157643 El valor existe en la tabla de prestatarios y la siguiente instrucción INSERT se ejecutará correctamente.
INSERTAR DENTRO book_borrow_info VALORES('157643', 1, '2020-03-10', '2020-03-20', 'Devuelto');
La siguiente instrucción INSERT generará un mensaje de error porque el valor de id 195680 no existe en la tabla de prestatarios.
INSERTAR DENTRO book_borrow_info VALORES('195680', 1, '2020-04-15', '2020-04-30', 'Devuelto');
Conclusión:
Definir correctamente las restricciones de clave externa es una tarea muy importante para crear una base de datos relacional y administrar los datos entre las tablas de manera adecuada. Conocer los usos de las restricciones de clave externa 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 externa y aplicarlas correctamente en sus tareas.