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

Uma restrição CHECK permite que você especifique se os valores de uma ou mais colunas precisam satisfazer uma expressão booleana. Nesta página, descrevemos como adicionar e gerenciar esse tipo de restrição em bancos de dados com dialeto GoogleSQL e PostgreSQL.

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 start_before_end neste exemplo para ajudar você a encontrá-la em mensagens de erro e sempre que precisarmos consultar. Se nenhum nome for fornecido, o Spanner vai fornecer um, com o nome gerado que começa 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 restrição de 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 o StartTime ou o 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.

Conferir as propriedades de uma restrição de verificação

O INFORMATION_SCHEMA do Spanner contém informações sobre as restrições de verificação do 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, isso significa que o Spanner ainda está em processo de validação de dados em relação a essa restrição.

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

A seguir