La panadería, etapa 5: la base trabaja sola

En resumen: La etapa 5 añade un índice, una vista con los totales de los pedidos, un trigger que baja las existencias y una columna nueva con ALTER TABLE.

Problema

Las cosas que hay que recordar

En la etapa 4 las existencias se bajaron con una consulta escrita a mano, y la suma de un pedido se calculaba cada vez con tres tablas. La panadería crece: los pedidos pasan a ser miles y más personas escriben consultas. Las cosas que dependen de que alguien se acuerde se olvidan tarde o temprano.

Esta etapa le pasa el trabajo a la base: un índice para buscar rápido, una vista para las sumas, un trigger para el almacén y una columna nueva para el teléfono.

La idea

Cómo busca la base

La página «Mis pedidos» pide los pedidos de un cliente. EXPLAIN QUERY PLAN muestra cómo lo hará la base:

SQLbase de datos vacía

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 2;

EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 2;

▶ EjecutarRestaurar la consultaBase nueva

SCAN orders: la base lee cada pedido y mira el cliente. Con seis pedidos da igual, con un millón es lento.

Pruébalo

El índice

Crea un índice orders_customer sobre la columna customer_id de orders.

SQLbase de datos vacía

SELECT * FROM orders WHERE customer_id = 2;

SELECT * FROM orders WHERE customer_id = 2;

Comprobar▶ Ejecutar

La misma consulta con el índice:

SQLbase de datos vacía

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 2;

EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 2;

▶ EjecutarRestaurar la consultaBase nueva

SEARCH … USING INDEX: la base va directa a los pedidos del cliente 2.

Más precisamente, para curiosos

SQLite no crea por sí solo un índice para una clave foránea, y PostgreSQL tampoco. Una columna que apunta a otra tabla casi siempre quiere un índice: por ella se unen y se buscan las tablas. El índice no es gratis, sin embargo: cada INSERT y UPDATE lo actualiza.

Pruébalo

La vista

La suma de un pedido son cada vez tres tablas y un GROUP BY. Una vista es una consulta con nombre: se lee de ella como de una tabla.

Crea una vista order_totals con el número del pedido (id), el cliente (customer_id), el estado y la suma (total, redondeada a 2 decimales) de cada pedido.

SQLbase de datos vacía

CREATE VIEW order_totals AS
SELECT id, customer_id, status
FROM orders;

CREATE VIEW order_totals AS SELECT id, customer_id, status FROM orders;

Comprobar▶ Ejecutar

Míralo

La vista como tabla

Cuánto ha gastado cada cliente, sin los pedidos cancelados. La suma con las tres tablas está escondida en la vista:

SQLbase de datos vacía

SELECT c.name, SUM(t.total) AS spent
FROM order_totals t
JOIN customers c ON c.id = t.customer_id
WHERE t.status <> 'cancelled'
GROUP BY c.id
ORDER BY spent DESC;

SELECT c.name, SUM(t.total) AS spent FROM order_totals t JOIN customers c ON c.id = t.customer_id WHERE t.status <> 'cancelled' GROUP BY c.id ORDER BY spent DESC;

▶ EjecutarRestaurar la consultaBase nueva

La vista no guarda datos, guarda su consulta. Un pedido nuevo aparece en ella de inmediato:

SQLbase de datos vacía

INSERT INTO orders (id, customer_id, ordered_at) VALUES (7, 5, '2026-10-25');
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (7, 3, 2, 1.60);
SELECT * FROM order_totals WHERE id = 7;

INSERT INTO orders (id, customer_id, ordered_at) VALUES (7, 5, '2026-10-25'); INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (7, 3, 2, 1.60); SELECT * FROM order_totals WHERE id = 7;

▶ EjecutarRestaurar la consultaBase nueva

Pruébalo

El trigger

Un trigger es código que la base ejecuta por sí sola ante un INSERT, UPDATE o DELETE en una tabla. En su cuerpo new es la fila nueva.

Crea un trigger take_stock: después de cada fila nueva en order_items, las existencias del producto de la fila bajan en sus unidades.

SQLbase de datos vacía

CREATE TRIGGER take_stock
AFTER INSERT ON order_items
BEGIN
  SELECT 1;
END;

CREATE TRIGGER take_stock AFTER INSERT ON order_items BEGIN SELECT 1; END;

Comprobar▶ Ejecutar

Míralo

El trigger y las reglas

Con el trigger, Kalin pide 20 kozunaks, y hay 10:

SQLbase de datos vacía

INSERT INTO orders (id, customer_id, ordered_at) VALUES (7, 5, '2026-10-25');
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (7, 5, 20, 6.50);

INSERT INTO orders (id, customer_id, ordered_at) VALUES (7, 5, '2026-10-25'); INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (7, 5, 20, 6.50);

▶ EjecutarRestaurar la consultaBase nueva

El UPDATE del trigger rompe CHECK (stock >= 0). El trigger es parte del INSERT, así que la fila del pedido tampoco entra: la base no tiene una fila que diga «20 kozunaks vendidos» cuando no están.

Más precisamente, para curiosos

Los triggers mantienen una regla en un solo lugar, pero están escondidos: quien lee el código del programa no los ve. Por eso se usan con moderación, para cosas que siempre tienen que ocurrir, escriba lo que escriba en la base. Las existencias aquí son una de esas cosas, y enviar un correo no lo es.

Pruébalo

Una columna nueva

La panadería quiere llamar a los clientes cuando el pedido esté listo. La tabla tiene datos, así que no se crea de nuevo: ALTER TABLE le añade una columna.

Añade a customers una columna phone de tipo TEXT que pueda faltar.

SQLbase de datos vacía

SELECT * FROM customers;

SELECT * FROM customers;

Comprobar▶ Ejecutar

Con NOT NULL no funciona:

SQLbase de datos vacía

ALTER TABLE customers ADD COLUMN phone TEXT NOT NULL;

ALTER TABLE customers ADD COLUMN phone TEXT NOT NULL;

▶ EjecutarRestaurar la consultaBase nueva

Más precisamente, para curiosos

ALTER TABLE en SQLite puede añadir, renombrar y (desde la 3.35) eliminar columnas. No puede añadir una restricción como CHECK o una clave foránea a una columna existente: entonces la tabla se crea de nuevo (una tabla nueva, INSERT … SELECT, DROP de la vieja y un cambio de nombre). En PostgreSQL ALTER TABLE … ADD CONSTRAINT lo hace directamente.

La idea

Lo que viene

La base está llena, es rápida y mantiene sus reglas por sí sola. La última etapa es el final: un informe, una copia de la base y DROP de todo en el orden correcto.

Test

Ponte a prueba

1. ¿La vista order_totals guarda las sumas de los pedidos?

Sí, como una tablaLas guarda hasta un reinicioNo, guarda la consulta. Las sumas se calculan cada vez que alguien lee de la vista

2. El trigger baja las existencias y el producto se agota. ¿Qué pasa con la fila de order_items?

Se escribe con NULLNo se escribe: el trigger es parte del INSERT, y el error dentro de él cancela también la filaSe escribe, y las existencias se quedan en 0

3. ¿Por qué ALTER TABLE customers ADD COLUMN phone TEXT NOT NULL da un error?

Los clientes que ya existen tendrían el teléfono vacío, y NOT NULL no lo permiteALTER TABLE no existe en SQLiteTEXT no puede ser NOT NULL

Comprobar

Empezar la lección de nuevo

Lecciones relacionadas

• Se basa en: Vistas · Triggers · Índices · EXPLAIN: el plan de la consulta