Curso de MariaDB / Diseñar relaciones de uno a muchos y muchos a muchos con claves foráneas
Diseñe las relaciones definiendo qué representa cada fila y conectando claves primarias y foráneas. Un cliente puede tener varios pedidos; la relación de muchos a muchos entre pedidos y productos se representa mediante líneas de pedido con cantidades y precios unitarios registrados al comprar.
Definir las filas y la dirección de cada relación
Guardar un nombre de cliente y una lista de productos en una única cadena de pedido obliga a separar texto para calcular cantidades y repite información al actualizar datos. Separe clientes, productos, pedidos y líneas según los hechos y las reglas de conservación que necesite, en lugar de dividir tablas sin un propósito definido.
| Tabla del ejercicio | Una fila representa | Identificación o relación |
|---|---|---|
| c25_customers | Un cliente | Clave primaria customer_id |
| c25_products | Un tipo de producto | Clave primaria product_id |
| c25_orders | Un pedido | customer_id referencia a su cliente |
| c25_items | Un producto dentro de un pedido | Clave primaria compuesta order_id + product_id |
Cada línea guarda unit_price, el precio unitario de compra. Así puede reproducir el importe de un pedido anterior aunque cambie el precio actual del producto. Si un pedido admite varias líneas del mismo producto con opciones distintas, revise si necesita un identificador de línea o una clave de opciones; la clave compuesta simplificada de aquí solo admite una combinación de pedido y producto.
El esquema permite que un cliente exista sin pedidos y que un producto exista sin ventas. Cada pedido sí exige un cliente y cada línea exige un pedido y un producto. Las claves foráneas no obligan por sí solas a que un pedido tenga al menos una línea; esa condición necesita una política adicional. La referencia oficial de claves foráneas explica las comprobaciones de integridad.
Crear el ejemplo completo con claves foráneas
Conéctese como course_user al servidor de aprendizaje y seleccione shopcourse. La guía del cliente y los archivos SQL UTF-8 explica cómo guardar el código y ejecutarlo con SOURCE C:/mariadb-lab/lesson.sql;, sustituyendo la ruta por la real. Ejecute SQL dentro del cliente MariaDB.
Antes de crear c25_customers, c25_products, c25_orders y c25_items, compruebe SELECT VERSION(), DATABASE(); y SHOW TABLES;. Si existen, revise sus definiciones y filas antes de decidir si puede reiniciarlas. Utilice únicamente las tablas desechables del ejercicio, sin sustituir tablas de una aplicación. Compruebe que @@SESSION.foreign_key_checks y @@SESSION.check_constraint_checks estén activados.
Cree e inserte primero clientes y productos, y después los pedidos y las líneas que los referencian. La guía de claves primarias y compuestas explica las decisiones de identificación.
USE shopcourse;
CREATE TABLE c25_customers (
customer_id INT NOT NULL PRIMARY KEY,
customer_name VARCHAR(50) NOT NULL,
city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c25_products (
product_id INT NOT NULL PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
category VARCHAR(30) NOT NULL,
price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c25_orders (
order_id INT NOT NULL PRIMARY KEY,
customer_id INT NOT NULL,
ordered_at DATETIME NOT NULL,
status VARCHAR(20) NOT NULL,
shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
CONSTRAINT fk_c25_order_customer FOREIGN KEY (customer_id)
REFERENCES c25_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c25_items (
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL CHECK (quantity > 0),
unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
PRIMARY KEY (order_id, product_id),
CONSTRAINT fk_c25_item_order FOREIGN KEY (order_id)
REFERENCES c25_orders(order_id) ON DELETE RESTRICT,
CONSTRAINT fk_c25_item_product FOREIGN KEY (product_id)
REFERENCES c25_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c25_customers VALUES
(1,'김민수','서울'),(2,'이서연','부산'),
(3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c25_products VALUES
(1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
(3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c25_orders
(order_id,customer_id,ordered_at,status,shipping_fee) VALUES
(101,1,'2026-01-10 10:00:00','paid',1000),
(102,1,'2026-01-20 11:00:00','paid',1000),
(103,2,'2026-02-05 12:00:00','paid',1000),
(104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c25_items VALUES
(101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
(103,1,1,3500),(103,3,2,8000),(104,2,1,1500);
El SQL conserva nombres y ciudades coreanos de ejemplo; las explicaciones usan identificadores estables. Los productos 1 a 4 son cuaderno, bolígrafo, taza y lámpara. Los clientes 1 y 3 tienen la ciudad Seúl; el 2, Busan; y el 4, Daejeon. Conserve los literales UTF-8 al reproducir los resultados. Son datos de la práctica.
Verificar relaciones de uno a muchos y muchos a muchos
SHOW CREATE TABLE c25_items;
SELECT c.customer_id,c.customer_name,o.order_id
FROM c25_customers AS c JOIN c25_orders AS o
ON o.customer_id=c.customer_id ORDER BY c.customer_id,o.order_id;
SELECT COUNT(*) AS customers FROM c25_customers;
SELECT COUNT(*) AS orders_count FROM c25_orders;
SELECT COUNT(*) AS detail_rows FROM c25_items;
Hay cuatro clientes, cuatro pedidos y seis líneas. El cliente 1 tiene los pedidos 101 y 102; el pedido 101 contiene los productos 1 y 2; y el producto 1 aparece en los pedidos 101 y 103. Cada vínculo pedido-producto es una fila con su cantidad y su precio histórico.
La consulta JOIN combina filas mediante customer_id para observar esas relaciones. Solo aparecen los clientes que tienen un pedido coincidente; que el cliente 4 no aparezca en esa salida no significa que se haya eliminado de customers. Las relaciones se mantienen mediante identificadores, no por nombres: dos clientes pueden llamarse igual y un nombre puede cambiar.
Probar referencias inexistentes y eliminación restringida
Después del archivo válido, ejecute cada una de estas pruebas fallidas por separado. La primera referencia al cliente 999, que no existe. La segunda intenta eliminar al cliente 1 mientras conserva pedidos que lo referencian.
INSERT INTO c25_orders(order_id,customer_id,ordered_at,status)
VALUES(999,999,'2026-03-01 10:00:00','paid');
DELETE FROM c25_customers WHERE customer_id=1;
Ambas deben rechazarse por la clave foránea. Después de cada fallo, compruebe que el cliente 1 sigue existiendo y que la cantidad de pedidos continúa en cuatro. Mantenga estas pruebas fuera del archivo normal, porque un error puede interrumpir sus instrucciones posteriores. No desactive foreign_key_checks solo para aceptar la entrada.
En otro diseño, una referencia hija nullable puede representar que no existe un padre asignado. Aquí customer_id en pedidos es NOT NULL para impedir ese estado. La guía de condiciones de entrada explica cómo se combinan presencia y otras reglas.
Elegir una política de eliminación
CASCADE puede propagar la eliminación de un padre a sus hijos. La comodidad no basta para elegirlo: las distintas partes de un registro pueden necesitar conservación, anonimización o eliminación según el propósito de la aplicación. Las líneas de pedido también pueden ser necesarias para devoluciones o historiales. Aquí se usa RESTRICT para evitar eliminar automáticamente los pedidos relacionados.
Revise compatibilidad de tipos y signo, índices y motor de almacenamiento. SHOW CREATE TABLE y una entrada deliberadamente inválida verifican más que la presencia de una palabra REFERENCES en un archivo. Una clave foránea comprueba las referencias, pero no todas las reglas del negocio, como si hay suficientes existencias para servir un pedido.
Revisar relaciones opcionales y limpiar primero los hijos
Compruebe qué entidades pueden existir antes de sus relaciones: un cliente sin pedidos, un producto sin ventas o un pedido que exige cliente. Distinga también los atributos actuales de los hechos de la compra. Actualizar el precio de catálogo no debe alterar el unit_price de una línea histórica si necesita reproducir el importe original.
Antes de repetir, confirme la base seleccionada, revise SHOW CREATE TABLE para cada una de las cuatro tablas y consulte sus filas. Conserve lo necesario y elimine únicamente las tablas desechables creadas aquí, primero los hijos y después sus padres. DROP TABLE elimina definición y datos y no se deshace con un ROLLBACK ordinario; mantenga esta limpieza separada de las pruebas de transacciones.
DROP TABLE shopcourse.c25_items;
DROP TABLE shopcourse.c25_orders;
DROP TABLE shopcourse.c25_products;
DROP TABLE shopcourse.c25_customers;





