Criar e gerenciar restrições de verificação

Uma restrição CHECK permite que você especifique que os valores de uma ou mais colunas precisam satisfazer uma expressão booleana. Neste artigo, descreveremos como gerenciar esse tipo de restrição no banco de dados.

Adicionar uma restrição de verificação a uma nova tabela

No snippet CREATE TABLE a seguir, criamos uma tabela para armazenar informações. sobre shows. Para exigir que o horário de término de um show seja posterior ao horário de início, incluímos uma restrição de verificação.

GoogleSQL 

CREATE TABLE Concerts (
  ConcertId INT64,
  StartTime Timestamp,
  EndTime Timestamp,
  CONSTRAINT start_before_end CHECK(StartTime < EndTime),
) PRIMARY KEY (ConcertId);

PostgreSQL 

CREATE TABLE Concerts (
  ConcertId BIGINT,
  StartTime TIMESTAMPTZ,
  EndTime TIMESTAMPTZ,
  CONSTRAINT start_before_end CHECK(StartTime < EndTime),
  PRIMARY KEY (ConcertId)
);

A definição da restrição começa com a palavra-chave CONSTRAINT. Chamamos explicitamente a restrição como start_before_end neste exemplo, o que facilita a identificação em mensagens de erro e sempre que precisamos consultá-la. Em caso negativo é fornecido, o Spanner fornece um, com o nome gerado começando com o prefixo CK_. Os nomes das restrições têm como escopo o esquema, além dos nomes das tabelas e índices, por exemplo. Eles precisam ser exclusivos dentro do esquema. A definição da restrição de verificação consiste na palavra-chave CHECK seguida de uma expressão entre parênteses. A expressão só pode referenciar colunas desta tabela. Neste exemplo, ele se refere a StartTime e EndTime. A verificação garante que o horário de início de um show seja sempre menor que o horário de término.

O valor da expressão de restrição de verificação é avaliado quando uma nova linha é inserida ou quando StartTime ou EndTime de uma linha existente são atualizados. Se a expressão for avaliada como TRUE ou NULL, a alteração de dados será permitida pela restrição de verificação. Se a expressão for avaliada como FALSE, a alteração dos dados não será permitida.

  • As restrições a seguir se aplicam a um termo expression de restrição de verificação.

    • A expressão só pode referenciar colunas na mesma tabela.

    • A expressão precisa referenciar pelo menos uma coluna não gerada diretamente ou meio de uma coluna gerada que se refere a uma coluna não gerada.

    • A expressão não pode referenciar colunas que tenham a opção allow_commit_timestamp definida.

    • A expressão não pode conter subconsultas.

    • A expressão não pode conter funções não determinísticas, como CURRENT_DATE() e CURRENT_TIMESTAMP().

Adicionar uma restrição de verificação a uma tabela

Usando a instrução ALTER TABLE a seguir, adicionamos uma restrição para garantir que todos os IDs de shows sejam maiores que zero.

ALTER TABLE Concerts
ADD CONSTRAINT concert_id_gt_0 CHECK (ConcertId > 0);

Mais uma vez, fornecemos um nome à restrição, concert_id_gt_0. Adicionar uma restrição CHECK a uma tabela existente inicia a aplicação da restrição imediatamente para novos dados e inicia uma operação de longa duração para validar se os dados existentes obedecem à nova restrição. Como essa validação é executada como uma operação de longa duração, as transações em andamento na tabela não são afetadas. Para mais informações, consulte Desempenho da atualização do esquema. Se houver alguma violação nos dados existentes, a restrição será revertida.

Remover uma restrição de verificação

A instrução DDL a seguir descarta uma restrição CHECK da tabela Concerts.

ALTER TABLE Concerts
DROP CONSTRAINT concert_id_gt_0;

Modificar uma expressão de restrição de verificação

Não é permitido modificar a expressão de uma restrição CHECK. Em vez disso, é necessário descartar a restrição atual e criar uma nova com a nova expressão.

Mostrar propriedades de uma restrição de verificação

O INFORMATION_SCHEMA do Spanner contém informações sobre a verificação. restrições no seu banco de dados. Veja a seguir alguns exemplos das perguntas que é possível responder consultando o esquema de informações.

Quais restrições de verificação são definidas no meu banco de dados?

SELECT tc.CONSTRAINT_NAME, tc.TABLE_NAME, tc.CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
WHERE tc.CONSTRAINT_TYPE = 'CHECK';

Qual é o estado atual das restrições de verificação no meu banco de dados?

Se você adicionou uma restrição de verificação a uma tabela atual, convém visualizar o estado atual dela para determinar, por exemplo, se todos os dados existentes foram validados em relação à restrição. Se SPANNER_STATE retornar VALIDATING_DATA na consulta a seguir significa que o Spanner ainda está no processo de validação dos dados existentes em relação a essa restrição.

SELECT cc.CONSTRAINT_NAME, cc.SPANNER_STATE
FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS as cc;

A seguir