Curso de MariaDB / Projetar relações de um para muitos, muitos para muitos e chaves estrangeiras

Projete relações definindo o que cada linha representa e conectando chaves primárias e estrangeiras. Um cliente pode ter vários pedidos; a relação de muitos para muitos entre pedidos e produtos é representada por itens de pedido com quantidades e preços unitários registrados na compra.

Definir as linhas e a direção de cada relação

Guardar o nome do cliente e uma lista de produtos em um único texto de pedido exige separar esse texto para calcular quantidades e repete informações nas atualizações. Separe clientes, produtos, pedidos e itens conforme os fatos e as regras de conservação necessários, em vez de dividir tabelas sem um propósito definido.

Tabela da prática Uma linha representa Identificação ou relação
c25_customers Um cliente Chave primária customer_id
c25_products Um tipo de produto Chave primária product_id
c25_orders Um pedido customer_id referencia seu cliente
c25_items Um produto em um pedido Chave primária composta order_id + product_id

Cada item guarda unit_price, o preço unitário da compra. Assim, é possível reproduzir o valor de um pedido antigo mesmo quando o preço atual do produto muda. Se um pedido admitir várias linhas do mesmo produto com opções diferentes, avalie um identificador de item ou uma chave de opções; a chave composta simplificada daqui aceita somente uma combinação de pedido e produto.

Um cliente pode ter vários pedidos; cada item liga um pedido a um produto e conserva a quantidade e o preço unitário da compra

O esquema permite um cliente sem pedidos e um produto sem vendas. Cada pedido exige um cliente, e cada item exige um pedido e um produto. As chaves estrangeiras sozinhas não obrigam que um pedido tenha pelo menos um item; essa condição exige uma regra adicional. A referência oficial de chaves estrangeiras explica as verificações de integridade.

Criar o exemplo completo com chaves estrangeiras

Conecte como course_user ao servidor de aprendizado e selecione shopcourse. O guia do cliente e de arquivos SQL UTF-8 explica como salvar o código e executar SOURCE C:/mariadb-lab/lesson.sql;, substituindo o caminho pelo real. Execute SQL dentro do cliente MariaDB.

Antes de criar c25_customers, c25_products, c25_orders e c25_items, confira SELECT VERSION(), DATABASE(); e SHOW TABLES;. Se existirem, examine definições e linhas antes de decidir se pode reiniciá-las. Use somente as tabelas prescindíveis do exercício e preserve tabelas de aplicações. Confira se @@SESSION.foreign_key_checks e @@SESSION.check_constraint_checks estão ativos.

Crie e preencha primeiro clientes e produtos, depois os pedidos e itens que os referenciam. O guia de chaves primárias e compostas explica as escolhas de identificação.

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);

O SQL mantém nomes e cidades coreanos de exemplo; as explicações usam identificadores estáveis. Os produtos 1 a 4 são caderno, caneta, caneca e luminária. Os clientes 1 e 3 têm a cidade Seul; o 2, Busan; e o 4, Daejeon. Conserve os literais UTF-8 ao reproduzir os resultados. São dados da prática.

Verificar relações de um para muitos e muitos para muitos

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;

Existem quatro clientes, quatro pedidos e seis itens. O cliente 1 tem os pedidos 101 e 102; o pedido 101 contém os produtos 1 e 2; e o produto 1 aparece nos pedidos 101 e 103. Cada ligação pedido-produto é uma linha com sua quantidade e seu preço histórico.

A consulta JOIN combina linhas por customer_id para observar essas relações. Aparecem somente clientes com um pedido correspondente; a ausência do cliente 4 nessa saída não significa que tenha sido removido de customers. As relações usam identificadores: clientes podem compartilhar um nome, e nomes podem mudar.

Testar referências inexistentes e exclusão restrita

Depois do arquivo válido, execute separadamente cada um dos testes de falha abaixo. O primeiro referencia o cliente 999, que não existe. O segundo tenta excluir o cliente 1 enquanto existem pedidos que o referenciam.

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;

Ambos devem ser rejeitados pela chave estrangeira. Após cada falha, confira se o cliente 1 continua existindo e se a quantidade de pedidos permanece em quatro. Mantenha esses testes fora do arquivo normal, pois um erro pode interromper as instruções seguintes. Preserve foreign_key_checks ativo ao investigar a entrada.

Em outro projeto, uma referência filha que aceita NULL pode representar a ausência de um pai atribuído. Aqui, customer_id nos pedidos é NOT NULL para impedir esse estado. O guia de condições de entrada explica a combinação de presença e outras regras.

Escolher uma política de exclusão

CASCADE pode propagar a exclusão de um pai aos filhos. A conveniência não basta para escolhê-lo: partes diferentes de um registro podem exigir conservação, anonimização ou exclusão conforme o propósito da aplicação. Itens de pedido também podem ser necessários para devoluções e históricos. Aqui, RESTRICT evita excluir automaticamente pedidos relacionados.

Examine compatibilidade de tipos e sinal, índices e motor de armazenamento. SHOW CREATE TABLE e uma entrada deliberadamente inválida verificam mais que a presença da palavra REFERENCES em um arquivo. Uma chave estrangeira verifica referências, mas não todas as regras de negócio, como a existência de estoque suficiente para atender ao pedido.

Examinar relações opcionais e remover primeiro os filhos

Confira quais entidades podem existir antes de suas relações: cliente sem pedidos, produto sem vendas ou pedido que exige cliente. Diferencie atributos atuais dos fatos da compra. Atualizar o preço de catálogo deve preservar unit_price de um item histórico quando for necessário reproduzir o valor original.

Antes de repetir, confirme o banco selecionado, examine SHOW CREATE TABLE para cada uma das quatro tabelas e consulte suas linhas. Conserve o necessário e remova somente as tabelas prescindíveis criadas aqui, primeiro os filhos e depois os pais. DROP TABLE remove definição e dados e não é desfeito por um ROLLBACK comum; separe essa limpeza dos testes de transações.

DROP TABLE shopcourse.c25_items;
DROP TABLE shopcourse.c25_orders;
DROP TABLE shopcourse.c25_products;
DROP TABLE shopcourse.c25_customers;