Clave externa SQL: Mantén tus relaciones de bases de datos bajo control

Claves externas compuestas y autorreferenciales

Una clave compuesta consta de dos o más columnas que identifican de forma única un registro y deben referenciarse juntas. Este patrón se utiliza cuando la clave natural abarca varios campos o si deseas evitar relaciones duplicadas. Por ejemplo, puedes tener una oferta de cursos identificada por (course_id, semester) o una tabla de unión con student_id y course_id como clave primaria compuesta.

-- Defines courses table
CREATE TABLE course_offerings (
    course_id INT,
    semester VARCHAR(10),
    
    -- Composite primary key ensures a course can only be offered once per semester
    PRIMARY KEY (course_id, semester) 
);

-- Create enrollment table
CREATE TABLE enrollment (
    student_id INT,
    course_id INT,
    semester VARCHAR(10),

    -- This foreign key links enrollment records to a valid course offering
    FOREIGN KEY (course_id, semester)
        REFERENCES course_offerings(course_id, semester)
);

Una clave externa autorreferencial o clave externa recursiva es una clave externa que hace referencia a la clave principal dentro de la misma tabla. Este patrón es común en datos jerárquicos como Empleado → Gerente. Esto permite representar relaciones recursivas sin crear tablas adicionales.

En la consulta siguiente, la tabla « manager_id » hace referencia a la tabla « employee_id » en la misma tabla.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),

    -- manager_id references employee_id in the same table
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);

Trabajar con datos y garantizar la integridad

Ahora sabemos que las claves externas se utilizan para garantizar la integridad de los datos en las bases de datos. En esta sección, analizaremos las consideraciones prácticas a la hora de trabajar con claves externas para operaciones SQL, los retos más comunes y cómo gestionar las restricciones.

Operaciones INSERT, UPDATE y DELETE

Veamos cómo interactúan estas operaciones con las claves externas.

Operaciones de inserción con claves externas

Para insertar un registro secundario, el valor principal al que se hace referencia debe existir. Si no es así, la inserción falla. Por ejemplo, la consulta siguiente insertará el registro en la tabla orders si la tabla users contiene un registro en el que user_id es 1.

-- users table contains user_id = 1
INSERT INTO orders (order_id, user_id, order_date)
VALUES (100, 1, '2024-02-01');

Si intentas insertar un registro para un usuario que no existe en la tabla principal, aparecerá un error indicando una violación de la restricción de clave externa.

-- user_id = 999 does NOT exist in users table
INSERT INTO orders (order_id, user_id, order_date)
VALUES (101, 999, '2024-02-01');

ERROR: insert or update on table "orders" violates foreign key constraint "fk_orders_users"

Operaciones de actualización y comportamiento en cascada

Las actualizaciones pueden afectar tanto a la tabla de claves externas como a la tabla de claves primarias. Por ejemplo, si actualizas la clave externa en la tabla de pedidos, solo se realizará correctamente si la clave principal existe en la tabla principal. Por ejemplo, la actualización siguiente solo se realizará correctamente si users.user_id = 5 exists.

-- updating foreign key value
UPDATE orders
SET user_id = 5
WHERE order_id = 100;

En el ejemplo anterior, no puedes actualizar la clave principal sin una cláusula « ON UPDATE CASCADE », ya que las filas secundarias siguen haciendo referencia a 1. Para evitar esto, primero eliminamos la restricción de clave externa existente y, a continuación, la volvemos a crear con la opción « ON UPDATE CASCADE » (Incluir restricción de clave externa) habilitada. Con este método, los valores relacionados orders.user_id se actualizan automáticamente.

-- Drop existing foreign key constraint
ALTER TABLE orders
DROP CONSTRAINT fk_orders_users;

-- Recreate FK with ON UPDATE CASCADE enabled
ALTER TABLE orders
ADD CONSTRAINT fk_orders_users
FOREIGN KEY (user_id)
    REFERENCES users(user_id)
    ON UPDATE CASCADE;   -- Automatically update child rows when parent key changes

UPDATE users
SET user_id = 500
WHERE user_id = 1;

Operaciones de eliminación y cascadas

Si intentas eliminar una fila principal sin ninguna regla en cascada establecida, la eliminación fallará y aparecerá un error. Cuando añades una cláusula « ON DELETE CASCADE », le indicas a la base de datos que elimine automáticamente todas las filas secundarias relacionadas cuando se elimine el registro principal, para mantener la coherencia.

En el ejemplo siguiente, creamos una clave externa en orders.user_id que hace referencia a users.user_id. Con ON DELETE CASCADE, si se elimina un usuario de la tabla de usuarios, todos los pedidos relacionados se eliminarán automáticamente.

-- Add a new foreign key constraint to the orders table
ALTER TABLE orders
ADD CONSTRAINT fk_orders_users
FOREIGN KEY (user_id)
    REFERENCES users(user_id)
    ON DELETE CASCADE;             -- Delete orders automatically when user is deleted

A veces, es posible que desees que la base de datos gestione las eliminaciones automáticamente. Puedes aplicar esto con ON DELETE SET NULL al crear la tabla. Esto establecerá las columnas de clave externa en la tabla secundaria como « NULL » si el registro principal cambia o se elimina.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,               -- FK referencing users.user_id
    order_date DATE,
    FOREIGN KEY (user_id)
        REFERENCES users(user_id)
        ON DELETE SET NULL     -- If a user is deleted, set user_id in orders to NULL
);

Afrontar retos comunes

Al diseñar bases de datos complejas, es probable que te encuentres con dificultades relacionadas con las claves externas. A continuación, se describen los problemas más comunes que he experimentado con las claves externas y las soluciones prácticas para cada uno de ellos.

• Tipos de datos incompatibles: Si la clave externa y las columnas de clave primaria referenciadas tienen tipos de datos incompatibles, no se podrá crear la restricción. Para evitar esto, estandariza siempre los tipos en las tablas relacionadas.

• Dependencias circulares: Se producen cuando dos tablas se hacen referencia entre sí, lo que puede provocar bloqueos de inserción. Para resolver este problema, debes volver a evaluar el diseño del esquema o crear primero la tabla sin claves externas, cargar los datos base y, a continuación, utilizar ALTER TABLE para añadir las restricciones.

• Claves externas colgantes: Este problema se produce cuando la clave externa de un hijo apunta a una tabla principal que no existe. Para solucionar esto, utiliza acciones en cascada para validar los datos durante las operaciones de eliminación y actualización.

Gestión de modificaciones y eliminación de restricciones

A veces, es posible que quieras modificar las restricciones de clave externa al rediseñar el esquema de tu base de datos. A continuación se indican las formas de eliminar la clave externa con ligeras variaciones sintácticas entre los diferentes dialectos SQL.

En las bases de datos PostgreSQL, SQL Server y Oracle, utilizamos la instrucción « DROP CONSTRAINT ».

-- Drop foreign key
ALTER TABLE orders
DROP CONSTRAINT fk_orders_users;

Sin embargo, en MySQL, usamos la instrucción « DROP FOREIGN KEY » para eliminar la clave externa.

-- Drop foreign key
ALTER TABLE orders
DROP FOREIGN KEY fk_orders_users;

Ten en cuenta que al eliminar una restricción de clave externa, se elimina la aplicación de la integridad referencial de la base de datos. Después de la eliminación, puedes insertar o actualizar datos que infrinjan la relación, lo que puede dar lugar a datos incoherentes.

Rendimiento y optimización

En los ejemplos anteriores, hemos visto cómo las claves externas garantizan un diseño eficaz de los datos al mantener su integridad. Sin embargo, debes comprender cómo optimizar las claves externas para mejorar el rendimiento de tus bases de datos. A continuación, te ofrezco algunos consejos que me han resultado útiles al trabajar con claves externas:

Rendimiento de la indexación y las consultas

Cuando indexas una clave externa, la base de datos puede validar rápidamente los cambios durante las operaciones de inserción, actualización o eliminación localizando las claves correspondientes en la tabla principal. Del mismo modo, los índices aceleran las operaciones de unión que vinculan tablas secundarias y principales al permitir que el motor de la base de datos coincida las filas basándose en valores de claves externas.

Como práctica recomendada, te aconsejo que indexes explícitamente las columnas de claves externas si el DBMS no lo hace automáticamente.

Técnicas de optimización de consultas

Algunos motores SQL utilizan la existencia de claves externas para aplicar la eliminación de uniones, una técnica que identifica y elimina las uniones innecesarias del plan de ejecución de la consulta. Por ejemplo, si una consulta solo accede a columnas de la tabla secundaria, pero incluye una unión con una tabla principal para el filtrado garantizado por la restricción de clave externa, la operación de unión se puede optimizar.

Siempre puedes ver los planes de ejecución para comprobar cómo el optimizador de consultas gestiona las claves externas y las combinaciones. Estas optimizaciones y su eficacia varían según los motores SQL, como PostgreSQL, MySQL y SQL Server. Comprender el comportamiento del optimizador de tu base de datos específica ayuda a escribir consultas eficaces que aprovechan al máximo las restricciones de claves externas.

Por ejemplo, puedes utilizar la siguiente consulta para ver el plan de ejecución en PostgreSQL:

-- View execution plan
EXPLAIN ANALYZE
SELECT o.order_id, u.email
FROM orders o
JOIN users u ON o.user_id = u.user_id;

Esta tabla resume cómo ver el plan de ejecución en diferentes bases de datos:

Terecomiendo que utilices nuestra función curso «Unir datos en SQL» para aprender los diferentes tipos de uniones en SQL y cómo trabajar con diferentes tablas relacionadas en la base de datos.

Casos de uso avanzados y mejores prácticas

Más allá de la implementación básica, puedes utilizar claves externas para crear una arquitectura de base de datos robusta, escalable y fácil de mantener. En esta sección, explicaré los escenarios especializados y los principios de diseño de alto nivel que hay que tener en cuenta al utilizar claves externas.

Relaciones entre bases de datos o entre esquemas

Si bien las claves externas están diseñadas para garantizar la integridad dentro de una sola base de datos o esquema, los entornos empresariales más complejos a menudo requieren relaciones que trascienden estos límites. La siguiente tabla resume la compatibilidad entre esquemas y bases de datos para diferentes dialectos SQL.

Mantenimiento y auditoría

A medida que crees claves externas, notarás que cambian cada vez que cambia tu lógica de negocio. Ten en cuenta las siguientes formas de auditar y mantener las claves externas para garantizar que estén actualizadas:

• Revisar las restricciones: Audita periódicamente las restricciones de claves externas para garantizar que sigan estando en consonancia con la evolución de las reglas empresariales y los cambios en el esquema.
• Auditoría periódica del índice: Verifica siempre que existan índices en todas las columnas de claves externas para mantener un rendimiento óptimo de unión y validación, especialmente después de importaciones de datos de gran tamaño o actualizaciones del sistema.
• Documentación y visualización: Utiliza diagramas entidad-relación (ER) o herramientas de visualización de esquemas para documentar las relaciones. Estas ayudas visuales garantizarán que todos los programadores y analistas comprendan las relaciones y las reglas de integridad que imponen tus claves externas.

Mejores prácticas para un diseño de bases de datos robusto

Para sacar el máximo partido al funcionamiento de las claves externas, sigue estas directrices:

• Alinea tus claves con la lógica empresarial real: Asegúrate de que las claves primarias y externas representen realmente entidades reales y las relaciones entre ellas, en lugar de basarse en identificadores artificiales o inestables.

• Utiliza nombres claros y coherentes: Elige nombres descriptivos para las restricciones, como « fk_orders_user_id », para que tu esquema sea más fácil de mantener, depurar y trabajar con otras personas.

• Equilibra el rigor y la flexibilidad: Las restricciones deben proteger la calidad de los datos, pero tu diseño debe seguir permitiendo casos extremos prácticos o validaciones gestionadas en la capa de aplicación.

• Evita las relaciones circulares: Estructura tu esquema de manera que las dependencias de claves externas no se repitan entre sí, ya que esto complica innecesariamente las inserciones, actualizaciones y eliminaciones.

• Optimizar el rendimiento: Indexa correctamente tus claves externas y aplica con cuidado las acciones en cascada para mantener la integridad sin ralentizar tu sistema.

Conclusión

Como con cualquier habilidad, dominar las claves externas se consigue con la práctica. Te animo a que experimentes con el modelado de esquemas, pruebes acciones en cascada y comprendas cómo se comportan las restricciones en situaciones reales. Con el tiempo, aprenderás a equilibrar las reglas estrictas con la flexibilidad que necesitan tus aplicaciones, creando bases de datos que sean resistentes y adaptables a medida que evolucionan los requisitos.

Ahora que ya sabes cómo utilizar las claves externas en el diseño de bases de datos, te recomiendo que pruebes nuestro programa de ingeniero de datos asociado en SQL para aprender los fundamentos de la ingeniería de datos y el almacenamiento de datos. Por último, si deseas mejorar tus habilidades de gestión de bases de datos para big data, te recomiendo que realices nuestro curso Introducción al modelado de datos en Snowflake para aprender más sobre el modelado dimensional.