Emulação de restrição MySQL CHECK

Resumo : neste tutorial, você aprenderá como usar CHECKrestrições de emulação no MySQL usando gatilhos ou visualizações.

O MySQL 8.0.16 implementou totalmente a CHECKrestrição SQL. Se você usa MySQL 8.0.16 ou posterior, confira o tutorial CHECKde restrição .

Emulando CHECKrestrições usando gatilhos

Para emular CHECKrestrições no MySQL, você pode usar dois gatilhos : BEFORE INSERTe 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 coste 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 UPDATEacione. 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 INSERTinstrução invoca o BEFORE INSERTgatilho e aceita os valores.

A INSERTinstruçã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 failedLinguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

A INSERTinstruçã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 failedLinguagem de código:  JavaScript  ( javascript )

A INSERTinstruçã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 partsmesa.

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 failedLinguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

A declaração foi rejeitada.

Portanto, usando dois gatilhos: BEFORE INSERTe BEFORE UPDATE, você pode emular CHECKrestrições no MySQL.

Emular CHECKrestrições usando visualizações

A ideia é criar uma visualização WITH CHECK OPTIONbaseada na tabela subjacente. Na SELECTdeclaração da definição da visualização, selecionamos apenas linhas válidas que satisfaçam as CHECKcondiçõ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 partstabela para remover todos os gatilhos associados e crie uma nova tabela como a partstabela, 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 partscom base na parts_datatabela. Fazendo isso, podemos manter partsintacto o código das aplicações que utilizam a tabela. Além disso, todos os privilégios da partstabela 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_datatabela através da partsvisualizaçã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 CHECKrestrições no MySQL.