SQL: views, índices e restrições de integridade com erros reais

Os guias de SQL do site já mostram SELECT, junções, agregações e subconsultas. Falta o outro lado das provas de banco de dados: as restrições de integridade, que impedem dados inválidos, as views, que guardam consultas com nome, e os índices, que aceleram buscas. Este guia explica cada uma com um mini banco de dados real, executado em SQLite; as mensagens de erro citadas são as observadas, e outros sistemas gerenciadores usam textos diferentes para os mesmos erros.

Sem norma aplicável: é matéria técnica. Antes, vale ver comandos DDL, DML, DCL e TCL e modelagem de dados.

O banco de teste

Duas tabelas: setor (id, nome) e servidor (id, nome, setor_id, nota). As restrições foram declaradas assim:

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

CREATE TABLE servidor(
  id INTEGER PRIMARY KEY,
  nome TEXT NOT NULL,
  setor_id INTEGER REFERENCES setor(id) ON DELETE RESTRICT,
  nota INTEGER CHECK(nota BETWEEN 0 AND 10));

Dados: setores 1 (TI) e 2 (RH); servidores Ana (TI, nota 9), Bruno (TI, 6), Carla (RH, 8) e Davi (RH, 4).

Restrições de integridade

Restrição	O que garante
PRIMARY KEY	Identifica cada linha: valor único e não nulo
FOREIGN KEY (REFERENCES)	O valor deve existir na tabela referenciada (integridade referencial)
UNIQUE	Não pode haver valores repetidos na coluna
NOT NULL	A coluna não aceita nulo
CHECK	O valor deve satisfazer a condição escrita

Cada restrição disparando

Tentativas feitas no banco de teste, com o resultado:

INSERT INTO servidor VALUES (5,'Eva',9,5) (setor 9 não existe): erro de chave estrangeira, FOREIGN KEY constraint failed.
DELETE FROM setor WHERE id=1 (o setor TI tem servidores): também FOREIGN KEY constraint failed, por causa do ON DELETE RESTRICT.
INSERT INTO servidor VALUES (6,'Fábio',1,11) (nota 11): CHECK constraint failed: nota BETWEEN 0 AND 10.
INSERT INTO setor VALUES (3,'TI') (nome repetido): UNIQUE constraint failed: setor.nome.
INSERT INTO servidor VALUES (7,NULL,1,5) (nome nulo): NOT NULL constraint failed: servidor.nome.
Inserir duas vezes o mesmo id numa tabela com chave primária: UNIQUE constraint failed, pois a chave primária também impede repetição.

Em SQLite, as chaves estrangeiras só são verificadas se o recurso for ligado na conexão (comando PRAGMA foreign_keys=ON); em outros sistemas elas valem sempre. Em prova, a resposta padrão é: a chave estrangeira impede inserir referência a uma linha que não existe e, conforme a regra, impede ou propaga a exclusão da linha referenciada.

ON DELETE: o que acontece ao excluir o “pai”

Regra	Efeito ao apagar a linha referenciada
RESTRICT	Impede a exclusão se houver linhas filhas
CASCADE	Apaga também as linhas filhas
SET NULL	Mantém as filhas, colocando nulo na chave estrangeira

Teste do CASCADE: uma tabela filha com duas linhas ligadas ao registro 1 da tabela pai, declarada com ON DELETE CASCADE. Ao excluir o registro 1 do pai, a contagem de linhas da filha passou a 0: as duas foram apagadas junto. Isso é útil e perigoso: confira antes de usar.

Views (visões)

Uma view é uma consulta salva com um nome; ela se usa como se fosse uma tabela, mas não guarda os dados, apenas a definição da consulta. Cada vez que é consultada, executa a consulta de novo sobre as tabelas de origem.

CREATE VIEW v_media AS
SELECT s.nome AS setor, COUNT(*) AS qtd, AVG(v.nota) AS media
FROM servidor v JOIN setor s ON s.id = v.setor_id
GROUP BY s.nome;

Consultando SELECT * FROM v_media ORDER BY setor, o resultado foi:

setor	qtd	media
RH	2	6,0
TI	2	7,5

Conferência: TI tem notas 9 e 6, média 7,5; RH tem 8 e 4, média 6. Depois de UPDATE servidor SET nota=10 WHERE id=4 (Davi passa de 4 para 10), a mesma consulta à view deu RH com média 9,0 (a média de 8 e 10), sem que a view tenha sido alterada. É a prova de que a view reflete os dados atuais.

Usos típicos: simplificar consultas complexas, restringir o que cada usuário enxerga (expor só algumas colunas, o que combina com o GRANT do DCL) e dar um nome estável a uma consulta. Excluir a view com DROP VIEW não apaga nenhuma linha das tabelas; depois do DROP VIEW, consultar o nome dá erro de objeto inexistente. As chamadas views materializadas (disponíveis em alguns sistemas) são diferentes: guardam o resultado fisicamente.

Índices

Um índice é uma estrutura auxiliar que acelera a busca por uma coluna, semelhante ao índice remissivo de um livro. Cria-se com CREATE INDEX idx_nome ON servidor(nome).

O plano de execução do SQLite mostrou a diferença:

SELECT * FROM servidor WHERE nome='Ana' (coluna indexada): SEARCH servidor USING INDEX idx_nome, ou seja, busca pelo índice.
SELECT * FROM servidor WHERE nota=9 (coluna sem índice): SCAN servidor, ou seja, varredura de todas as linhas.

Características para a prova:

Índices aceleram leituras, mas ocupam espaço e deixam as escritas (inserção, atualização e exclusão) mais lentas, pois o índice também precisa ser atualizado.
Colunas de chave primária e as declaradas UNIQUE costumam ganhar índice automaticamente: na listagem de objetos do banco de teste, havia um índice automático criado para a coluna UNIQUE da tabela setor.
Vale indexar colunas muito usadas em filtros e junções, e não vale indexar tudo.

Pegadinhas

Afirmar que a view guarda os dados (só a materializada guarda).
Dizer que apagar uma view apaga os dados das tabelas.
Confundir PRIMARY KEY com UNIQUE: a chave primária identifica a linha e não admite nulo; o UNIQUE impede repetição e, na maioria dos sistemas, aceita nulo.
Esquecer que a chave estrangeira também restringe a exclusão do pai.
Achar que índices só trazem vantagens.
Confundir CHECK com chave estrangeira: o CHECK valida o valor por uma condição; a chave estrangeira valida contra outra tabela.

Próximos passos

Veja normalização 1FN, 2FN e 3FN, JOIN com tabelas e resultados e GROUP BY, HAVING e agregações.

Fontes e atualização

Sem norma aplicável. Todos os comandos foram executados em banco SQLite de teste e as saídas citadas são as observadas. Sintaxe e mensagens variam entre sistemas gerenciadores; siga o dialeto do edital e veja a metodologia do site.