Resumo : neste tutorial, você aprenderá como usar CHECK
a restrição SQLite para validar dados antes de inserir ou atualizar.
CHECK
Introdução às restrições SQLite
As restrições SQLite CHECK
permitem definir expressões para testar valores sempre que eles são inseridos ou atualizados em uma coluna.
Se os valores não atenderem aos critérios definidos pela expressão, o SQLite emitirá uma violação de restrição e abortará a instrução.
As CHECK
restrições permitem definir verificações adicionais de integridade de dados além UNIQUE
ou NOT NULL
para se adequar à sua aplicação específica.
SQLite permite definir uma CHECK
restrição no nível da coluna ou da tabela.
A instrução a seguir mostra como definir uma CHECK
restrição no nível da coluna:
CREATE TABLE table_name(
...,
column_name data_type CHECK(expression),
...
);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
e a instrução a seguir ilustra como definir uma CHECK
restrição no nível da tabela:
CREATE TABLE table_name(
...,
CHECK(expression)
);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Nesta sintaxe, sempre que uma linha é inserida em uma tabela ou uma linha existente é atualizada, a expressão associada a cada CHECK
restrição é avaliada e retorna um valor numérico 0 ou 1.
Se o resultado for zero, ocorreu uma violação da restrição. Se o resultado for um valor diferente de zero ou NULL, significa que não ocorreu nenhuma violação de restrição.
Observe que a expressão de uma CHECK
restrição não pode conter uma subconsulta .
CHECK
Exemplos de restrições SQLite
Vejamos alguns exemplos de uso das CHECK
restrições.
1) CHECK
Exemplo de uso de restrição SQLite no nível da coluna
A instrução a seguir cria uma nova tabela chamada contacts
:
CREATE TABLE contacts (
contact_id INTEGER PRIMARY KEY,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,
email TEXT,
phone TEXT NOT NULL
CHECK (length(phone) >= 10)
);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Na contacts
tabela, a phone
coluna possui uma CHECK
restrição:
CHECK (length(phone) >= 10)
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Essa CHECK
restrição garante que os valores na phone
coluna tenham pelo menos 10 caracteres.
Se você tentar executar a instrução a seguir, receberá um erro de violação de restrição:
INSERT INTO contacts(first_name, last_name, phone)
VALUES('John','Doe','408123456');
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Aqui está a mensagem de erro:
Result: CHECK constraint failed: contacts
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
O motivo foi que o número de telefone que você tentou inserir tem apenas 9 caracteres, embora exija pelo menos 10 caracteres.
A instrução a seguir deve funcionar porque o valor na phone
coluna possui 13 caracteres, o que satisfaz a expressão na CHECK
restrição:
INSERT INTO contacts(first_name, last_name, phone)
VALUES('John','Doe','(408)-123-456');
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
2) CHECK
Exemplo de uso de restrições SQLite no nível da tabela
A instrução a seguir cria uma nova tabela chamada products
:
CREATE TABLE products (
product_id INTEGER PRIMARY KEY,
product_name TEXT NOT NULL,
list_price DECIMAL (10, 2) NOT NULL,
discount DECIMAL (10, 2) NOT NULL
DEFAULT 0,
CHECK (list_price >= discount AND
discount >= 0 AND
list_price >= 0)
);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Neste exemplo, a CHECK
restrição é definida no nível da tabela:
CHECK (list_price >= discount AND
discount >= 0 AND
list_price >= 0)
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
A CHECK
restrição garante que o preço de tabela seja sempre maior ou igual ao desconto e que tanto o desconto quanto o preço de tabela sejam maiores ou iguais a zero.
A afirmação a seguir viola a CHECK
restrição porque o desconto é maior que o preço de tabela.
INSERT INTO products(product_name, list_price, discount)
VALUES('New Product',900,1000);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
A afirmação a seguir também viola a CHECK
restrição porque o desconto é negativo:
INSERT INTO products(product_name, list_price, discount)
VALUES('New XFactor',1000,-10);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Adicionando CHECK
restrições a uma tabela existente
A partir da versão 3.25.2, o SQLite não suporta a adição de uma CHECK
restrição a uma tabela existente.
No entanto, você pode seguir estas etapas:
Primeiro, crie uma nova tabela cuja estrutura seja a mesma da tabela à qual você deseja adicionar uma CHECK
restrição. A nova tabela também deve incluir a CHECK
restrição:
CREATE TABLE new_table (
[...],
CHECK ([...])
);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Para obter a estrutura da tabela antiga, você pode usar o .schema
comando. Confira o tutorial da tabela SQLite DESCRIBE para obter mais informações.
Segundo, copie os dados da tabela antiga para a nova tabela.
INSERT INTO new_table SELECT * FROM old_table;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Terceiro, elimine a tabela antiga:
DROP TABLE old_table;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Quarto, renomeie a nova tabela para a antiga:
ALTER TABLE new_table RENAME TO old_table;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Para tornar todas as declarações acima seguras para transações, você deve executá-las em uma transação como esta:
BEGIN;
-- create a new table
CREATE TABLE new_table (
[...],
CHECK ([...])
);
-- copy data from old table to the new one
INSERT INTO new_table SELECT * FROM old_table;
-- drop the old table
DROP TABLE old_table;
-- rename new table to the old one
ALTER TABLE new_table RENAME TO old_table;
-- commit changes
COMMIT;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Neste tutorial, você aprendeu como usar a CHECK
restrição SQLite para garantir que os valores em uma coluna ou grupo de colunas satisfaçam uma condição definida por uma expressão.