Resumo : neste tutorial, você aprenderá como usar CHECK
restrições de emulação no MySQL usando gatilhos ou visualizações.
O MySQL 8.0.16 implementou totalmente a CHECK
restrição SQL. Se você usa MySQL 8.0.16 ou posterior, confira o tutorial CHECK
de restrição .
Emulando CHECK
restrições usando gatilhos
Para emular CHECK
restrições no MySQL, você pode usar dois gatilhos : BEFORE INSERT
e BEFORE UPDATE
.
Primeiro, crie uma nova tabela com o nome parts
da demonstração:
CREATE TABLE IF NOT EXISTS parts (
part_no VARCHAR(18) PRIMARY KEY,
description VARCHAR(40),
cost DECIMAL(10 , 2 ) NOT NULL,
price DECIMAL(10,2) NOT NULL
);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Em seguida, crie um procedimento armazenado para verificar os valores nas colunas cost
e price
.
DELIMITER $
CREATE PROCEDURE `check_parts`(IN cost DECIMAL(10,2), IN price DECIMAL(10,2))
BEGIN
IF cost < 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'check constraint on parts.cost failed';
END IF;
IF price < 0 THEN
SIGNAL SQLSTATE '45001'
SET MESSAGE_TEXT = 'check constraint on parts.price failed';
END IF;
IF price < cost THEN
SIGNAL SQLSTATE '45002'
SET MESSAGE_TEXT = 'check constraint on parts.price & parts.cost failed';
END IF;
END$
DELIMITER ;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Em seguida, crie BEFORE INSERT
e BEFORE UPDATE
acione. Dentro dos gatilhos, chame o check_parts()
procedimento armazenado.
-- before insert
DELIMITER $
CREATE TRIGGER `parts_before_insert` BEFORE INSERT ON `parts`
FOR EACH ROW
BEGIN
CALL check_parts(new.cost,new.price);
END$
DELIMITER ;
-- before update
DELIMITER $
CREATE TRIGGER `parts_before_update` BEFORE UPDATE ON `parts`
FOR EACH ROW
BEGIN
CALL check_parts(new.cost,new.price);
END$
DELIMITER ;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Depois disso, insira uma nova linha que satisfaça todas as seguintes condições:
- custo > 0
- E preço > 0
- E preço >= custo
INSERT INTO parts(part_no, description,cost,price)
VALUES('A-001','Cooler',100,120);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
1 row(s) affected
A INSERT
instrução invoca o BEFORE INSERT
gatilho e aceita os valores.
A INSERT
instrução a seguir falha porque viola a condição: custo > 0.
INSERT INTO parts(part_no, description,cost,price)
VALUES('A-002','Heater',-100,120);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Error Code: 1644. check constraint on parts.cost failed
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
A INSERT
instrução a seguir falha porque viola a condição: preço > 0.
INSERT INTO parts(part_no, description,cost,price)
VALUES('A-002','Heater',100,-120);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Error Code: 1644. check constraint on parts.price failed
Linguagem de código: JavaScript ( javascript )
A INSERT
instrução a seguir falha porque viola a condição: preço > custo.
INSERT INTO parts(part_no, description,cost,price)
VALUES('A-003','wiper',120,100);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Vamos ver o que temos agora na parts
mesa.
SELECT * FROM parts;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
A declaração a seguir tenta atualizar o custo para torná-lo inferior ao preço:
UPDATE parts
SET price = 10
WHERE part_no = 'A-001';
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Error Code: 1644. check constraint on parts.price & parts.cost failed
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
A declaração foi rejeitada.
Portanto, usando dois gatilhos: BEFORE INSERT
e BEFORE UPDATE
, você pode emular CHECK
restrições no MySQL.
Emular CHECK
restrições usando visualizações
A ideia é criar uma visualização WITH CHECK OPTION
baseada na tabela subjacente. Na SELECT
declaração da definição da visualização, selecionamos apenas linhas válidas que satisfaçam as CHECK
condições. Qualquer inserção ou atualização na visualização será rejeitada se fizer com que a nova linha não apareça na visualização.
Primeiro, elimine a parts
tabela para remover todos os gatilhos associados e crie uma nova tabela como a parts
tabela, mas com um nome diferente parts_data
:
DROP TABLE IF EXISTS parts;
CREATE TABLE IF NOT EXISTS parts_data (
part_no VARCHAR(18) PRIMARY KEY,
description VARCHAR(40),
cost DECIMAL(10,2) NOT NULL,
price DECIMAL(10,2) NOT NULL
);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
A seguir, crie uma visualização nomeada parts
com base na parts_data
tabela. Fazendo isso, podemos manter parts
intacto o código das aplicações que utilizam a tabela. Além disso, todos os privilégios da parts
tabela antiga permanecem inalterados.
CREATE VIEW parts AS
SELECT
part_no, description, cost, price
FROM
parts_data
WHERE
cost > 0 AND price > 0 AND price >= cost
WITH CHECK OPTION;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Em seguida, insira uma nova linha na parts_data
tabela através da parts
visualização:
INSERT INTO parts(part_no, description,cost,price)
VALUES('A-001','Cooler',100,120);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
É aceito porque é válida a nova linha que aparece na visualização.
Depois disso, tente inserir uma nova linha que não apareceria na visualização.
INSERT INTO parts_checked(part_no, description,cost,price)
VALUES('A-002','Heater',-100,120);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Error Code: 1369. CHECK OPTION failed 'classicmodels.parts_checked'
Linguagem de código: JavaScript ( javascript )
Neste tutorial, você aprendeu como usar gatilhos ou visualizações para emular CHECK
restrições no MySQL.