Guía de relaciones en bases de datos

Una relación en una base de datos vincula filas de una tabla con filas de otra mediante una llave foránea: una columna que guarda la llave primaria de la fila relacionada. Hay tres tipos de relaciones, uno a uno, uno a muchos y muchos a muchos, y las tres se construyen con llaves foráneas. Los ejemplos funcionan en PostgreSQL, MySQL, SQLite y SQL Server.

Cómo crear relaciones en bases de datos

Agrega a la tabla hija una columna para el id del padre y declárala como llave foránea para que la base de datos rechace valores que no correspondan a una fila padre real:

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  total DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

Haz un join sobre la llave foránea para juntar las filas relacionadas. La restricción también impide filas que no apunten a nada:

-- Input: customers
-- id | name
-- ---+-----------
-- 1  | Ana Lima
-- 2  | Ben Okafor

-- Input: orders
-- id | customer_id | total
-- ---+-------------+------
-- 10 | 1           | 25.00
-- 11 | 2           | 15.50
-- 12 | 1           | 40.00

SELECT c.name, o.id AS order_id, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_id
ORDER BY o.id;

-- Output:
-- name       | order_id | total
-- -----------+----------+------
-- Ana Lima   | 10       | 25.00
-- Ben Okafor | 11       | 15.50
-- Ana Lima   | 12       | 40.00

INSERT INTO orders (id, customer_id, total) VALUES (13, 99, 10.00);
-- ERROR: insert or update on table "orders" violates foreign key constraint
-- "orders_customer_id_fkey"

DELETE FROM customers WHERE id = 1;
-- ERROR: update or delete on table "customers" violates foreign key constraint
-- "orders_customer_id_fkey" on table "orders"

Reglas que debes conocer

• SQLite ignora las llaves foráneas a menos que las actives. Ejecuta PRAGMA foreign_keys = ON; en cada conexión, o el INSERT incorrecto de arriba se va a ejecutar sin problema.
• Escribe la llave foránea en su propia línea FOREIGN KEY (...) REFERENCES ..., como arriba. MySQL 8.0 ignora sin avisar un REFERENCES escrito en la misma columna (customer_id INTEGER REFERENCES customers(id)), así que las filas incorrectas entran directo.
• Decide qué pasa al borrar un padre. Por defecto, la base de datos lo rechaza, como arriba. Agrega ON DELETE CASCADE a la llave foránea para borrar también las filas hijas, o ON DELETE SET NULL para conservarlas sin padre.
• Crea un índice en la columna de la llave foránea. Vas a hacer joins y filtrar por ella todo el tiempo, y solo MySQL la indexa por ti: CREATE INDEX idx_orders_customer_id ON orders (customer_id);
• Dale el mismo tipo que la llave a la que apunta, por ejemplo INTEGER para un id INTEGER.
• Nunca guardes una lista de ids en una sola columna, como '1,3,5'. La base de datos no puede validarla ni hacer joins con ella. Eso es una relación muchos a muchos y necesita una tabla intermedia (más abajo).

Sintaxis alternativa

Ponle nombre a la restricción para que sus errores sean más fáciles de leer y sea más fácil eliminarla después:

CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id)

Para agregar una llave foránea a una tabla existente, usa ALTER TABLE orders ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id);. SQLite no permite esto: crea una tabla nueva con la llave foránea, copia las filas e intercambia las tablas.

Para ids que genera la propia base de datos, usa GENERATED ALWAYS AS IDENTITY (o SERIAL) en PostgreSQL, AUTO_INCREMENT en MySQL, IDENTITY(1,1) en SQL Server e INTEGER PRIMARY KEY en SQLite.

¿Cuándo usar relaciones en bases de datos?

Usa una relación siempre que dos tipos de cosas estén conectadas. El tipo que necesitas depende de cuántas filas puede haber de cada lado.

Dividir una tabla en dos: uno a uno

Cada fila corresponde como máximo a una fila de la otra tabla. Agrega una llave foránea UNIQUE a la segunda tabla. Úsala para sacar columnas opcionales o poco usadas de una tabla muy ancha, o para mantener datos sensibles (usuarios y sus perfiles, empleados y sus registros de salario) con permisos separados.

Darle muchos hijos a una fila: uno a muchos

La relación más común: un cliente tiene muchos pedidos, un autor muchas publicaciones. Pon una llave foránea simple del lado de “muchos”. El padre puede ser incluso la misma tabla, como cuando employees.manager_id apunta a otro empleado.

Vincular muchas filas con muchas otras: muchos a muchos

Los estudiantes toman muchos cursos y los cursos tienen muchos estudiantes, así que ninguna tabla puede guardar el id de la otra. Agrega una tabla intermedia con una llave foránea hacia cada lado y una llave primaria sobre el par, para que el mismo vínculo no se guarde dos veces. Los datos del vínculo en sí, como una calificación, van en la tabla intermedia.

Encontrar filas sin nada del otro lado

Una relación también muestra lo que falta. Un LEFT JOIN sobre la llave foránea conserva todas las filas de la primera tabla y rellena con NULL donde no hay coincidencia, así que filtrar por NULL encuentra clientes sin pedidos o cursos que nadie ha tomado.

Ejemplos reales de relaciones en bases de datos

Usuarios y sus perfiles

profiles.user_id es UNIQUE, así que un usuario no puede tener un segundo perfil. El LEFT JOIN conserva a los usuarios que todavía no han escrito uno:

-- profiles.user_id is declared as: user_id INTEGER UNIQUE NOT NULL

-- Input: users
-- id | username
-- ---+---------
-- 1  | mia
-- 2  | raj
-- 3  | zoe

-- Input: profiles
-- id | user_id | bio
-- ---+---------+---------------------
-- 1  | 1       | Software developer
-- 2  | 2       | Marketing specialist

SELECT u.username, p.bio
FROM users u
LEFT JOIN profiles p ON p.user_id = u.id
ORDER BY u.id;

-- Output:
-- username | bio
-- ---------+---------------------
-- mia      | Software developer
-- raj      | Marketing specialist
-- zoe      | NULL

INSERT INTO profiles (id, user_id, bio) VALUES (3, 1, 'Second profile');
-- ERROR: duplicate key value violates unique constraint "profiles_user_id_key"

Empleados y sus gerentes

Una relación uno a muchos dentro de una sola tabla: manager_id es una llave foránea hacia employees.id. Haz un join de la tabla consigo misma para poner a cada empleado junto a su gerente:

-- Input: employees
-- id | name      | manager_id
-- ---+-----------+-----------
-- 1  | Maya Chen | NULL
-- 2  | Sam Ortiz | 1
-- 3  | Jo Kim    | 2
-- 4  | Lee Ross  | 2

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id
ORDER BY e.id;

-- Output:
-- employee  | manager
-- ----------+----------
-- Maya Chen | NULL
-- Sam Ortiz | Maya Chen
-- Jo Kim    | Sam Ortiz
-- Lee Ross  | Sam Ortiz

Estudiantes y sus cursos

La tabla intermedia enrollments tiene una llave foránea hacia cada lado y PRIMARY KEY (student_id, course_id). Haz el join a través de ella para listar quién toma qué:

-- Input: students
-- id | name
-- ---+------
-- 1  | Priya
-- 2  | Tomas
-- 3  | Leah

-- Input: courses
-- id | title
-- ---+----------------
-- 1  | Database Design
-- 2  | Web Development
-- 3  | Statistics

-- Input: enrollments
-- student_id | course_id | grade
-- -----------+-----------+------
-- 1          | 1         | A
-- 1          | 2         | B+
-- 2          | 1         | A-
-- 3          | 2         | A

SELECT s.name AS student, c.title AS course, e.grade
FROM enrollments e
JOIN students s ON s.id = e.student_id
JOIN courses c ON c.id = e.course_id
ORDER BY s.name, c.title;

-- Output:
-- student | course          | grade
-- --------+-----------------+------
-- Leah    | Web Development | A
-- Priya   | Database Design | A
-- Priya   | Web Development | B+
-- Tomas   | Database Design | A-

INSERT INTO enrollments VALUES (1, 1, 'B');
-- ERROR: duplicate key value violates unique constraint "enrollments_pkey"

Cursos en los que nadie se ha inscrito

NOT EXISTS es otra forma de encontrar filas sin coincidencia. Conserva cada curso que no tiene ninguna fila en la tabla intermedia:

-- Input: the courses and enrollments tables above

SELECT c.title
FROM courses c
WHERE NOT EXISTS (
  SELECT 1 FROM enrollments e WHERE e.course_id = c.id
);

-- Output:
-- title
-- ----------
-- Statistics

Más lecturas sobre relaciones en bases de datos

En este blog:

• Guía de relaciones uno a uno: uno a uno a fondo
• Guía de relaciones uno a muchos: joins, conteo y agregación de filas hijas
• Guía de relaciones muchos a muchos: tablas intermedias a fondo
• Guía de diagramas de SQL JOIN: todos los tipos de join que usarás en una relación
• Cómo usar llaves foráneas en PostgreSQL: opciones de llaves foráneas en PostgreSQL
• ON DELETE CASCADE en PostgreSQL: borrar filas hijas junto con su padre
• Llaves primarias compuestas en SQL: la llave de dos columnas que usa una tabla intermedia
• Guía de SQL CREATE TABLE: llaves y restricciones al crear una tabla

Documentación oficial:

• PostgreSQL: llaves foráneas
• MySQL: restricciones FOREIGN KEY
• SQLite: soporte de llaves foráneas
• SQL Server: restricciones de llave primaria y foránea