Resumo : neste tutorial, você aprenderá como usar CHECK
a restrição MySQL para garantir que os valores armazenados em uma coluna ou grupo de colunas satisfaçam uma expressão booleana.
MySQL 8.0.16 implementou a restrição de verificação SQL. Se você usa MySQL com versões anteriores, você pode emular uma CHECK
restrição usando uma visualização WITH CHECK OPTION
ou um gatilho .
Introdução à restrição MySQL CHECK
Antes do MySQL 8.0.16, CREATE TABLE
permite incluir uma CHECK
restrição de tabela. No entanto, o MySQL ignora todas as CHECK
restrições:
CHECK(expression)
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
A partir do MySQL 8.0.16, os CREATE TABLE
recursos essenciais suportados de CHECK
restrições de tabela e coluna para todos os mecanismos de armazenamento .
Aqui está a sintaxe básica:
CONSTRAINT constraint_name
CHECK (expression)
[ENFORCED | NOT ENFORCED]
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Nesta sintaxe:
Primeiro, especifique o nome da restrição de verificação que deseja criar após a CONSTRAINT
palavra-chave. Se você omitir o nome da restrição, o MySQL gera automaticamente um nome com a seguinte convenção:
table_name_chk_n
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Nesta convenção, n
é um número ordinal como 1,2 e 3. Por exemplo, os nomes de CHECK
restrições da parts
tabela gerados automaticamente serão parts_chk_1
, parts_chk_2
e assim por diante.
Segundo, especifique um booleano expression
que deve ser avaliado para TRUE
ou UNKNOWN
para cada linha da tabela entre parênteses após a CHECK
palavra-chave.
Se a expressão for avaliada como FALSE
, os valores violam a restrição ou ocorre uma violação da restrição.
Observe que o MySQL trata 1 como verdadeiro e 0 como falso.
Terceiro, especifique opcionalmente a cláusula de execução para indicar se a restrição de verificação é aplicada:
- Use
ENFORCED
ou omita aENFORCED
cláusula para criar e impor a restrição. - Use
NOT ENFORCED
para criar a restrição, mas não aplicá-la.
Conforme mencionado anteriormente, você pode definir uma CHECK
restrição como uma restrição de tabela ou uma restrição de coluna.
Uma CHECK
restrição de tabela pode fazer referência a várias colunas, enquanto a CHECK
restrição de coluna pode fazer referência à única coluna onde está definida.
Exemplos de restrições CHECK do MySQL
Vejamos alguns exemplos de uso das CHECK
restrições.
1) Criando restrições CHECK como restrições de coluna
A seguinte instrução CREATE TABLE cria uma nova tabela chamada parts
:
CREATE TABLE parts (
part_no VARCHAR(18) PRIMARY KEY,
description VARCHAR(40),
cost DECIMAL(10,2 ) NOT NULL CHECK (cost >= 0),
price DECIMAL(10,2) NOT NULL CHECK (price >= 0)
);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
A tabela de peças possui duas CHECK
restrições de coluna: uma para a cost
coluna e outra para a price
coluna.
Como não especificamos explicitamente os nomes das CHECK
restrições, o MySQL gerou nomes automaticamente para elas.
Para visualizar a definição da tabela com o CHECK
nome da restrição, use a SHOW CREATE TABLE
instrução:
SHOW CREATE TABLE parts;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Saída:
A saída indica que o MySQL gerou os nomes ( parts_chk_1
e parts_chk_2
) para as restrições de verificação.
Após criar CHECK
restrições, se você inserir ou atualizar um valor que faça com que a expressão booleana seja falsa, o MySQL rejeitará a alteração e emitirá um erro.
Esta instrução insere uma nova linha na parts
tabela:
INSERT INTO parts(part_no, description,cost,price)
VALUES('A-001','Cooler',0,-100);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
MySQL emitiu um erro:
Error Code: 3819. Check constraint 'parts_chk_2' is violated.
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Como o valor da price
coluna é negativo, o que faz com que a expressão price > 0
seja avaliada como FALSE
resultado de uma violação de restrição.
2) Criando restrições CHECK como restrições de tabela
Primeiro, elimine a parts
tabela:
DROP TABLE IF EXISTS parts;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Em seguida, crie uma nova parts
tabela com mais uma CHECK
restrição de tabela:
CREATE TABLE parts (
part_no VARCHAR(18) PRIMARY KEY,
description VARCHAR(40),
cost DECIMAL(10,2 ) NOT NULL CHECK (cost >= 0),
price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
CONSTRAINT parts_chk_price_gt_cost
CHECK(price >= cost)
);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
A nova cláusula a seguir define uma CHECK
restrição de tabela que garante que o preço seja sempre maior ou igual ao custo:
CONSTRAINT parts_chk_price_gt_cost CHECK(price >= cost)
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Como especificamos explicitamente o nome da CHECK
restrição, o MySQL cria a nova restrição com o nome especificado.
Aqui está a definição da parts
tabela:
SHOW CREATE TABLE parts;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
A CHECK
restrição da tabela aparece no final da definição da tabela, após a lista de colunas.
Esta afirmação tenta inserir uma nova peça cujo preço é menor que o custo:
INSERT INTO parts(part_no, description,cost,price)
VALUES('A-001','Cooler',200,100);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Aqui está o erro devido à violação da restrição:
Error Code: 3819. Check constraint 'parts_chk_price_gt_cost' is violated.
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Adicionando uma restrição de verificação a uma tabela
Para adicionar uma restrição de verificação a uma tabela existente, você usa a ALTER TABLE ... ADD CHECK
instrução:
ALTER TABLE table_name
ADD CHECK (expression);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Se quiser especificar explicitamente o nome da CHECK
restrição, você pode usar a ALTER TABLE ... ADD CONSTRAINT ... CHECK
instrução:
ALTER TABLE table_name
ADD CONSTRAINT contraint_name
CHECK (expression);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Por exemplo, a instrução a seguir adiciona uma CHECK
restrição à parts
tabela:
ALTER TABLE parts
ADD CHECK (part_no <> description);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Essa CHECK
restrição impede que você tenha o part_no
mesmo arquivo description
.
Por exemplo, a seguinte INSERT
declaração será rejeitada:
INSERT INTO parts
VALUES('A','A',100,120);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Saída:
ERROR 3819 (HY000): Check constraint 'parts_chk_3' is violated.
Linguagem de código: JavaScript ( javascript )
Removendo uma restrição de verificação de uma tabela
Para remover uma CHECK
restrição de uma tabela, você usa a ALTER TABLE ... DROP CHECK
instrução:
ALTER TABLE table_name
DROP CHECK constraint_name;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Por exemplo, a instrução a seguir remove a CHECK
restrição parts_chk_3
da tabela de peças:
ALTER TABLE parts
DROP CHECK parts_chk_3;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Resumo
- Use
CHECK
restrições para garantir que os valores armazenados em uma coluna satisfaçam uma condição booleana. - Use
CHECK(expression)
para definir umaCHECK
restrição. - Use
ALTER TABLE ... ADD CHECK
para adicionar umaCHECK
restrição a uma tabela. - Use
ALTER TABLE ... DROP CHECK
para remover umaCHECK
restrição de uma tabela.