Projeto: a Loja Aurora Completa

Em todas as aulas você usou a Loja Aurora pronta: 7 tabelas, 45 produtos, 90 pedidos. Alguém teve de criar esse banco, tabela por tabela, decidindo cada coluna, cada chave e a ordem certa de tudo. Neste projeto, esse alguém é você. Vamos reconstruir a Aurora do zero, em versões: primeiro as categorias, depois os produtos, os clientes, os pedidos… Em cada versão você roda o banco de verdade no editor, vê o que funciona e vê o que o SQLite recusa.

Você vai aprender
  • A planejar um banco antes de escrever SQL: que tabelas existem e quem depende de quem.
  • A criar as 7 tabelas da Aurora com CREATE TABLE, PRIMARY KEY, REFERENCES, NOT NULL e DEFAULT.
  • Por que as tabelas "mães" (categorias, fornecedores, clientes) vêm antes das "filhas" (produtos, pedidos, itens).
  • A chave primária de duas colunas de itens_pedido e a chave que aponta para a própria tabela em funcionarios.
  • A conferir a sua construção: o total do pedido 1 tem de bater com o da Aurora de verdade.
  • A deixar o banco mais rigoroso que o original, com CHECK, e a guardá-lo num arquivo no terminal.

Este é um projeto: ele junta o que você viu em várias aulas. Quando um conceito aparecer, o link leva para a aula dele. Os mais usados aqui são CREATE TABLE, PRIMARY KEY, FOREIGN KEY e Inserindo Várias Linhas.

A ideia: a planta antes da obra

Ninguém levanta uma casa assentando tijolo sem planta. Com banco de dados é igual: antes do primeiro CREATE TABLE, a gente escreve no papel o que a loja precisa guardar e como as coisas se ligam. A Loja Aurora guarda sete "fichários":

TabelaUma linha é…Aponta para
categoriasum tipo de produto (Papelaria, Livros…)ninguém
fornecedoresuma empresa que vende para a lojaninguém
produtosum item à vendacategorias e fornecedores
clientesuma pessoa que compraninguém
funcionariosum empregado da lojafuncionarios (o chefe dele)
pedidosuma compra: quem comprou, quem vendeu, quandoclientes e funcionarios
itens_pedidoum produto dentro de um pedido, com a quantidadepedidos e produtos

A coluna "Aponta para" é a mais importante da planta. Um produto aponta para a sua categoria: na linha do produto há uma coluna categoria_id com o número da categoria. Por isso a categoria tem de existir antes do produto, como a gaveta do fichário tem de existir antes da ficha que diz "guarde-me na gaveta 3". Chamamos a tabela apontada de mãe e a que aponta de filha. A ordem de construção sai daí:

versão 1   categorias, fornecedores          (mães: não apontam para ninguém)
versão 2   produtos                          (filha de categorias e fornecedores)
versão 3   clientes, funcionarios            (mães dos pedidos)
versão 4   pedidos, itens_pedido             (as filhas do fim da fila)
versão 5   melhorias: CHECK
versão 6   consultas no banco completo

Se a ideia de tabelas que se apontam ainda está nebulosa, a aula Tabelas que se Relacionam explica com calma, e Modelando um Banco do Zero mostra como se chega a essa planta.

Antes de começar: o editor já tem uma Aurora

Lembre-se de como o editor do guia funciona: a cada Rodar, ele cria um banco novo e carrega a Aurora pronta. É ótimo para as aulas, mas atrapalha quem quer construí-la do zero. Veja o que já existe quando a sua caixa começa a rodar:

SELECT name, type
FROM sqlite_schema
WHERE type = 'table';
sqlite_schema
Uma tabela especial que todo banco SQLite tem: o catálogo do próprio banco, com uma linha para cada tabela, índice ou view. Você só lê; quem escreve nela é o SQLite, sempre que você cria ou apaga alguma coisa.
name, type
O nome do objeto e o tipo dele (table, index, view…).

As 7 tabelas já estão lá. Se você tentar criar categorias de novo, o SQLite recusa:

CREATE TABLE categorias (
    id   INTEGER PRIMARY KEY,
    nome TEXT NOT NULL
);

"A tabela categorias já existe." Então toda caixa deste projeto começa com o bloco 0, que apaga a Aurora pronta com DROP TABLE. A ordem dele não é à toa: apagam-se primeiro as filhas e só depois as mães, o caminho contrário ao da construção.

-- 0. Abre espaço: apaga a Aurora pronta (filhas antes das mães)
DROP TABLE itens_pedido;
DROP TABLE pedidos;
DROP TABLE produtos;
DROP TABLE funcionarios;
DROP TABLE clientes;
DROP TABLE fornecedores;
DROP TABLE categorias;

Por que a ordem importa? No editor, as chaves estrangeiras estão ligadas. Apagar categorias enquanto ainda existem produtos apontando para elas deixaria 45 produtos órfãos, e o SQLite não deixa:

DROP TABLE categorias;
Fica tudo igual em toda caixa

Como cada Rodar parte da Aurora limpa, o editor não lembra o que você construiu na versão anterior. Por isso cada caixa repete o bloco 0 e as tabelas das versões anteriores, e a novidade da versão vem marcada com o comentário -- NOVO. Fora do editor, num arquivo de verdade, você não precisaria repetir nada: no fim do projeto você guarda tudo num arquivo pelo terminal.

Versão 1: as primeiras mães (categorias e fornecedores)

Comece pela tabela mais simples da loja: categorias. Ela tem um número, um nome e uma descrição.

-- 0. Abre espaço: apaga a Aurora pronta (filhas antes das mães)
DROP TABLE itens_pedido;
DROP TABLE pedidos;
DROP TABLE produtos;
DROP TABLE funcionarios;
DROP TABLE clientes;
DROP TABLE fornecedores;
DROP TABLE categorias;

-- 1. NOVO: categorias
CREATE TABLE categorias (
    id        INTEGER PRIMARY KEY,
    nome      TEXT NOT NULL,
    descricao TEXT
);

INSERT INTO categorias (id, nome, descricao) VALUES
(1, 'Papelaria',      'Material escolar e de escritório'),
(3, 'Casa e Cozinha', 'Utensílios para o lar'),
(4, 'Livros',         'Livros, dicionários e guias');

SELECT * FROM categorias;
id INTEGER PRIMARY KEY
A chave primária: o número que identifica cada categoria, sem repetir. No SQLite, uma coluna INTEGER PRIMARY KEY ganha número sozinha se você não informar (aula PRIMARY KEY). Aqui informamos, para usar os mesmos números da Aurora.
nome TEXT NOT NULL
Toda categoria precisa de um nome: NOT NULL proíbe deixar vazio (aula NOT NULL).
descricao TEXT
Sem NOT NULL: a descrição é opcional.
INSERT INTO … VALUES (…), (…), (…)
Três categorias num comando só (aula Inserindo Várias Linhas). Pulamos o 2 de propósito: Eletrônicos chega numa missão mais adiante.

O quadro mostra os 7 DROP TABLE: ok do bloco 0, o CREATE TABLE: ok, as "3 linhas afetadas" do INSERT e, no fim, a tabela nova. É a sua primeira tabela da Aurora.

Missão: crie a outra mãe da versão 1, a tabela fornecedores, com as colunas id (chave primária), nome (obrigatório), cidade, estado, pais (obrigatório) e telefone. Depois cadastre os dois primeiros fornecedores da Aurora: 1, 'Distribuidora Piauí Center', 'Teresina', 'PI', 'Brasil', '(86) 99821-4455' e 2, 'Loja D''Água', 'Fortaleza', 'CE', 'Brasil', '(85) 98712-3344'. Repare no apóstrofo de D'Água: dentro das aspas simples, ele se escreve dobrado ('').

-- 0. Abre espaço: apaga a Aurora pronta (filhas antes das mães)
DROP TABLE itens_pedido;
DROP TABLE pedidos;
DROP TABLE produtos;
DROP TABLE funcionarios;
DROP TABLE clientes;
DROP TABLE fornecedores;
DROP TABLE categorias;

-- 1. categorias
CREATE TABLE categorias (
    id        INTEGER PRIMARY KEY,
    nome      TEXT NOT NULL,
    descricao TEXT
);
INSERT INTO categorias (id, nome, descricao) VALUES
(1, 'Papelaria',      'Material escolar e de escritório'),
(3, 'Casa e Cozinha', 'Utensílios para o lar'),
(4, 'Livros',         'Livros, dicionários e guias');

-- 2. NOVO: fornecedores (escreva aqui o CREATE TABLE e o INSERT)


SELECT * FROM categorias;

-- 0. Abre espaço: apaga a Aurora pronta (filhas antes das mães)
DROP TABLE itens_pedido;
DROP TABLE pedidos;
DROP TABLE produtos;
DROP TABLE funcionarios;
DROP TABLE clientes;
DROP TABLE fornecedores;
DROP TABLE categorias;

-- 1. categorias
CREATE TABLE categorias (
    id        INTEGER PRIMARY KEY,
    nome      TEXT NOT NULL,
    descricao TEXT
);
INSERT INTO categorias (id, nome, descricao) VALUES
(1, 'Papelaria',      'Material escolar e de escritório'),
(3, 'Casa e Cozinha', 'Utensílios para o lar'),
(4, 'Livros',         'Livros, dicionários e guias');

-- 2. NOVO: fornecedores
CREATE TABLE fornecedores (
    id       INTEGER PRIMARY KEY,
    nome     TEXT NOT NULL,
    cidade   TEXT,
    estado   TEXT,
    pais     TEXT NOT NULL,
    telefone TEXT
);
INSERT INTO fornecedores (id, nome, cidade, estado, pais, telefone) VALUES
(1, 'Distribuidora Piauí Center', 'Teresina',  'PI', 'Brasil', '(86) 99821-4455'),
(2, 'Loja D''Água',               'Fortaleza', 'CE', 'Brasil', '(85) 98712-3344');

SELECT * FROM fornecedores;

Por que estado pode ficar vazio e pais não? Porque a Aurora compra de Portugal e da Argentina, e lá não existe "UF": nos fornecedores 4 e 5 o estado é NULL. Já o país, todo fornecedor tem. Decidir o que é obrigatório é uma decisão sobre o mundo real, não sobre o SQL.

Versão 2: produtos, a primeira filha

Um produto pertence a uma categoria e vem de um fornecedor. Em vez de escrever "Papelaria" em cada produto, guardamos só o número da categoria, e dizemos ao banco que esse número tem de existir lá na tabela mãe. É isso que o REFERENCES faz:

-- 0. Abre espaço: apaga a Aurora pronta (filhas antes das mães)
DROP TABLE itens_pedido;
DROP TABLE pedidos;
DROP TABLE produtos;
DROP TABLE funcionarios;
DROP TABLE clientes;
DROP TABLE fornecedores;
DROP TABLE categorias;

-- 1. categorias (da versão 1)
CREATE TABLE categorias (
    id        INTEGER PRIMARY KEY,
    nome      TEXT NOT NULL,
    descricao TEXT
);
INSERT INTO categorias (id, nome, descricao) VALUES
(1, 'Papelaria',      'Material escolar e de escritório'),
(3, 'Casa e Cozinha', 'Utensílios para o lar'),
(4, 'Livros',         'Livros, dicionários e guias');

-- 2. fornecedores (da versão 1)
CREATE TABLE fornecedores (
    id       INTEGER PRIMARY KEY,
    nome     TEXT NOT NULL,
    cidade   TEXT,
    estado   TEXT,
    pais     TEXT NOT NULL,
    telefone TEXT
);
INSERT INTO fornecedores (id, nome, cidade, estado, pais, telefone) VALUES
(1, 'Distribuidora Piauí Center', 'Teresina',  'PI', 'Brasil', '(86) 99821-4455'),
(2, 'Loja D''Água',               'Fortaleza', 'CE', 'Brasil', '(85) 98712-3344');

-- 3. NOVO: produtos
CREATE TABLE produtos (
    id            INTEGER PRIMARY KEY,
    nome          TEXT NOT NULL,
    categoria_id  INTEGER NOT NULL REFERENCES categorias(id),
    fornecedor_id INTEGER NOT NULL REFERENCES fornecedores(id),
    preco         REAL NOT NULL,
    estoque       INTEGER NOT NULL DEFAULT 0,
    descontinuado INTEGER NOT NULL DEFAULT 0
);
INSERT INTO produtos (id, nome, categoria_id, fornecedor_id, preco, estoque) VALUES
(1,  'Caderno Pautado 96 folhas', 1, 1, 12.35, 120),
(14, 'Jogo de Panelas 5 peças',   3, 2, 249.9, 12),
(20, 'Dicionário Escolar',        4, 1, 34.6,  40);

SELECT p.nome AS produto, c.nome AS categoria, f.nome AS fornecedor,
       p.preco, p.descontinuado
FROM produtos p
JOIN categorias c   ON c.id = p.categoria_id
JOIN fornecedores f ON f.id = p.fornecedor_id;
categoria_id INTEGER NOT NULL REFERENCES categorias(id)
A chave estrangeira: o número guardado aqui tem de ser um id que existe em categorias. O nome termina em _id por costume, para avisar quem lê que é um número de outra tabela (aula FOREIGN KEY).
preco REAL NOT NULL
O preço, com casas decimais. Todo produto tem preço.
estoque INTEGER NOT NULL DEFAULT 0
Se o INSERT não disser o estoque, vale 0 (aula DEFAULT).
descontinuado … DEFAULT 0
0 = à venda, 1 = saiu de linha. O INSERT não informou essa coluna, e mesmo assim o resultado mostra 0 nos três produtos: foi o DEFAULT.
JOIN categorias c ON c.id = p.categoria_id
Na hora de ler, o JOIN segue o número até a mãe e traz o nome de volta. Guarda-se o número, mostra-se o nome.

Agora a prova de que a chave funciona. O produto 7, Fone de Ouvido Bluetooth, é da categoria 2 (Eletrônicos) e do fornecedor 3 (Atlântico Importados). Nenhum dos dois existe nesta versão.

Missão: rode como está e veja o SQLite recusar o fone (FOREIGN KEY constraint failed: "a regra da chave estrangeira falhou"). Conserte sem mexer no INSERT do fone: antes dele, cadastre a categoria 2, 'Eletrônicos', 'Acessórios e dispositivos eletrônicos' e o fornecedor 3, 'Atlântico Importados', 'São Paulo', 'SP', 'Brasil', '(11) 97654-8899'.

-- 0. Abre espaço: apaga a Aurora pronta (filhas antes das mães)
DROP TABLE itens_pedido;
DROP TABLE pedidos;
DROP TABLE produtos;
DROP TABLE funcionarios;
DROP TABLE clientes;
DROP TABLE fornecedores;
DROP TABLE categorias;

-- 1. categorias
CREATE TABLE categorias (id INTEGER PRIMARY KEY, nome TEXT NOT NULL, descricao TEXT);
INSERT INTO categorias VALUES
(1, 'Papelaria',      'Material escolar e de escritório'),
(3, 'Casa e Cozinha', 'Utensílios para o lar'),
(4, 'Livros',         'Livros, dicionários e guias');

-- 2. fornecedores
CREATE TABLE fornecedores (id INTEGER PRIMARY KEY, nome TEXT NOT NULL, cidade TEXT,
                           estado TEXT, pais TEXT NOT NULL, telefone TEXT);
INSERT INTO fornecedores VALUES
(1, 'Distribuidora Piauí Center', 'Teresina',  'PI', 'Brasil', '(86) 99821-4455'),
(2, 'Loja D''Água',               'Fortaleza', 'CE', 'Brasil', '(85) 98712-3344');

-- 3. produtos
CREATE TABLE produtos (
    id            INTEGER PRIMARY KEY,
    nome          TEXT NOT NULL,
    categoria_id  INTEGER NOT NULL REFERENCES categorias(id),
    fornecedor_id INTEGER NOT NULL REFERENCES fornecedores(id),
    preco         REAL NOT NULL,
    estoque       INTEGER NOT NULL DEFAULT 0,
    descontinuado INTEGER NOT NULL DEFAULT 0
);
INSERT INTO produtos (id, nome, categoria_id, fornecedor_id, preco, estoque) VALUES
(1,  'Caderno Pautado 96 folhas', 1, 1, 12.35, 120),
(14, 'Jogo de Panelas 5 peças',   3, 2, 249.9, 12),
(20, 'Dicionário Escolar',        4, 1, 34.6,  40);

-- NOVO: o fone (categoria 2, fornecedor 3)
INSERT INTO produtos (id, nome, categoria_id, fornecedor_id, preco, estoque) VALUES
(7, 'Fone de Ouvido Bluetooth', 2, 3, 129.9, 44);

SELECT p.nome AS produto, c.nome AS categoria, f.nome AS fornecedor
FROM produtos p
JOIN categorias c   ON c.id = p.categoria_id
JOIN fornecedores f ON f.id = p.fornecedor_id;

-- 0. Abre espaço: apaga a Aurora pronta (filhas antes das mães)
DROP TABLE itens_pedido;
DROP TABLE pedidos;
DROP TABLE produtos;
DROP TABLE funcionarios;
DROP TABLE clientes;
DROP TABLE fornecedores;
DROP TABLE categorias;

-- 1. categorias
CREATE TABLE categorias (id INTEGER PRIMARY KEY, nome TEXT NOT NULL, descricao TEXT);
INSERT INTO categorias VALUES
(1, 'Papelaria',      'Material escolar e de escritório'),
(3, 'Casa e Cozinha', 'Utensílios para o lar'),
(4, 'Livros',         'Livros, dicionários e guias');

-- 2. fornecedores
CREATE TABLE fornecedores (id INTEGER PRIMARY KEY, nome TEXT NOT NULL, cidade TEXT,
                           estado TEXT, pais TEXT NOT NULL, telefone TEXT);
INSERT INTO fornecedores VALUES
(1, 'Distribuidora Piauí Center', 'Teresina',  'PI', 'Brasil', '(86) 99821-4455'),
(2, 'Loja D''Água',               'Fortaleza', 'CE', 'Brasil', '(85) 98712-3344');

-- 3. produtos
CREATE TABLE produtos (
    id            INTEGER PRIMARY KEY,
    nome          TEXT NOT NULL,
    categoria_id  INTEGER NOT NULL REFERENCES categorias(id),
    fornecedor_id INTEGER NOT NULL REFERENCES fornecedores(id),
    preco         REAL NOT NULL,
    estoque       INTEGER NOT NULL DEFAULT 0,
    descontinuado INTEGER NOT NULL DEFAULT 0
);
INSERT INTO produtos (id, nome, categoria_id, fornecedor_id, preco, estoque) VALUES
(1,  'Caderno Pautado 96 folhas', 1, 1, 12.35, 120),
(14, 'Jogo de Panelas 5 peças',   3, 2, 249.9, 12),
(20, 'Dicionário Escolar',        4, 1, 34.6,  40);

-- as mães do fone, ANTES dele
INSERT INTO categorias VALUES (2, 'Eletrônicos', 'Acessórios e dispositivos eletrônicos');
INSERT INTO fornecedores VALUES (3, 'Atlântico Importados', 'São Paulo', 'SP', 'Brasil', '(11) 97654-8899');

-- NOVO: o fone (categoria 2, fornecedor 3)
INSERT INTO produtos (id, nome, categoria_id, fornecedor_id, preco, estoque) VALUES
(7, 'Fone de Ouvido Bluetooth', 2, 3, 129.9, 44);

SELECT p.nome AS produto, c.nome AS categoria, f.nome AS fornecedor
FROM produtos p
JOIN categorias c   ON c.id = p.categoria_id
JOIN fornecedores f ON f.id = p.fornecedor_id;

Repare que, nesta caixa, as categorias e os fornecedores foram escritos numa linha só e o INSERT deles não lista as colunas. Funciona porque os valores vêm na ordem exata em que as colunas foram criadas. É mais curto, mas mais frágil: se um dia a tabela ganhar uma coluna no meio, esse INSERT quebra. No banco "de verdade", prefira listar as colunas, como na versão 1.

Versão 3: clientes e funcionários

Os pedidos apontam para quem comprou e para quem vendeu, então essas duas mães vêm agora. Nenhuma delas depende de categorias nem de produtos, e por isso esta caixa é curta: só o bloco 0 e as duas tabelas novas.

-- 0. Abre espaço: apaga a Aurora pronta (filhas antes das mães)
DROP TABLE itens_pedido;
DROP TABLE pedidos;
DROP TABLE produtos;
DROP TABLE funcionarios;
DROP TABLE clientes;
DROP TABLE fornecedores;
DROP TABLE categorias;

-- 4. NOVO: clientes
CREATE TABLE clientes (
    id         INTEGER PRIMARY KEY,
    nome       TEXT NOT NULL,
    email      TEXT,
    cidade     TEXT,
    estado     TEXT,
    nascimento TEXT,
    vip        INTEGER NOT NULL DEFAULT 0
);
INSERT INTO clientes (id, nome, email, cidade, estado, nascimento, vip) VALUES
(1, 'José Ribamar Silva', 'jose.ribamar@exemplo.com.br', 'Teresina',  'PI', '1985-03-12', 1),
(3, 'Ana Beatriz Sousa',  'ana.beatriz@exemplo.com.br',  'São Paulo', 'SP', '1998-11-03', 1),
(7, 'Luís Otávio Brito',  NULL,                          'Parnaíba',  'PI', '1988-12-02', 0);

SELECT * FROM clientes;
email TEXT
Opcional: o Luís (cliente 7) não deu email, e o banco guarda NULL, "sem valor" (aula O Valor NULL).
nascimento TEXT
O SQLite não tem um tipo próprio para datas. A Aurora guarda a data como texto no formato AAAA-MM-DD, que se ordena certinho e que as funções de data entendem (aula Datas e Horas).
vip INTEGER NOT NULL DEFAULT 0
Sim ou não, como 1 ou 0. Quem não for informado como VIP, não é.

A tabela funcionarios tem uma novidade curiosa: a chave estrangeira chefe_id aponta para a própria tabela. O chefe de um funcionário é outro funcionário. A gerente, Marta Lustosa, não tem chefe: o chefe_id dela é NULL.

Missão: a tabela já tem a Marta, o Caio e a Luana. Cadastre a 7, 'Sofia Rocha', 'Vendedora', 3025.4, cujo chefe é a Luana (id 5). Depois troque a consulta do fim por uma que mostre cada funcionário ao lado do nome do chefe, juntando a tabela com ela mesma (aula Self Join).

-- 0. Abre espaço: apaga a Aurora pronta (filhas antes das mães)
DROP TABLE itens_pedido;
DROP TABLE pedidos;
DROP TABLE produtos;
DROP TABLE funcionarios;
DROP TABLE clientes;
DROP TABLE fornecedores;
DROP TABLE categorias;

-- 5. NOVO: funcionarios (chefe_id aponta para a própria tabela)
CREATE TABLE funcionarios (
    id       INTEGER PRIMARY KEY,
    nome     TEXT NOT NULL,
    cargo    TEXT NOT NULL,
    salario  REAL NOT NULL,
    chefe_id INTEGER REFERENCES funcionarios(id)
);
INSERT INTO funcionarios (id, nome, cargo, salario, chefe_id) VALUES
(1, 'Marta Lustosa', 'Gerente',     9800.0,  NULL),
(2, 'Caio Teles',    'Vendedor',    3200.5,  1),
(5, 'Luana Castelo', 'Supervisora', 5400.25, 1);

SELECT nome, cargo, chefe_id FROM funcionarios;

-- 0. Abre espaço: apaga a Aurora pronta (filhas antes das mães)
DROP TABLE itens_pedido;
DROP TABLE pedidos;
DROP TABLE produtos;
DROP TABLE funcionarios;
DROP TABLE clientes;
DROP TABLE fornecedores;
DROP TABLE categorias;

-- 5. NOVO: funcionarios (chefe_id aponta para a própria tabela)
CREATE TABLE funcionarios (
    id       INTEGER PRIMARY KEY,
    nome     TEXT NOT NULL,
    cargo    TEXT NOT NULL,
    salario  REAL NOT NULL,
    chefe_id INTEGER REFERENCES funcionarios(id)
);
INSERT INTO funcionarios (id, nome, cargo, salario, chefe_id) VALUES
(1, 'Marta Lustosa', 'Gerente',     9800.0,  NULL),
(2, 'Caio Teles',    'Vendedor',    3200.5,  1),
(5, 'Luana Castelo', 'Supervisora', 5400.25, 1),
(7, 'Sofia Rocha',   'Vendedora',   3025.4,  5);

SELECT f.nome AS funcionario, chefe.nome AS chefe
FROM funcionarios f
LEFT JOIN funcionarios chefe ON chefe.id = f.chefe_id;

Com LEFT JOIN, a Marta aparece na lista com o chefe NULL; com JOIN comum, ela some, porque não há ninguém para casar com o chefe_id vazio dela. As duas respostas cumprem a missão; escolha a que conta a história que você quer (aula LEFT JOIN).

E a ordem de inserção dentro da própria tabela também importa: a Luana (5) tem de entrar antes da Sofia, que aponta para ela. No mesmo INSERT com várias linhas, a ordem das linhas resolve isso.

Versão 4: pedidos e itens, o coração da loja

Chegou a vez das filhas do fim da fila. Um pedido diz quem comprou, quem vendeu, quando e o frete. Os produtos do pedido ficam numa tabela à parte, itens_pedido, com uma linha por produto. Por que separar? Porque um pedido tem vários produtos e um produto aparece em vários pedidos: é uma relação muitos-para-muitos, e ela sempre pede uma tabela no meio (aula Relação Muitos-para-Muitos).

Esta é a primeira caixa com as 7 tabelas. Ela é longa porque repete tudo; as linhas novas são as do bloco 6 e do bloco 7. Os dados são o pedido 1 da Aurora de verdade: o José Ribamar comprou um caderno, uma caneta e lápis, atendido pelo Caio, em 5 de janeiro de 2024.

-- 0. Abre espaço: apaga a Aurora pronta (filhas antes das mães)
DROP TABLE itens_pedido;
DROP TABLE pedidos;
DROP TABLE produtos;
DROP TABLE funcionarios;
DROP TABLE clientes;
DROP TABLE fornecedores;
DROP TABLE categorias;

-- 1 a 5. as mães (versões 1 a 3), só com o necessário para o pedido 1
CREATE TABLE categorias (id INTEGER PRIMARY KEY, nome TEXT NOT NULL, descricao TEXT);
CREATE TABLE fornecedores (id INTEGER PRIMARY KEY, nome TEXT NOT NULL, cidade TEXT,
                           estado TEXT, pais TEXT NOT NULL, telefone TEXT);
CREATE TABLE produtos (
    id            INTEGER PRIMARY KEY,
    nome          TEXT NOT NULL,
    categoria_id  INTEGER NOT NULL REFERENCES categorias(id),
    fornecedor_id INTEGER NOT NULL REFERENCES fornecedores(id),
    preco         REAL NOT NULL,
    estoque       INTEGER NOT NULL DEFAULT 0,
    descontinuado INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE clientes (id INTEGER PRIMARY KEY, nome TEXT NOT NULL, email TEXT, cidade TEXT,
                       estado TEXT, nascimento TEXT, vip INTEGER NOT NULL DEFAULT 0);
CREATE TABLE funcionarios (id INTEGER PRIMARY KEY, nome TEXT NOT NULL, cargo TEXT NOT NULL,
                           salario REAL NOT NULL, chefe_id INTEGER REFERENCES funcionarios(id));

INSERT INTO categorias VALUES (1, 'Papelaria', 'Material escolar e de escritório');
INSERT INTO fornecedores VALUES
(1, 'Distribuidora Piauí Center', 'Teresina', 'PI', 'Brasil', '(86) 99821-4455'),
(6, 'Norte Suprimentos',          'Teresina', 'PI', 'Brasil', '(86) 99344-2211');
INSERT INTO produtos (id, nome, categoria_id, fornecedor_id, preco, estoque) VALUES
(1, 'Caderno Pautado 96 folhas', 1, 1, 12.35, 120),
(2, 'Caneta Esferográfica Azul', 1, 1, 2.79,  500),
(3, 'Lápis Grafite HB',          1, 6, 1.15,  430);
INSERT INTO clientes (id, nome, email, cidade, estado, nascimento, vip) VALUES
(1, 'José Ribamar Silva', 'jose.ribamar@exemplo.com.br', 'Teresina', 'PI', '1985-03-12', 1);
INSERT INTO funcionarios VALUES
(1, 'Marta Lustosa', 'Gerente',  9800.0, NULL),
(2, 'Caio Teles',    'Vendedor', 3200.5, 1);

-- 6. NOVO: pedidos
CREATE TABLE pedidos (
    id             INTEGER PRIMARY KEY,
    cliente_id     INTEGER NOT NULL REFERENCES clientes(id),
    funcionario_id INTEGER NOT NULL REFERENCES funcionarios(id),
    data           TEXT NOT NULL,
    entregue_em    TEXT,
    frete          REAL NOT NULL DEFAULT 0
);
INSERT INTO pedidos (id, cliente_id, funcionario_id, data, entregue_em, frete) VALUES
(1, 1, 2, '2024-01-05', '2024-01-10', 9.9);

-- 7. NOVO: itens_pedido
CREATE TABLE itens_pedido (
    pedido_id      INTEGER NOT NULL REFERENCES pedidos(id),
    produto_id     INTEGER NOT NULL REFERENCES produtos(id),
    quantidade     INTEGER NOT NULL,
    preco_unitario REAL NOT NULL,
    PRIMARY KEY (pedido_id, produto_id)
);
INSERT INTO itens_pedido (pedido_id, produto_id, quantidade, preco_unitario) VALUES
(1, 1, 2, 12.35),
(1, 2, 1, 2.79),
(1, 3, 3, 1.15);

SELECT pr.nome AS produto, i.quantidade, i.preco_unitario,
       i.quantidade * i.preco_unitario AS subtotal
FROM itens_pedido i
JOIN produtos pr ON pr.id = i.produto_id
WHERE i.pedido_id = 1;
entregue_em TEXT
Sem NOT NULL: enquanto o pedido não chega, a data de entrega fica vazia. Na Aurora, os pedidos 86 a 90 estão assim.
frete REAL NOT NULL DEFAULT 0
Frete grátis é 0, não "sem valor". Guardar 0 em vez de NULL deixa as contas simples: soma + frete nunca vira NULL.
PRIMARY KEY (pedido_id, produto_id)
Uma chave primária de duas colunas (chave composta). Nenhuma das duas sozinha identifica a linha: o pedido 1 tem três itens, e o caderno aparece em vários pedidos. Mas o par "pedido 1, produto 1" só pode existir uma vez. Para vender dois cadernos, aumenta-se a quantidade; não se repete a linha.
preco_unitario REAL NOT NULL
O preço no dia da venda. Por que guardar de novo, se o produto já tem preço? Porque o preço do catálogo muda; o do pedido antigo não pode mudar junto. Na Aurora, o pedido 7 levou o Dicionário Escolar a 29.9, numa promoção, embora o catálogo diga 34.6.

Os subtotais são 24.7, 2.79 e 3.45, que somam 30.94 em produtos. Com o frete de 9.9, o pedido 1 fecha em 40.84. Esse número é o teste da sua construção: a mesma conta na Aurora de verdade tem de dar o mesmo resultado.

Missão: troque a consulta do fim por uma que mostre o pedido 1 numa linha só: o id do pedido, o nome do cliente, o nome do vendedor e o total (soma de quantidade * preco_unitario mais o frete). Você vai precisar de três JOIN e de um GROUP BY p.id (aula GROUP BY). Use as colunas nessa ordem.

-- (os blocos 0 a 7 estão resumidos: é a mesma construção de cima)
DROP TABLE itens_pedido; DROP TABLE pedidos; DROP TABLE produtos; DROP TABLE funcionarios;
DROP TABLE clientes; DROP TABLE fornecedores; DROP TABLE categorias;

CREATE TABLE categorias (id INTEGER PRIMARY KEY, nome TEXT NOT NULL, descricao TEXT);
CREATE TABLE fornecedores (id INTEGER PRIMARY KEY, nome TEXT NOT NULL, cidade TEXT,
                           estado TEXT, pais TEXT NOT NULL, telefone TEXT);
CREATE TABLE produtos (id INTEGER PRIMARY KEY, nome TEXT NOT NULL,
    categoria_id INTEGER NOT NULL REFERENCES categorias(id),
    fornecedor_id INTEGER NOT NULL REFERENCES fornecedores(id),
    preco REAL NOT NULL, estoque INTEGER NOT NULL DEFAULT 0,
    descontinuado INTEGER NOT NULL DEFAULT 0);
CREATE TABLE clientes (id INTEGER PRIMARY KEY, nome TEXT NOT NULL, email TEXT, cidade TEXT,
                       estado TEXT, nascimento TEXT, vip INTEGER NOT NULL DEFAULT 0);
CREATE TABLE funcionarios (id INTEGER PRIMARY KEY, nome TEXT NOT NULL, cargo TEXT NOT NULL,
                           salario REAL NOT NULL, chefe_id INTEGER REFERENCES funcionarios(id));
CREATE TABLE pedidos (id INTEGER PRIMARY KEY,
    cliente_id INTEGER NOT NULL REFERENCES clientes(id),
    funcionario_id INTEGER NOT NULL REFERENCES funcionarios(id),
    data TEXT NOT NULL, entregue_em TEXT, frete REAL NOT NULL DEFAULT 0);
CREATE TABLE itens_pedido (
    pedido_id INTEGER NOT NULL REFERENCES pedidos(id),
    produto_id INTEGER NOT NULL REFERENCES produtos(id),
    quantidade INTEGER NOT NULL, preco_unitario REAL NOT NULL,
    PRIMARY KEY (pedido_id, produto_id));

INSERT INTO categorias VALUES (1, 'Papelaria', 'Material escolar e de escritório');
INSERT INTO fornecedores VALUES
(1, 'Distribuidora Piauí Center', 'Teresina', 'PI', 'Brasil', '(86) 99821-4455'),
(6, 'Norte Suprimentos',          'Teresina', 'PI', 'Brasil', '(86) 99344-2211');
INSERT INTO produtos (id, nome, categoria_id, fornecedor_id, preco, estoque) VALUES
(1, 'Caderno Pautado 96 folhas', 1, 1, 12.35, 120),
(2, 'Caneta Esferográfica Azul', 1, 1, 2.79,  500),
(3, 'Lápis Grafite HB',          1, 6, 1.15,  430);
INSERT INTO clientes (id, nome, email, cidade, estado, nascimento, vip) VALUES
(1, 'José Ribamar Silva', 'jose.ribamar@exemplo.com.br', 'Teresina', 'PI', '1985-03-12', 1);
INSERT INTO funcionarios VALUES
(1, 'Marta Lustosa', 'Gerente',  9800.0, NULL),
(2, 'Caio Teles',    'Vendedor', 3200.5, 1);
INSERT INTO pedidos VALUES (1, 1, 2, '2024-01-05', '2024-01-10', 9.9);
INSERT INTO itens_pedido VALUES (1, 1, 2, 12.35), (1, 2, 1, 2.79), (1, 3, 3, 1.15);

-- NOVO: o pedido 1 numa linha só
SELECT * FROM pedidos;

-- (os blocos 0 a 7 estão resumidos: é a mesma construção de cima)
DROP TABLE itens_pedido; DROP TABLE pedidos; DROP TABLE produtos; DROP TABLE funcionarios;
DROP TABLE clientes; DROP TABLE fornecedores; DROP TABLE categorias;

CREATE TABLE categorias (id INTEGER PRIMARY KEY, nome TEXT NOT NULL, descricao TEXT);
CREATE TABLE fornecedores (id INTEGER PRIMARY KEY, nome TEXT NOT NULL, cidade TEXT,
                           estado TEXT, pais TEXT NOT NULL, telefone TEXT);
CREATE TABLE produtos (id INTEGER PRIMARY KEY, nome TEXT NOT NULL,
    categoria_id INTEGER NOT NULL REFERENCES categorias(id),
    fornecedor_id INTEGER NOT NULL REFERENCES fornecedores(id),
    preco REAL NOT NULL, estoque INTEGER NOT NULL DEFAULT 0,
    descontinuado INTEGER NOT NULL DEFAULT 0);
CREATE TABLE clientes (id INTEGER PRIMARY KEY, nome TEXT NOT NULL, email TEXT, cidade TEXT,
                       estado TEXT, nascimento TEXT, vip INTEGER NOT NULL DEFAULT 0);
CREATE TABLE funcionarios (id INTEGER PRIMARY KEY, nome TEXT NOT NULL, cargo TEXT NOT NULL,
                           salario REAL NOT NULL, chefe_id INTEGER REFERENCES funcionarios(id));
CREATE TABLE pedidos (id INTEGER PRIMARY KEY,
    cliente_id INTEGER NOT NULL REFERENCES clientes(id),
    funcionario_id INTEGER NOT NULL REFERENCES funcionarios(id),
    data TEXT NOT NULL, entregue_em TEXT, frete REAL NOT NULL DEFAULT 0);
CREATE TABLE itens_pedido (
    pedido_id INTEGER NOT NULL REFERENCES pedidos(id),
    produto_id INTEGER NOT NULL REFERENCES produtos(id),
    quantidade INTEGER NOT NULL, preco_unitario REAL NOT NULL,
    PRIMARY KEY (pedido_id, produto_id));

INSERT INTO categorias VALUES (1, 'Papelaria', 'Material escolar e de escritório');
INSERT INTO fornecedores VALUES
(1, 'Distribuidora Piauí Center', 'Teresina', 'PI', 'Brasil', '(86) 99821-4455'),
(6, 'Norte Suprimentos',          'Teresina', 'PI', 'Brasil', '(86) 99344-2211');
INSERT INTO produtos (id, nome, categoria_id, fornecedor_id, preco, estoque) VALUES
(1, 'Caderno Pautado 96 folhas', 1, 1, 12.35, 120),
(2, 'Caneta Esferográfica Azul', 1, 1, 2.79,  500),
(3, 'Lápis Grafite HB',          1, 6, 1.15,  430);
INSERT INTO clientes (id, nome, email, cidade, estado, nascimento, vip) VALUES
(1, 'José Ribamar Silva', 'jose.ribamar@exemplo.com.br', 'Teresina', 'PI', '1985-03-12', 1);
INSERT INTO funcionarios VALUES
(1, 'Marta Lustosa', 'Gerente',  9800.0, NULL),
(2, 'Caio Teles',    'Vendedor', 3200.5, 1);
INSERT INTO pedidos VALUES (1, 1, 2, '2024-01-05', '2024-01-10', 9.9);
INSERT INTO itens_pedido VALUES (1, 1, 2, 12.35), (1, 2, 1, 2.79), (1, 3, 3, 1.15);

-- NOVO: o pedido 1 numa linha só
SELECT p.id, c.nome AS cliente, f.nome AS vendedor,
       SUM(i.quantidade * i.preco_unitario) + p.frete AS total
FROM pedidos p
JOIN clientes c     ON c.id = p.cliente_id
JOIN funcionarios f ON f.id = p.funcionario_id
JOIN itens_pedido i ON i.pedido_id = p.id
GROUP BY p.id;

Agora a prova dos nove: a mesma consulta, sem bloco 0, na Aurora que o editor carrega. Se a sua construção está certa, o pedido 1 dá o mesmo número:

SELECT p.id, c.nome AS cliente, f.nome AS vendedor,
       SUM(i.quantidade * i.preco_unitario) + p.frete AS total
FROM pedidos p
JOIN clientes c     ON c.id = p.cliente_id
JOIN funcionarios f ON f.id = p.funcionario_id
JOIN itens_pedido i ON i.pedido_id = p.id
WHERE p.id = 1
GROUP BY p.id;

40.84 nos dois. A sua Aurora e a de verdade concordam.

Versão 5: uma Aurora mais rigorosa que a original

Agora que a construção está de pé, dá para melhorar. A Aurora original confia em quem digita: ela aceita um produto com preço negativo, e aceita descontinuado = 7. Teste na Aurora pronta, sem bloco 0:

INSERT INTO produtos (id, nome, categoria_id, fornecedor_id, preco)
VALUES (46, 'Caneta Vermelha', 1, 1, -5.0);

SELECT id, nome, preco FROM produtos WHERE preco < 0;

Entrou. Uma loja que vende caneta pagando 5 reais para o cliente levar está com um erro de digitação, não com uma promoção. A restrição CHECK ("verifique") faz o banco recusar valores que não fazem sentido (aula CHECK).

Missão: rode como está: a caneta de -5.0 entra. Acrescente à tabela produtos as regras CHECK (preco >= 0) na coluna preco e CHECK (descontinuado IN (0, 1)) na coluna descontinuado. Rode de novo: agora o banco tem de recusar a caneta, com um erro que começa por CHECK constraint failed.

-- 0. Abre espaço: apaga a Aurora pronta (filhas antes das mães)
DROP TABLE itens_pedido; DROP TABLE pedidos; DROP TABLE produtos; DROP TABLE funcionarios;
DROP TABLE clientes; DROP TABLE fornecedores; DROP TABLE categorias;

CREATE TABLE categorias (id INTEGER PRIMARY KEY, nome TEXT NOT NULL, descricao TEXT);
CREATE TABLE fornecedores (id INTEGER PRIMARY KEY, nome TEXT NOT NULL, cidade TEXT,
                           estado TEXT, pais TEXT NOT NULL, telefone TEXT);
INSERT INTO categorias VALUES (1, 'Papelaria', 'Material escolar e de escritório');
INSERT INTO fornecedores VALUES
(1, 'Distribuidora Piauí Center', 'Teresina', 'PI', 'Brasil', '(86) 99821-4455');

-- NOVO: produtos com regras
CREATE TABLE produtos (
    id            INTEGER PRIMARY KEY,
    nome          TEXT NOT NULL,
    categoria_id  INTEGER NOT NULL REFERENCES categorias(id),
    fornecedor_id INTEGER NOT NULL REFERENCES fornecedores(id),
    preco         REAL NOT NULL,
    estoque       INTEGER NOT NULL DEFAULT 0,
    descontinuado INTEGER NOT NULL DEFAULT 0
);
INSERT INTO produtos (id, nome, categoria_id, fornecedor_id, preco)
VALUES (46, 'Caneta Vermelha', 1, 1, -5.0);

SELECT id, nome, preco FROM produtos;

-- 0. Abre espaço: apaga a Aurora pronta (filhas antes das mães)
DROP TABLE itens_pedido; DROP TABLE pedidos; DROP TABLE produtos; DROP TABLE funcionarios;
DROP TABLE clientes; DROP TABLE fornecedores; DROP TABLE categorias;

CREATE TABLE categorias (id INTEGER PRIMARY KEY, nome TEXT NOT NULL, descricao TEXT);
CREATE TABLE fornecedores (id INTEGER PRIMARY KEY, nome TEXT NOT NULL, cidade TEXT,
                           estado TEXT, pais TEXT NOT NULL, telefone TEXT);
INSERT INTO categorias VALUES (1, 'Papelaria', 'Material escolar e de escritório');
INSERT INTO fornecedores VALUES
(1, 'Distribuidora Piauí Center', 'Teresina', 'PI', 'Brasil', '(86) 99821-4455');

-- NOVO: produtos com regras
CREATE TABLE produtos (
    id            INTEGER PRIMARY KEY,
    nome          TEXT NOT NULL,
    categoria_id  INTEGER NOT NULL REFERENCES categorias(id),
    fornecedor_id INTEGER NOT NULL REFERENCES fornecedores(id),
    preco         REAL NOT NULL CHECK (preco >= 0),
    estoque       INTEGER NOT NULL DEFAULT 0,
    descontinuado INTEGER NOT NULL DEFAULT 0 CHECK (descontinuado IN (0, 1))
);
INSERT INTO produtos (id, nome, categoria_id, fornecedor_id, preco)
VALUES (46, 'Caneta Vermelha', 1, 1, -5.0);

SELECT id, nome, preco FROM produtos;

O erro que aparece é CHECK constraint failed: preco >= 0: o SQLite repete a regra que foi quebrada, o que ajuda muito a achar o problema. Outras regras que caberiam na Aurora: CHECK (quantidade > 0) em itens_pedido, CHECK (vip IN (0, 1)) em clientes, CHECK (estoque >= 0) em produtos. Toda regra que o banco garante é uma regra que nenhum programa esquecido consegue quebrar.

Versão 6: o banco completo, consultado

A sua construção tinha meia dúzia de linhas por tabela. A Aurora que o editor carrega tem exatamente as mesmas tabelas, com os dados todos. Para conferir que o molde é o mesmo, peça ao catálogo o CREATE TABLE que o SQLite guardou da tabela itens_pedido e compare com o seu bloco 7:

SELECT sql
FROM sqlite_schema
WHERE name = 'itens_pedido';

Linha por linha, é o que você escreveu. Com o banco completo, a planta mostra para que serve: qualquer pergunta sobre a loja vira uma consulta que segue as chaves. Quantas linhas tem cada tabela?

SELECT 'categorias' AS tabela, COUNT(*) AS linhas FROM categorias
UNION ALL SELECT 'fornecedores', COUNT(*) FROM fornecedores
UNION ALL SELECT 'produtos',     COUNT(*) FROM produtos
UNION ALL SELECT 'clientes',     COUNT(*) FROM clientes
UNION ALL SELECT 'funcionarios', COUNT(*) FROM funcionarios
UNION ALL SELECT 'pedidos',      COUNT(*) FROM pedidos
UNION ALL SELECT 'itens_pedido', COUNT(*) FROM itens_pedido;

O UNION ALL empilha as sete contagens numa tabela só. São 220 itens em 90 pedidos.

Missão: na Aurora completa (sem bloco 0), a consulta abaixo mostra números no lugar dos nomes. Troque-a por uma que mostre os 45 produtos com três colunas chamadas exatamente produto, categoria e fornecedor, com os nomes de cada um.

SELECT nome, categoria_id, fornecedor_id
FROM produtos;

SELECT p.nome AS produto, c.nome AS categoria, f.nome AS fornecedor
FROM produtos p
JOIN categorias c   ON c.id = p.categoria_id
JOIN fornecedores f ON f.id = p.fornecedor_id;

Os relatórios de verdade da loja (faturamento por mês, melhores clientes, produtos encalhados) ficam para o próximo projeto: Relatórios da Loja.

Guardando num arquivo de verdade

O editor esquece tudo a cada Rodar. Para a sua loja existir de verdade, ela precisa morar num arquivo. No terminal, a Ana abre o sqlite3 com o nome de um arquivo novo; ele é criado na hora (aula O Terminal do SQLite). Ela começa pelas duas primeiras tabelas e faz um teste com a chave estrangeira:

$ sqlite3 minha-loja.db
SQLite version 3.50.4 2025-07-30 19:33:53
Enter ".help" for usage hints.
sqlite> CREATE TABLE categorias (id INTEGER PRIMARY KEY, nome TEXT NOT NULL);
sqlite> CREATE TABLE produtos (id INTEGER PRIMARY KEY, nome TEXT NOT NULL, categoria_id INTEGER NOT NULL REFERENCES categorias(id));
sqlite> INSERT INTO produtos VALUES (1, 'Caderno Pautado 96 folhas', 9);   « a categoria 9 não existe… e entrou!
sqlite> PRAGMA foreign_keys;
0
sqlite> PRAGMA foreign_keys = ON;
sqlite> INSERT INTO produtos VALUES (2, 'Caneta Esferográfica Azul', 9);
Runtime error: FOREIGN KEY constraint failed (19)
sqlite> .tables
categorias  produtos
sqlite> .quit

Surpresa: o primeiro INSERT, com a categoria 9 que não existe, entrou. No programa sqlite3, as chaves estrangeiras vêm desligadas em cada conexão nova, por compatibilidade com bancos antigos: PRAGMA foreign_keys respondeu 0. Depois do PRAGMA foreign_keys = ON, o segundo produto foi recusado, como no editor. (No editor elas estão ligadas porque o aurora.sql começa justamente com PRAGMA foreign_keys = ON;.) Mais sobre isso em PRAGMAs Úteis.

Ligue as chaves em toda conexão

O PRAGMA foreign_keys = ON vale só para a conexão aberta, não fica gravado no arquivo. Ponha essa linha no começo de todo script que mexe no banco, como o aurora.sql faz. E o caderno órfão que entrou antes do PRAGMA? Ele continua lá; o comando PRAGMA foreign_key_check; lista as linhas que apontam para mães inexistentes.

Na prática, ninguém digita o banco inteiro no prompt. Você junta os blocos deste projeto num arquivo de texto, digamos minha-loja.sql, começando por PRAGMA foreign_keys = ON; e sem o bloco 0 (o arquivo novo nasce vazio), e manda o sqlite3 executar tudo de uma vez: sqlite3 minha-loja.db < minha-loja.sql. É exatamente assim que o arquivo da Aurora é gerado. Os detalhes de ler e gravar arquivos ficam em Backup (.dump e .backup) e no projeto da Planilha ao Banco.

Formas diferentes de escrever a mesma tabela

A chave estrangeira pode ser escrita de dois jeitos. Os dois criam a mesma regra:

-- forma 1: na própria coluna (a da Aurora)
CREATE TABLE avaliacoes (
    id         INTEGER PRIMARY KEY,
    produto_id INTEGER NOT NULL REFERENCES produtos(id),
    nota       INTEGER NOT NULL
);

-- forma 2: no fim, como uma regra da tabela
CREATE TABLE avaliacoes2 (
    id         INTEGER PRIMARY KEY,
    produto_id INTEGER NOT NULL,
    nota       INTEGER NOT NULL,
    FOREIGN KEY (produto_id) REFERENCES produtos(id)
);

INSERT INTO avaliacoes  (produto_id, nota) VALUES (1, 5);
INSERT INTO avaliacoes2 (produto_id, nota) VALUES (1, 5);
SELECT * FROM avaliacoes;

Repare que os INSERT não informaram o id: a coluna INTEGER PRIMARY KEY se numerou sozinha, começando em 1.

FormaExemploQuando usar
Na colunaproduto_id INTEGER REFERENCES produtos(id)A chave tem uma coluna só (quase sempre). Mais curta, e a regra fica ao lado da coluna.
No fim da tabelaFOREIGN KEY (produto_id) REFERENCES produtos(id)Quando a chave tem duas ou mais colunas, ou quando você quer as regras todas juntas no fim.
Chave primária na colunaid INTEGER PRIMARY KEYUma coluna só: categorias, produtos, pedidos…
Chave primária no fimPRIMARY KEY (pedido_id, produto_id)Chave composta, como em itens_pedido. É a única forma para duas colunas.

Quando dá errado

Construir um banco é ver o SQLite dizer "não" várias vezes. Cada "não" é uma regra sua funcionando. As mensagens abaixo são as reais, rodadas na Aurora do editor.

Criou uma tabela que já existe 
CREATE TABLE produtos (id INTEGER PRIMARY KEY, nome TEXT);

O que aconteceu: table produtos already exists, "a tabela produtos já existe". No editor, é porque a Aurora vem carregada; num arquivo seu, é porque você rodou o script duas vezes.

Como consertar: apague antes (o bloco 0) ou escreva CREATE TABLE IF NOT EXISTS produtos (…), que simplesmente não faz nada se a tabela já existir. Cuidado: o IF NOT EXISTS não confere se a tabela antiga tem as mesmas colunas da nova.

Apagou a mãe antes da filha 
DROP TABLE clientes;

O que aconteceu: FOREIGN KEY constraint failed. Com as chaves ligadas, apagar clientes deixaria os 90 pedidos apontando para ninguém.

Como consertar: apague as filhas primeiro: itens_pedido, depois pedidos, e só então clientes. É a ordem do bloco 0.

Cadastrou a filha antes da mãe 
INSERT INTO pedidos (id, cliente_id, funcionario_id, data)
VALUES (91, 31, 2, '2026-10-18');

O que aconteceu: FOREIGN KEY constraint failed. Não existe cliente 31 (a Aurora tem 30). A mensagem não diz qual chave falhou; num pedido com duas chaves, confira as duas.

Como consertar: cadastre o cliente antes, ou corrija o número.

Repetiu a chave primária 
INSERT INTO itens_pedido (pedido_id, produto_id, quantidade, preco_unitario)
VALUES (1, 1, 5, 12.35);

O que aconteceu: UNIQUE constraint failed: itens_pedido.pedido_id, itens_pedido.produto_id, "a regra de valor único falhou". O pedido 1 já tem o produto 1. Repare que a mensagem cita as duas colunas da chave composta.

Como consertar: se a ideia era vender mais cadernos no mesmo pedido, aumente a quantidade com UPDATE itens_pedido SET quantidade = 5 WHERE pedido_id = 1 AND produto_id = 1; (aula UPDATE).

Esqueceu uma coluna obrigatória 
INSERT INTO produtos (id, nome, categoria_id, fornecedor_id)
VALUES (46, 'Caneta Vermelha', 1, 1);

O que aconteceu: NOT NULL constraint failed: produtos.preco. O preco é NOT NULL e não tem DEFAULT, então não há o que pôr ali. O estoque e o descontinuado, que também não foram informados, não deram erro: eles têm DEFAULT 0.

Como consertar: informe o preço.

Aspas duplas no texto 
INSERT INTO categorias (id, nome) VALUES (9, "Pet Shop");

O que aconteceu: no such column: "Pet Shop" - should this be a string literal in single-quotes?, "não existe a coluna "Pet Shop"; isto deveria ser um texto entre aspas simples?". Em SQL, aspas duplas são para nomes de coluna ou tabela; o SQLite procurou uma coluna chamada "Pet Shop" e, gentil, já sugeriu o conserto.

Como consertar: texto vai sempre entre aspas simples: 'Pet Shop'.

Nos outros bancos

No PostgreSQL, no MySQL (com o motor InnoDB, o padrão) e no SQL Server, as chaves estrangeiras valem sempre; não existe um PRAGMA para ligá-las. Outras diferenças que você encontraria ao levar a planta da Aurora para lá: dinheiro costuma ir numa coluna DECIMAL(10,2) ou NUMERIC(10,2) em vez de REAL, datas ganham o tipo DATE, e a numeração automática do id se escreve de outro jeito (veja AUTOINCREMENT e As Diferenças entre os Bancos). Este trecho é ilustrativo, de PostgreSQL, e não roda no editor:

CREATE TABLE produtos (
    id            INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nome          TEXT NOT NULL,
    categoria_id  INTEGER NOT NULL REFERENCES categorias(id),
    preco         NUMERIC(10,2) NOT NULL CHECK (preco >= 0)
);

Referência rápida

Peça da AuroraSQLAula
Chave primáriaid INTEGER PRIMARY KEYPRIMARY KEY
Chave primária compostaPRIMARY KEY (pedido_id, produto_id)PRIMARY KEY
Chave estrangeiracategoria_id INTEGER NOT NULL REFERENCES categorias(id)FOREIGN KEY
Chave para a própria tabelachefe_id INTEGER REFERENCES funcionarios(id)Self Join
Obrigatórionome TEXT NOT NULLNOT NULL
Valor padrãoestoque INTEGER NOT NULL DEFAULT 0DEFAULT
Regra de valorCHECK (preco >= 0)CHECK
Apagar uma tabelaDROP TABLE itens_pedido; (filhas primeiro)DROP TABLE
Criar só se não existirCREATE TABLE IF NOT EXISTS …CREATE TABLE
Ver as tabelas do bancoSELECT name FROM sqlite_schema WHERE type = 'table';Comandos de Ponto (.tables)
Ligar as chaves no terminalPRAGMA foreign_keys = ON;PRAGMAs Úteis

Exercícios

1. A chave que aponta

Complete a coluna de pedidos que aponta para o cliente:

cliente_id INTEGER NOT NULL clientes(id)

2. A chave de duas colunas

Complete a chave primária de itens_pedido:

PRIMARY KEY (pedido_id, )

3. Em que ordem as tabelas precisam ser criadas?

4. Por que itens_pedido guarda preco_unitario, se o produto já tem preco?

5. No programa sqlite3, a Ana inseriu um produto com uma categoria que não existe, e entrou. Por quê?

6. Qual destas regras faria o banco recusar uma quantidade zero num item de pedido?

Missão final: um cliente novo, do cadastro à compra

Missão: agora na Aurora completa (sem bloco 0). Uma cliente nova, 31, 'Ana Paula Reis', de Teresina (PI), com email ana.reis@exemplo.com.br, fez o pedido 91 em '2026-10-18', atendida pelo Caio (funcionário 2), com frete 9.9 e ainda sem entrega. Ela levou 2 Cadernos Pautados (produto 1, 12.35 cada) e 1 Mochila Escolar Azul (produto 5, 89.99). Cadastre tudo na ordem certa e termine com a consulta que mostra o nome dela e o total do pedido. O código abaixo está na ordem errada: rode para ver o erro.

INSERT INTO itens_pedido (pedido_id, produto_id, quantidade, preco_unitario) VALUES
(91, 1, 2, 12.35),
(91, 5, 1, 89.99);

INSERT INTO pedidos (id, cliente_id, funcionario_id, data, entregue_em, frete)
VALUES (91, 31, 2, '2026-10-18', NULL, 9.9);

INSERT INTO clientes (id, nome, email, cidade, estado)
VALUES (31, 'Ana Paula Reis', 'ana.reis@exemplo.com.br', 'Teresina', 'PI');

SELECT c.nome, SUM(i.quantidade * i.preco_unitario) + p.frete AS total
FROM pedidos p
JOIN clientes c     ON c.id = p.cliente_id
JOIN itens_pedido i ON i.pedido_id = p.id
WHERE p.id = 91
GROUP BY p.id;

INSERT INTO clientes (id, nome, email, cidade, estado)
VALUES (31, 'Ana Paula Reis', 'ana.reis@exemplo.com.br', 'Teresina', 'PI');

INSERT INTO pedidos (id, cliente_id, funcionario_id, data, entregue_em, frete)
VALUES (91, 31, 2, '2026-10-18', NULL, 9.9);

INSERT INTO itens_pedido (pedido_id, produto_id, quantidade, preco_unitario) VALUES
(91, 1, 2, 12.35),
(91, 5, 1, 89.99);

SELECT c.nome, SUM(i.quantidade * i.preco_unitario) + p.frete AS total
FROM pedidos p
JOIN clientes c     ON c.id = p.cliente_id
JOIN itens_pedido i ON i.pedido_id = p.id
WHERE p.id = 91
GROUP BY p.id;

O pedido da Ana Paula fecha em 124.59 (24.7 de cadernos, 89.99 da mochila e 9.9 de frete). E repare: você não informou o vip nem o nascimento dela. O vip virou 0 pelo DEFAULT, e o nascimento ficou NULL. Cada decisão que você tomou na planta trabalhou sozinha.

Resumo

  • Antes do SQL, a planta: que tabelas existem e quem aponta para quem. Quem aponta é a filha; quem é apontado é a mãe.
  • Crie e preencha as mães primeiro (categorias, fornecedores, clientes, funcionários) e as filhas depois (produtos, pedidos, itens). Para apagar, o caminho contrário.
  • INTEGER PRIMARY KEY identifica cada linha; PRIMARY KEY (a, b) faz uma chave de duas colunas; REFERENCES tabela(id) cria a chave estrangeira, que pode apontar até para a própria tabela.
  • NOT NULL, DEFAULT e CHECK transformam as regras da loja em regras do banco. Os erros FOREIGN KEY constraint failed, UNIQUE constraint failed, NOT NULL constraint failed e CHECK constraint failed são essas regras funcionando.
  • Confira a construção com um número conhecido: o pedido 1 dá 30.94 em produtos e 40.84 com o frete, na sua versão e na Aurora de verdade.
  • No terminal, o banco mora num arquivo, e as chaves estrangeiras só valem depois de PRAGMA foreign_keys = ON, em cada conexão.