Cómo utilizar claves externas en SQL

El autor seleccionó a Apache Software Foundation para recibir una donación como parte del programa Write for DOnations .

Introducción

Al trabajar en un proyecto SQL de gran tamaño, debe mantener la precisión y la coherencia de los datos en todas las tablas con claves externas. Una clave externa es una columna o un grupo de columnas en una tabla de base de datos relacional que proporciona un vínculo entre los datos de dos tablas. En este caso de uso, aquí es donde entra en juego la integridad referencial. Por ejemplo, puede tener una employeestabla con una columna denominada job_title_idque haga referencia a una tabla de búsqueda denominada job_titles.

Otro ejemplo se puede demostrar en una base de datos de comercio electrónico donde se puede crear una category_idcolumna en una productstabla que se vincule a una products_categoriestabla principal.

La integridad referencial garantiza que todas las referencias de datos sean válidas y evita entradas incoherentes o registros huérfanos. La integridad referencial también es útil para evitar entradas de datos no válidos en un entorno de base de datos multiusuario.

En esta guía, aplicará la integridad referencial con claves externas en su base de datos. Aunque esta guía se ha probado en una base de datos MySQL, puede funcionar en otras bases de datos basadas en SQL con solo unos pocos cambios de sintaxis.

Prerrequisitos

Para completar este tutorial, necesitará lo siguiente:

• Un servidor Ubuntu 20.04 protegido con un usuario no root sudoy un firewall básico. Siga la guía Configuración inicial del servidor para Ubuntu 20.04 para crear un usuario no root y habilitar un firewall.

• Un servidor de base de datos MySQL. Revisa el tutorial sobre cómo instalar MySQL en un servidor Ubuntu 20.04 para configurar un servidor de base de datos.

Paso 1: Configuración de una base de datos y tablas de muestra

En este paso, creará una base de datos de muestra y configurará algunas tablas. También insertará algunos datos de muestra que usará para trabajar con claves externas a lo largo de la guía.

Comience por conectarse a su servidor como usuario no root. Luego, ejecute el siguiente comando para iniciar sesión en su servidor MySQL. Reemplace example_usercon el nombre exacto de su cuenta no root.

sudo mysql -u example_user -p

Cuando se le solicite, ingrese la contraseña de la cuenta de usuario no root de su servidor MySQL y presione ENTERo RETURNpara continuar. A continuación, ejecute el siguiente comando SQL para crear una company_dbbase de datos de muestra:

CREATE DATABASE company_db;

Confirme la siguiente salida para asegurarse de que la base de datos se haya creado sin errores.

OutputQuery OK, 1 row affected (0.01 sec)

Una vez que haya creado correctamente la base de datos sin mensajes de error en la salida, aplique la USEpalabra clave SQL para cambiar a su nueva company_dbbase de datos:

USE company_db;

Debería ver la siguiente confirmación que muestra que ha cambiado exitosamente a la company_dbbase de datos:

OutputDatabase changed

A continuación, configure una job_titlestabla con el CREATE TABLEcomando. Esta tabla funciona como una tabla de búsqueda para todos los puestos de trabajo disponibles en su base de datos. Se job_title_idtrata de una clave principal que identifica de forma única cada puesto de trabajo en su base de datos utilizando el BIGINTtipo de datos que puede alojar hasta 2^63-1registros. Está utilizando la AUTO_INCREMENTpalabra clave para permitir que MySQL asigne automáticamente valores numéricos secuenciales cada vez que inserte un nuevo puesto de trabajo.

En el CREATE TABLEcomando, incluya una job_title_namecolumna que almacene un valor legible para el puesto de trabajo. Esta columna almacena valores de cadena con una longitud máxima de 50caracteres. Definirá este tipo de datos con la sintaxis VARCHAR(50).

Siguiendo CREATE TABLEel comando, indique a MySQL que utilice el InnoDBmotor de base de datos incluyendo la ENGINE = InnoDBpalabra clave. Se trata de un motor de almacenamiento de propósito general, preparado para transacciones, que gestiona la concurrencia y, al mismo tiempo, garantiza una alta confiabilidad y un alto rendimiento en su aplicación de base de datos.

Ejecute el siguiente comando para crear la job_titlestabla:

CREATE TABLE job_titles (
    job_title_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    job_title_name VARCHAR(50)
) ENGINE = InnoDB;

Después de ejecutar la CREATE TABLE job_titles...declaración, asegúrese de que su comando se completó correctamente confirmando el siguiente resultado:

OutputQuery OK, 0 rows affected (0.03 sec)

Ahora tiene una tabla de búsqueda de todos los puestos válidos disponibles en su empresa de ejemplo. A continuación, inserte algunos puestos de muestra en la job_titlestabla:

INSERT INTO job_titles (job_title_name) VALUES ('BRANCH MANAGER');
INSERT INTO job_titles (job_title_name) VALUES ('CLERK');
INSERT INTO job_titles (job_title_name) VALUES ('LEVEL 1 SUPERVISOR');

Después de cada comando, debería recibir el siguiente mensaje de confirmación:

OutputQuery OK, 1 row affected (0.00 sec)...

Ahora que ha insertado los títulos de trabajo disponibles, utilice la SELECTpalabra clave MySQL para consultar la job_titlestabla y verificar sus datos:

SELECT
job_title_id,
job_title_name            
FROM job_titles;

Ahora debería ver una lista de todos los puestos disponibles que se muestran a continuación:

Output+--------------+--------------------+| job_title_id | job_title_name     |+--------------+--------------------+|            1 | BRANCH MANAGER     ||            2 | CLERK              ||            3 | LEVEL 1 SUPERVISOR |+--------------+--------------------+3 rows in set (0.00 sec)

A continuación, crea una employeestabla. Esta tabla contiene registros de todos los miembros del personal de la empresa. La job_title_idcolumna de la employeestabla apunta a la misma columna de la job_titlestabla. Esto se logra mediante la emisión de la instrucción FOREIGN KEY (job_title_id) REFERENCES job_titles (job_title_id). Por motivos de coherencia, se utiliza el BIGINTtipo de datos que se utilizó para las columnas relacionadas.

En la siguiente employeestabla, el employees_ides PRIMARY KEYy ha utilizado la AUTO_INCREMENTpalabra clave para generar nuevos valores employees_idsa medida que inserta nuevos valores.

Estás capturando los nombres de los empleados mediante los campos de texto first_namey last_namecon una longitud máxima de 50caracteres. Este tipo de datos también es perfecto para el número de teléfono. Por lo tanto, un VARCHAR(50)tipo de datos debería funcionar para los campos first_name, last_namey phone.

Para mejorar la velocidad al recuperar datos de las dos tablas interconectadas, utilice la declaración INDEX (job_title_id)para indexar la job_title_idcolumna. Nuevamente, asegúrese de incluir la palabra clave ENGINE = InnoDBpara aprovechar el InnoDBmotor de almacenamiento como se describe en el Paso 1 .

Para crear la employeestabla, ejecute el siguiente comando:

CREATE TABLE employees (
    employee_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    job_title_id BIGINT NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    phone VARCHAR(50),
    INDEX (job_title_id),
    FOREIGN KEY (job_title_id) REFERENCES job_titles (job_title_id) 
) ENGINE = InnoDB;

Asegúrese de obtener el siguiente resultado que confirma que ha creado la tabla:

OutputQuery OK, 0 rows affected (0.04 sec)

Ahora que ha configurado la base de datos y las tablas correctas para fines de prueba, verá qué ocurre a continuación al insertar datos en la tabla.

Paso 2: Inserción de datos no válidos

En este paso, insertará algunos registros huérfanos en la employeestabla. En este caso, los registros huérfanos son registros con valores no válidos job_title_ids. En su job_titlestabla, solo tiene 3 cargos válidos, como se muestra a continuación.

1. BRANCH MANAGER
2. CLERK
3. LEVEL 1 SUPERVISOR

Ahora, intente agregar algunos registros no válidos a la employeestabla ejecutando las siguientes INSERTdeclaraciones:

INSERT INTO employees (job_title_id, first_name, last_name, phone) VALUES (4, 'JOHN', 'DOE', '11111');
INSERT INTO employees (job_title_id, first_name, last_name, phone) VALUES (15, 'MARY', 'SMITH', '22222');
INSERT INTO employees (job_title_id, first_name, last_name, phone) VALUES (7, 'JANE', 'MIKE', '33333');

Todas las declaraciones anteriores INSERTdeberían fallar y mostrar los siguientes errores ya que 4, 15, y 7no son válidos job_title_ids.

OutputERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`company_db`.`employees`, CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`job_title_id`) REFERENCES `job_titles` (`job_title_id`))...

En el siguiente paso, ingresará datos válidos en la employeestabla y verificará si los comandos tienen éxito.

Paso 3: Inserción de datos válidos

Ya ha visto cómo la integridad referencial impide la entrada de datos no válidos cuando las tablas están interconectadas con claves externas. En otras palabras, el uso de claves externas mantiene su base de datos en un estado coherente incluso sin tener que codificar necesariamente esa lógica empresarial en una aplicación cliente externa.

En este paso, insertará datos válidos y comprobará si la inserción se realiza correctamente. Ejecute los siguientes comandos:

INSERT INTO employees (job_title_id, first_name, last_name, phone) VALUES (2, 'PETER', 'SMITH', '55555');
INSERT INTO employees (job_title_id, first_name, last_name, phone) VALUES (1, 'JOHN', 'DOE', '11111');
INSERT INTO employees (job_title_id, first_name, last_name, phone) VALUES (2, 'STEVE', 'KIM', '66666');
INSERT INTO employees (job_title_id, first_name, last_name, phone) VALUES (3, 'MARY', 'ROE', '22222');
INSERT INTO employees (job_title_id, first_name, last_name, phone) VALUES (2, 'JANE', 'MIKE', '33333');

Como estás insertando valores válidos job_title_ids, las INSERTinstrucciones se ejecutarán correctamente. Después de ejecutar cada INSERTcomando, recibirás el siguiente resultado:

OutputQuery OK, 1 row affected (0.00 sec)...

A esta altura, habrás notado que implementar la integridad referencial es un enfoque útil para validar datos y evitar la entrada de registros inexistentes al trabajar con tablas interconectadas. Además, al usar claves externas, estás creando una base de datos optimizada que te permite consultar datos interconectados de manera eficiente.

Por ejemplo, para recuperar los registros de todos los miembros del personal con los nombres de los puestos escritos, ejecute la siguiente JOINdeclaración en las tablas employeesy job_titles.

SELECT
employee_id,
employees.job_title_id,
job_titles.job_title_name,
first_name,
last_name,
phone      
FROM employees
LEFT JOIN job_titles
ON employees.job_title_id = job_titles.job_title_id;

En el resultado a continuación, la información de cada empleado ahora se muestra junto con su función/posición asociada:

Output+-------------+--------------+--------------------+------------+-----------+-------+| employee_id | job_title_id | job_title_name     | first_name | last_name | phone |+-------------+--------------+--------------------+------------+-----------+-------+|           5 |            1 | BRANCH MANAGER     | JOHN       | DOE       | 11111 ||           4 |            2 | CLERK              | PETER      | SMITH     | 55555 ||           6 |            2 | CLERK              | STEVE      | KIM       | 66666 ||           8 |            2 | CLERK              | JANE       | MIKE      | 33333 ||           7 |            3 | LEVEL 1 SUPERVISOR | MARY       | ROE       | 22222 |+-------------+--------------+--------------------+------------+-----------+-------+5 rows in set (0.00 sec)

Como puedes ver en el resultado anterior, tienes uno BRANCH MANAGER, tres de CLERKy uno LEVEL 1 SUPERVISOR.

Las claves externas también son excelentes para evitar la eliminación de registros principales a los que ya hace referencia una clave externa en una tabla secundaria vinculada. A continuación, se muestran algunos ejemplos reales en los que puede aplicar esto:

• En un sitio web de comercio electrónico, puede evitar la eliminación accidental de los detalles del cliente de una customerstabla cuando tiene pedidos activos para el cliente en la salestabla.

• En un sistema de biblioteca, puede evitar la eliminación de un estudiante de una registerstabla cuando el estudiante tiene registros asociados en la issued_bookstabla.

• En un banco, puede utilizar el método de claves externas para evitar eliminar registros de la savings_accountstabla cuando un cliente ya ha realizado algunos depósitos o retiros en la savings_accounts_transactionstabla.

De manera similar, puedes intentar eliminar datos de tu tabla. En la terminal de línea de comandos, elimina una sola posición de la job_titlestabla:

DELETE FROM job_titles 
WHERE job_title_id = 1 ;

Dado que ya ha insertado un registro en la employeestabla con un título de BRANCH MANAGER, la DELETEinstrucción fallará y mostrará el siguiente error:

OutputERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`company_db`.`employees`, CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`job_title_id`) REFERENCES `job_titles` (`job_title_id`))

Nuevamente, agrega un nuevo rol a la job_titlestabla:

INSERT INTO job_titles (job_title_name) VALUES ('CEO');

Debería recibir el siguiente resultado una vez que haya ejecutado el comando correctamente.

OutputQuery OK, 1 row affected (0.00 sec)...

Nuevamente, consulta la job_titlestabla para verificar la job_title_idnueva posición:

SELECT
job_title_id,
job_title_name            
FROM job_titles;

Ahora debería ver una lista de todos los puestos disponibles, como se muestra a continuación. El puesto CEOtiene job_title_id:4

Output+--------------+--------------------+| job_title_id | job_title_name     |+--------------+--------------------+|            1 | BRANCH MANAGER     ||            2 | CLERK              ||            3 | LEVEL 1 SUPERVISOR ||            4 | CEO                |+--------------+--------------------+4 rows in set (0.00 sec)

Ahora tiene 4 filas en la tabla. A continuación, elimine el nuevo rol con job_title_idof 4antes de ingresar cualquier registro asociado en la employeestabla.

DELETE FROM job_titles 
WHERE job_title_id = 4 ;

La DELETEdeclaración ahora debería tener éxito.

OutputQuery OK, 1 row affected (0.00 sec)

Después de completar todas las pruebas anteriores sin ningún error, ahora está claro que sus claves externas funcionan como se esperaba.

Conclusión

En esta guía, ha configurado una base de datos de muestra con tablas interconectadas y ha practicado el uso de la integridad referencial en un sistema de gestión de bases de datos relacionales. Ha visto la importancia de las claves externas para validar y evitar la eliminación de datos que, de otro modo, pondrían a la base de datos en un estado incoherente. Utilice los conocimientos de esta guía en su próximo proyecto de base de datos para aprovechar las claves externas.

Para practicar más con su base de datos MySQL, consulte estos tutoriales:

• Cómo utilizar la extensión PHP PDO para realizar transacciones MySQL en PHP en Ubuntu 18.04

• Cómo implementar paginación en MySQL con PHP en Ubuntu 18.04