Crie e faça a gestão de restrições de verificação

Uma restrição CHECK permite especificar que os valores de uma ou mais colunas têm de satisfazer uma expressão booleana. Nesta página, descrevemos como adicionar e gerir este tipo de restrição em bases de dados com dialeto GoogleSQL e bases de dados com dialeto PostgreSQL.

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

No seguinte fragmento do CREATE TABLE, criamos uma tabela para armazenar informações sobre concertos. Para exigir que a hora de fim de um concerto seja posterior à hora 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. Nomeámos explicitamente a restrição start_before_end neste exemplo para ajudar a encontrá-la nas mensagens de erro e sempre que precisarmos de fazer referência à mesma. Se não for indicado nenhum nome, o Spanner fornece um, com o nome gerado a começar pelo prefixo CK_. Os nomes das restrições estão ao nível do esquema, juntamente com os nomes das tabelas e dos índices, e têm de ser exclusivos no esquema. A definição da restrição CHECK consiste na palavra-chave CHECK seguida de uma expressão entre parênteses. A expressão só pode referenciar colunas desta tabela. Neste exemplo, faz referência a StartTime e EndTime, e a restrição de verificação garante que a hora de início de um concerto é sempre inferior à hora de fim.

O valor da expressão de restrição de verificação é avaliado quando é inserida uma nova linha ou quando os campos 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 é permitida pela restrição de verificação. Se a expressão for avaliada como FALSE, a alteração de dados não é permitida.

  • Aplicam-se as seguintes restrições a um termo de restrição de verificação expression.

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

    • A expressão tem de fazer referência a, pelo menos, uma coluna não gerada, seja diretamente ou através de uma coluna gerada que faça referência a uma coluna não gerada.

    • A expressão não pode fazer referência a colunas que tenham definido a opção allow_commit_timestamp.

    • 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().

Adicione uma restrição de verificação a uma tabela existente

Usando a seguinte declaração ALTER TABLE, adicionamos uma restrição para garantir que todos os IDs de concertos são superiores a zero.

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

Mais uma vez, atribuímos um nome à restrição, concert_id_gt_0. A adição de 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 estão em conformidade com a nova restrição. Uma vez que esta validação é realizada como uma operação de longa duração, as transações em curso na tabela não são afetadas. Para mais informações, consulte o artigo Desempenho da atualização do esquema. Se existirem violações nos dados existentes, a restrição é revertida.

Remova uma restrição de verificação

A declaração DDL seguinte elimina uma restrição CHECK da tabela Concerts.

ALTER TABLE Concerts
DROP CONSTRAINT concert_id_gt_0;

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

Não é permitido modificar a expressão de uma restrição CHECK. Em alternativa, tem de remover a restrição existente e criar uma nova restrição com a nova expressão.

Veja 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 na sua base de dados. Seguem-se alguns exemplos das perguntas às quais pode responder consultando o esquema de informações.

Que restrições de verificação estão definidas na minha base 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 na minha base de dados?

Se adicionou uma restrição de verificação a uma tabela existente, pode querer ver o respetivo estado atual para determinar, por exemplo, se todos os dados existentes foram validados em relação à restrição. Se SPANNER_STATE devolver VALIDATING_DATA na seguinte consulta, significa que o Spanner ainda está a validar os dados existentes em função dessa restrição.

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

O que se segue?