Compreendendo LOCAL e CASCADED na cláusula WITH CHECK OPTION

Resumo : neste tutorial, você aprenderá as diferenças entre LOCALe CASCADEDna WITH CHECK OPTIONcláusula.

Observe que você deve estar familiarizado com a WITH CHECK OPTIONcláusula antes de prosseguir com este tutorial. Se não for esse o caso, você pode seguir o WITH CHECK OPTIONtutorial.

Introdução ao escopo da verificação `LOCAL`e`CASCADED`

Quando você cria uma visualização com a WITH CHECK OPTIONcláusula e emite uma instrução DML contra a visualização, como INSERT, UPDATE, e DELETE, o MySQL verifica se as linhas que estão sendo alteradas estão em conformidade com a definição da visualização.

Como uma visualização pode ser criada com base em outras visualizações, o MySQL também verifica as regras nas visualizações dependentes quanto à consistência dos dados.

Para determinar o escopo da verificação, o MySQL oferece duas opções: LOCALe CASCADED. Se você não especificar a palavra-chave explicitamente na WITH CHECK OPTIONcláusula, o MySQL usará CASCADEDpor padrão.

MySQL`WITH CASCADED CHECK OPTION`

Considere o exemplo a seguir para entender o efeito do WITH CASCADED CHECK OPTION:

Primeiro, crie uma tabela nomeada t1com uma coluna ccujo tipo de dados seja um número inteiro.

CREATE TABLE t1 (
    c INT
);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

A seguir, crie uma visualização v1 baseada na t1tabela com os dados da ccoluna maiores que 10.

CREATE OR REPLACE VIEW v1 
AS
    SELECT 
        c
    FROM
        t1
    WHERE
        c > 10;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Como não especificamos o WITH CHECK OPTION, a instrução a seguir funciona mesmo que não esteja em conformidade com a definição da v1visualização.

INSERT INTO v1(c) 
VALUES (5);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Em seguida, crie uma visualização v2com base na cláusula v1view with WITH CASCADED CHECK OPTION.

CREATE OR REPLACE VIEW v2 
AS
    SELECT c FROM v1 
WITH CASCADED CHECK OPTION;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Agora, insira uma linha com valor 5 na t1tabela através da v2visualização.

INSERT INTO v2(c) 
VALUES (5);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

MySQL emitiu a seguinte mensagem de erro:

Error Code: 1369. CHECK OPTION failed 'classicmodels.v2'Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Ele falha na nova linha que não está em conformidade com a definição de v2visualização.

Depois disso, crie uma nova visualização chamada v3com base em v2.

CREATE OR REPLACE VIEW v3 
AS
    SELECT c
    FROM v2
    WHERE c < 20;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Insira uma nova linha na t1tabela através da v3visualização com valor 8.

INSERT INTO v3(c) 
VALUES (8);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

MySQL emitiu a seguinte mensagem de erro:

Error Code: 1369. CHECK OPTION failed 'classicmodels.v3'Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

A instrução insert falha mesmo que a linha pareça estar em conformidade com a definição da v3visualização.

Porque a visualização v3depende da v2visualização e a v2visualização tem a opção WITH CASCADED CHECK OPTION.

No entanto, a seguinte instrução insert funciona.

INSERT INTO v3(c) VALUES (30);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Porque a visualização v3 não possui um WITH CHECK OPTIONe a instrução está em conformidade com a definição da visualização v2.

Concluindo, quando uma visualização usa um WITH CASCADED CHECK OPTION, o MySQL verifica as regras da visualização e também as regras das visualizações subjacentes recursivamente.

MySQL COM OPÇÃO DE VERIFICAÇÃO EM CASCATA

MySQL`WITH LOCAL CHECK OPTION`

Vamos usar o mesmo exemplo acima para WITH LOCAL CHECK OPTIONver as diferenças.

Primeiro, altere a v2visualização para usar o WITH LOCAL CHECK OPTION.

ALTER VIEW v2 AS
    SELECT 
        c
    FROM
        v1 
WITH LOCAL CHECK OPTION;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Segundo, insira a mesma linha que fizemos no exemplo acima.

INSERT INTO v2(c) 
VALUES (5);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Foi bem sucedido.

Porque v2a visualização não tem regras. A visão v2depende da v1visão. No entanto, v1a visualização não especifica uma opção de verificação, portanto, o MySQL ignora a verificação das regras na v1visualização.

Observe que esta instrução falhou na visualização v2 criada com um arquivo WITH CASCADED CHECK OPTION.

Terceiro, insira a mesma linha na tabela t1 por meio da visualização v3.

INSERT INTO v3(c) VALUES (8);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

É bem-sucedido neste caso porque o MySQL não verificou as regras da visualização v1 devido à WITH LOCAL CHECK OPTIONvisualização v2.

Além disso, observe que esta instrução falhou no exemplo que a v2 criou com um arquivo WITH CASCADED CHECK OPTION.

Portanto, se uma visualização usa a WITH LOCAL CHECK OPTION, o MySQL verifica as regras das visualizações que possuem a WITH LOCAL CHECK OPTIONe a WITH CASCADED CHECK OPTION.

É diferente da visualização que usa um WITH CASCADED CHECK OPTIONMySQL que verifica as regras de todas as visualizações dependentes.

Observe que antes do MySQL 5.7.6, se você usar uma visualização com a WITH LOCAL CHECK OPTION, o MySQL apenas verifica as regras da visualização atual e não verifica as regras das visualizações subjacentes.