Clave primaria y clave foránea

En resumen: Una clave primaria es la columna que identifica de forma única cada fila de una tabla, y una clave foránea es una columna que guarda la clave primaria de una fila de otra tabla.

Problema

¿De quién es este pedido?

En la lección «La tienda» buscamos con los ojos quién hizo el pedido 60 y qué había en él. La respuesta estaba repartida en cuatro tablas: orders, customers, order_items y products.

¿Por qué la tienda no lo guarda todo en una sola tabla grande? ¿Y cómo «sabe» una tabla cuál es su fila en otra tabla?

La idea

Clave primaria

Casi todas las tablas de la tienda tienen una columna id. La excepción, order_items, está en la nota del final de la lección. El número es distinto en cada fila, nunca se repite y nunca está vacío. Por él se encuentra una fila sin ninguna duda:

SQLbase de datos: tienda

SELECT id, name, city FROM customers WHERE id = 1;

SELECT id, name, city FROM customers WHERE id = 1;

▶ EjecutarRestaurar la consultaBase nueva

Una columna así se llama clave primaria. Los nombres no sirven: dos clientes pueden llamarse igual, y un nombre puede cambiar. El número se queda.

SQLite te dice qué columna es la clave primaria. La columna pk vale 1 para ella:

SQLbase de datos: tienda

SELECT name, pk FROM pragma_table_info('customers');

SELECT name, pk FROM pragma_table_info('customers');

▶ EjecutarRestaurar la consultaBase nueva

La idea

Clave foránea

Un pedido no guarda el nombre del cliente. Guarda solo su id, en la columna customer_id:

customers

• PKidinteger
• nametext *
• emailtext *
• citytext
• joined_attext *

orders

• PKidinteger
• FKcustomer_idinteger *→ customers
• ordered_attext *
• statustext *

orders.customer_id apunta a customers.id. Una columna que guarda la clave primaria de una fila de otra tabla se llama clave foránea.

Los vínculos de una tabla los muestra pragma_foreign_key_list:

SQLbase de datos: tienda

SELECT * FROM pragma_foreign_key_list('orders');

SELECT * FROM pragma_foreign_key_list('orders');

▶ EjecutarRestaurar la consultaBase nueva

Una fila, un vínculo: de la columna customer_id (from) a la columna id (to) de la tabla customers.

Así, los datos de Maria (nombre, correo) se guardan una sola vez. Si cambia su correo, se corrige en una fila de customers, y sus 12 pedidos siguen apuntando al mismo id.

Pruébalo

Las líneas del pedido

¿A qué tablas apunta order_items? Muestra sus vínculos.

SQLbase de datos: tienda

SELECT * FROM pragma_foreign_key_list('orders');

SELECT * FROM pragma_foreign_key_list('orders');

Comprobar▶ Ejecutar

Míralo

Buscar por número

Con WHERE ya no buscamos con los ojos. ¿Quién hizo el pedido 60? Primero el pedido:

SQLbase de datos: tienda

SELECT id, customer_id, ordered_at, status FROM orders WHERE id = 60;

SELECT id, customer_id, ordered_at, status FROM orders WHERE id = 60;

▶ EjecutarRestaurar la consultaBase nueva

customer_id es 1. Luego el cliente con ese id:

SQLbase de datos: tienda

SELECT id, name, email FROM customers WHERE id = 1;

SELECT id, name, email FROM customers WHERE id = 1;

▶ EjecutarRestaurar la consultaBase nueva

Maria Ivanova. Funciona, pero hacen falta dos consultas, y pasas el número 1 a mano. Para un pedido es soportable. Para un informe de 60 pedidos con los nombres de sus clientes, no. La próxima lección junta los dos pasos en una sola consulta con JOIN.

Pruébalo

¿De quién es la reseña?

La reseña 13 tiene una puntuación de 1. ¿Quién la escribió? Muestra solo el nombre del cliente.

SQLbase de datos: tienda

SELECT * FROM reviews WHERE id = 13;

SELECT * FROM reviews WHERE id = 13;

Comprobar▶ Ejecutar

Ejercicio

Dos tareas

1. Una reseña apunta a dos cosas. Muestra los vínculos de la tabla reviews.

Los vínculos de reviews:

SQLbase de datos: tienda

Comprobar▶ Ejecutar

2. Las categorías forman un árbol: «Cocina» es una subcategoría de algo. La clave foránea parent_id apunta a una fila de la misma tabla categories. Encuentra el nombre de la categoría padre de «Cocina».

La categoría padre de «Cocina»:

SQLbase de datos: tienda

Comprobar▶ Ejecutar

Más precisamente, para curiosos

Una clave primaria puede estar formada por varias columnas. En order_items es el par (order_id, product_id): pk vale 1 y 2 para sus partes.

SQLbase de datos: tienda

SELECT name, pk FROM pragma_table_info('order_items');

SELECT name, pk FROM pragma_table_info('order_items');

▶ EjecutarRestaurar la consultaBase nueva

Una clave foránea es también una regla: la base rechaza un pedido de un cliente que no existe. En SQLite esta regla está desactivada por defecto y se activa con PRAGMA foreign_keys = ON. Aquí en el curso está activada. En PostgreSQL las claves foráneas se hacen cumplir siempre. Las verás en acción en el módulo sobre cambiar datos.

Test

Ponte a prueba

1. ¿Qué es cierto de la clave primaria de customers?

Es el nombre del clientePuede repetirse si los nombres son distintosCada cliente tiene su propio id, que nunca se repite y nunca está vacío

2. orders.customer_id es 4. ¿Qué significa?

Que el pedido tiene 4 productosQue el pedido pertenece al cliente con id 4 en customersQue es el cuarto pedido del cliente

3. ¿Por qué el nombre del cliente no se guarda en cada uno de sus pedidos?

Porque SQL no permite texto en ordersPara ahorrar espacio y evitar discrepancias: el nombre está en un solo lugarPorque los pedidos son anónimos

4. La clave primaria de order_items es (order_id, product_id). ¿Qué prohíbe?

Que un producto esté en dos pedidosQue el mismo producto esté en dos filas de un mismo pedidoQue un pedido tenga dos productos

Comprobar

Empezar la lección de nuevo

Lecciones relacionadas

• Se basa en: Por qué una base de datos · SELECT: elegir columnas · WHERE: filtrar filas
• Continúa en: JOIN: dos tablas en una consulta · LEFT JOIN: también las filas sin pareja · Muchas tablas, y una tabla unida consigo misma · INSERT: insertar filas
• Ejercicios sobre el tema: Unir tablas