Criação e alteração de dados em SQL

CREATE TABLE Produto define onde guardar cada produto e que valores aceitar. INSERT INTO Produto acrescenta um produto a essa estrutura.

A linguagem de definição de dados, DDL, cria e altera o esquema. A linguagem de manipulação, DML, consulta ou muda os tuplos.

CREATE TABLE e restrições

Uma tabela precisa de colunas, tipos e regras. Vamos começar por Produto:

CREATE TABLE Produto (
  id INTEGER PRIMARY KEY,
  nome TEXT NOT NULL,
  precoCentimos INTEGER NOT NULL CHECK (precoCentimos >= 0),
  stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0)
);

PRIMARY KEY escolhe a identidade. NOT NULL exige um valor. CHECK rejeita uma linha quando a sua expressão é falsa. DEFAULT fornece um valor quando a inserção omite a coluna, mas não substitui um NULL escrito explicitamente.

O preço guarda-se em cêntimos inteiros para evitar arredondamentos de ponto flutuante nas contas monetárias. Assim, 4500 representa 45 euros. Para mostrar euros, divide por 100.0.

Dividir dois inteiros em SQLite faz divisão inteira. 5 / 2 dá 2, enquanto 5 / 2.0 dá 2,5.

Um CHECK também pode relacionar colunas da mesma linha. Podemos dar-lhe um nome com CONSTRAINT, para identificar a regra:

CREATE TABLE Intervalo (
  inicio INTEGER NOT NULL,
  fim INTEGER NOT NULL,
  CONSTRAINT intervalo_valido CHECK (inicio <= fim)
);

O par (3,5) é aceite e (5,3) é recusado, tanto numa inserção como numa atualização. A regra compara início e fim do mesmo intervalo; não compara linhas diferentes nem assegura que dois intervalos não se sobrepõem.

Uma restrição UNIQUE(email) proíbe emails conhecidos repetidos. Em SQLite permite vários NULL. Se email tiver de ser uma chave candidata, acrescenta NOT NULL.

A teoria exige que a primária nunca seja nula. Em SQLite há uma particularidade histórica: primárias que não sejam INTEGER PRIMARY KEY podem admitir NULL em tabelas comuns. Declara NOT NULL nas colunas de chaves textuais e compostas, ou usa uma tabela STRICT ou WITHOUT ROWID quando apropriado. A declaração exata INTEGER PRIMARY KEY corresponde ao rowid numa tabela comum; INT PRIMARY KEY não tem o mesmo comportamento.

Na atribuição automática habitual de INTEGER PRIMARY KEY, o motor escolhe um valor superior ao maior rowid presente, começando em 1 numa tabela vazia. Se apagarmos o maior id positivo e inserirmos outra linha, o valor apagado pode ser reutilizado. No limite do maior inteiro de 64 bits, o algoritmo sem AUTOINCREMENT procura outro rowid livre, em vez de calcular um inteiro maior. O modificador AUTOINCREMENT, apenas nessa declaração exata, proíbe a reutilização e garante ids monótonos, à custa de uma tabela interna sqlite_sequence. Só o usa quando precisares dessa garantia, por exemplo para nunca confundir um registo novo com um registo apagado.

Tipos e valores desconhecidos

SQLite associa tipos aos valores e usa a declaração da coluna como afinidade, uma preferência de conversão. Uma coluna INTEGER numa tabela comum pode guardar texto que não se consegue converter.

Não trata VARCHAR(20) como uma imposição de comprimento. Usa uma restrição explícita se o máximo de vinte caracteres for uma regra.

Tabelas STRICT, disponíveis a partir de SQLite 3.37, impõem tipos de forma mais estrita, com conversões sem perda quando possíveis. Não tornam todas as regras do domínio automáticas. Uma idade inteira ainda pode ser negativa sem CHECK.

NULL representa um valor desconhecido ou não aplicável. CHECK (preco > 0) sozinho não proíbe NULL: a expressão fica desconhecida e SQLite não a trata como falsa. Combina o CHECK com NOT NULL quando o valor é obrigatório.

CREATE TABLE Exemplo(valor INTEGER CHECK (valor > 0));
INSERT INTO Exemplo VALUES (NULL), (7), ('texto');
SELECT COALESCE(CAST(valor AS TEXT), 'NULL') AS valor,
     typeof(valor) AS tipo
FROM Exemplo ORDER BY rowid;
SELECT 5 / 2 AS divisaoInteira, 5 / 2.0 AS divisaoReal;

O texto passa o CHECK neste exemplo porque a comparação entre classes de armazenamento não é uma verificação de “número positivo”. Para exigir valores inteiros positivos numa tabela comum, acrescenta CHECK (typeof(valor) = 'integer' AND valor > 0) e NOT NULL, ou escolhe STRICT.

Integridade referencial

Uma chave estrangeira afirma que o valor conhecido existe na chave referenciada. Em SQLite, ativa a verificação em cada ligação, antes da transação:

PRAGMA foreign_keys = ON;
CREATE TABLE Encomenda (
  id INTEGER PRIMARY KEY,
  data TEXT NOT NULL,
  idCliente INTEGER NOT NULL REFERENCES Cliente(id)
);

A tabela Cliente tem de existir quando usamos a referência. O alvo pode ser uma primária ou uma chave com unicidade compatível. Uma estrangeira não é automaticamente única: várias encomendas podem apontar para Ana.

Para uma referência composta, declara-a em conjunto:

FOREIGN KEY (edificio, numero) REFERENCES Sala(edificio, numero)

Duas referências separadas não asseguram que o edifício e o número pertencem à mesma sala. Em SQLite, se qualquer coluna da estrangeira composta for NULL, essa linha não precisa de corresponder a um tuplo alvo. Se a referência for obrigatória, declara todas as suas colunas NOT NULL.

Atualizar ou apagar o alvo

A política de referência define o que acontece às linhas dependentes:

As referências são imediatas por omissão. Uma referência DEFERRABLE INITIALLY DEFERRED pode ficar temporariamente inválida dentro da transação, mas tem de ser válida no COMMIT. Isto não é o mesmo que desligar foreign_keys.

Na loja, apagar uma encomenda elimina as linhas Item por cascata. Apagar um produto vendido é recusado. As duas escolhas correspondem a objetos com ciclos de vida diferentes.

Criar e povoar a loja

O bloco seguinte contém o esquema e todos os dados. Item declara a chave composta e guarda o preço da compra. Consulta os totais em cêntimos para conferir o povoamento.

PRAGMA foreign_keys = ON;
CREATE TABLE Cliente (
  id INTEGER PRIMARY KEY,
  nome TEXT NOT NULL,
  email TEXT UNIQUE
);
CREATE TABLE Produto (
  id INTEGER PRIMARY KEY,
  nome TEXT NOT NULL,
  precoCentimos INTEGER NOT NULL CHECK (precoCentimos >= 0),
  stock INTEGER NOT NULL CHECK (stock >= 0)
);
CREATE TABLE Encomenda (
  id INTEGER PRIMARY KEY,
  data TEXT NOT NULL,
  idCliente INTEGER NOT NULL REFERENCES Cliente(id)
);
CREATE TABLE Item (
  idEncomenda INTEGER NOT NULL REFERENCES Encomenda(id) ON DELETE CASCADE,
  idProduto INTEGER NOT NULL REFERENCES Produto(id),
  qtd INTEGER NOT NULL CHECK (qtd > 0),
  precoUnitario INTEGER NOT NULL CHECK (precoUnitario >= 0),
  PRIMARY KEY (idEncomenda, idProduto)
);
INSERT INTO Cliente VALUES
  (1, 'Ana', 'ana@example.test'),
  (2, 'Rui', NULL),
  (3, 'Mia', NULL);
INSERT INTO Produto VALUES
  (10, 'Teclado', 4500, 20),
  (11, 'Rato', 2500, 50),
  (12, 'Monitor', 18000, 5);
INSERT INTO Encomenda VALUES
  (100, '2026-01-05', 1),
  (101, '2026-01-06', 1),
  (102, '2026-01-06', 2);
INSERT INTO Item VALUES
  (100, 10, 2, 4500),
  (100, 11, 1, 2500),
  (101, 12, 1, 18000),
  (102, 11, 3, 2500);

SELECT idEncomenda, SUM(qtd * precoUnitario) AS totalCentimos
FROM Item GROUP BY idEncomenda ORDER BY idEncomenda;

A estrangeira não obriga uma encomenda a ter Item, como explica o mapeamento, porque CHECK não aceita uma subconsulta que conte linhas noutra tabela em SQLite.

Três requisitos, três restrições

Queremos uma tabela de reservas onde cada reserva indica um cliente existente, tem data e tem um número positivo de lugares:

CREATE TABLE Reserva(
  id INTEGER PRIMARY KEY,
  idCliente INTEGER NOT NULL REFERENCES Cliente(id),
  data TEXT NOT NULL,
  lugares INTEGER NOT NULL CHECK (lugares > 0)
);

Quatro inserções, três recusas:

Cada restrição rejeitou uma inserção diferente e a tabela fica só com a reserva 1. A estrangeira exige ainda PRAGMA foreign_keys = ON em cada ligação, como na secção de integridade referencial.

INSERT, UPDATE e DELETE

Especifica as colunas na inserção, sobretudo quando omites valores:

INSERT INTO Produto(id, nome, precoCentimos) VALUES (13, 'Cabo', 900);

Este excerto pressupõe a primeira versão de Produto, com stock DEFAULT 0. Se a tabela não tiver esse valor por omissão, a mesma inserção viola NOT NULL. A definição do esquema faz parte das condições de um exemplo.

INSERT ... SELECT insere o resultado de uma consulta. UPDATE altera as linhas que satisfazem WHERE; DELETE remove-as. Sem WHERE, ambos abrangem todas as linhas. As restrições também se aplicam às atualizações.

UPDATE Produto SET precoCentimos = precoCentimos + 500 WHERE id = 10;
DELETE FROM Encomenda WHERE id = 100;
SELECT id, precoCentimos FROM Produto WHERE id = 10;
SELECT idEncomenda, idProduto, qtd FROM Item
ORDER BY idEncomenda, idProduto;

PRAGMA foreign_keys = ON;
CREATE TABLE Cliente (
  id INTEGER PRIMARY KEY,
  nome TEXT NOT NULL,
  email TEXT UNIQUE
);
CREATE TABLE Produto (
  id INTEGER PRIMARY KEY,
  nome TEXT NOT NULL,
  precoCentimos INTEGER NOT NULL CHECK (precoCentimos >= 0),
  stock INTEGER NOT NULL CHECK (stock >= 0)
);
CREATE TABLE Encomenda (
  id INTEGER PRIMARY KEY,
  data TEXT NOT NULL,
  idCliente INTEGER NOT NULL REFERENCES Cliente(id)
);
CREATE TABLE Item (
  idEncomenda INTEGER NOT NULL REFERENCES Encomenda(id) ON DELETE CASCADE,
  idProduto INTEGER NOT NULL REFERENCES Produto(id),
  qtd INTEGER NOT NULL CHECK (qtd > 0),
  precoUnitario INTEGER NOT NULL CHECK (precoUnitario >= 0),
  PRIMARY KEY (idEncomenda, idProduto)
);
INSERT INTO Cliente VALUES
  (1, 'Ana', 'ana@example.test'),
  (2, 'Rui', NULL),
  (3, 'Mia', NULL);
INSERT INTO Produto VALUES
  (10, 'Teclado', 4500, 20),
  (11, 'Rato', 2500, 50),
  (12, 'Monitor', 18000, 5);
INSERT INTO Encomenda VALUES
  (100, '2026-01-05', 1),
  (101, '2026-01-06', 1),
  (102, '2026-01-06', 2);
INSERT INTO Item VALUES
  (100, 10, 2, 4500),
  (100, 11, 1, 2500),
  (101, 12, 1, 18000),
  (102, 11, 3, 2500);

Uma alteração do preço atual não modifica Item.precoUnitario nas vendas conservadas, porque são factos diferentes.

Não uses INSERT OR REPLACE como sinónimo universal de atualizar. Em SQLite, a resolução REPLACE pode eliminar a linha que entra em conflito e inserir outra, ativando efeitos referenciais de eliminação. Para uma atualização, escreve a operação pretendida ou usa uma cláusula ON CONFLICT ... DO UPDATE compatível com a chave e a versão do motor.

ALTER e DROP

ALTER TABLE muda o esquema. SQLite suporta, entre outras operações, renomear uma tabela, renomear uma coluna e acrescentar uma coluna. As alterações permitidas dependem da versão e têm limites para restrições e dependências. Uma migração que não caiba nessas operações pode exigir criar uma tabela nova, copiar dados e substituir a antiga numa transação.

DROP TABLE remove a estrutura e os dados; DELETE FROM remove linhas, conservando o esquema. CREATE TABLE ... AS SELECT cria uma tabela com resultados, mas em SQLite não copia primárias, estrangeiras nem CHECKs da origem. Uma cópia de dados não é uma cópia de todas as regras.