CREATE | ALTER TABLE … CONSTRAINT¶

Este tópico descreve como criar restrições especificando uma cláusula CONSTRAINT em uma instrução CREATE TABLE, CREATE HYBRID TABLE ou ALTER TABLE.

Uma restrição em linha é especificada como parte da definição da coluna individual.
Uma restrição fora de linha é especificada como uma cláusula independente:
- Ao criar uma tabela, a cláusula faz parte das definições da coluna para a tabela.
- Ao alterar uma tabela, a cláusula é especificada como uma ação ADD explícita para a tabela.

Para obter mais informações, consulte Restrições.

Se você estiver criando ou alterando tabelas híbridas, a sintaxe para definir restrições é a mesma; no entanto, as regras e os requisitos são diferentes.

Sintaxe para restrições em linha¶

CREATE TABLE <name> (
  <col1_name> <col1_type>  [ NOT NULL ] { inlineUniquePK | inlineFK | inlineCH }
  [ , <col2_name> <col2_type> [ NOT NULL ] { inlineUniquePK | inlineFK | inlineCH } ]
  [ , ... ]
)

ALTER TABLE <name> ADD COLUMN
  <col_name> <col_type> [ NOT NULL ] { inlineUniquePK | inlineFK | inlineCH }

Onde:

inlineUniquePK ::=
  [ CONSTRAINT <constraint_name> ]
  { UNIQUE | PRIMARY KEY }
  [ [ NOT ] ENFORCED ]
  [ [ NOT ] DEFERRABLE ]
  [ INITIALLY { DEFERRED | IMMEDIATE } ]
  [ { ENABLE | DISABLE } ]
  [ { VALIDATE | NOVALIDATE } ]
  [ { RELY | NORELY } ]

inlineFK ::=
  [ CONSTRAINT <constraint_name> ]
  [ FOREIGN KEY ]
  REFERENCES <ref_table_name> [ ( <ref_col_name> ) ]
  [ MATCH { FULL | SIMPLE | PARTIAL } ]
  [ ON [ UPDATE { CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION } ]
       [ DELETE { CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION } ] ]
  [ [ NOT ] ENFORCED ]
  [ [ NOT ] DEFERRABLE ]
  [ INITIALLY { DEFERRED | IMMEDIATE } ]
  [ { ENABLE | DISABLE } ]
  [ { VALIDATE | NOVALIDATE } ]
  [ { RELY | NORELY } ]

inlineCH ::=
  [ CONSTRAINT <constraint_name> ] CHECK ( <expr> )
  [ ENABLE { VALIDATE | NOVALIDATE } ]

Sintaxe para restrições fora de linha¶

CREATE TABLE <name> ... (
  <col1_name> <col1_type>
  [ , <col2_name> <col2_type> , ... ]
  [ , { outoflineUniquePK | outoflineFK | outoflineCH } ]
  [ , { outoflineUniquePK | outoflineFK | outoflineCH } ]
  [ , ... ]
)

ALTER TABLE <name> ... ADD { outoflineUniquePK | outoflineFK | outoflineCH }

Onde:

outoflineUniquePK ::=
  [ CONSTRAINT <constraint_name> ]
  { UNIQUE | PRIMARY KEY } ( <col_name> [ , <col_name> , ... ] )
  [ [ NOT ] ENFORCED ]
  [ [ NOT ] DEFERRABLE ]
  [ INITIALLY { DEFERRED | IMMEDIATE } ]
  [ { ENABLE | DISABLE } ]
  [ { VALIDATE | NOVALIDATE } ]
  [ { RELY | NORELY } ]
  [ COMMENT '<string_literal>' ]

outoflineFK ::=
  [ CONSTRAINT <constraint_name> ]
  FOREIGN KEY ( <col_name> [ , <col_name> , ... ] )
  REFERENCES <ref_table_name> [ ( <ref_col_name> [ , <ref_col_name> , ... ] ) ]
  [ MATCH { FULL | SIMPLE | PARTIAL } ]
  [ ON [ UPDATE { CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION } ]
       [ DELETE { CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION } ] ]
  [ [ NOT ] ENFORCED ]
  [ [ NOT ] DEFERRABLE ]
  [ INITIALLY { DEFERRED | IMMEDIATE } ]
  [ { ENABLE | DISABLE } ]
  [ { VALIDATE | NOVALIDATE } ]
  [ { RELY | NORELY } ]
  [ COMMENT '<string_literal>' ]

outoflineCH ::=
  [ CONSTRAINT <constraint_name> ] CHECK ( <expr> )
  [ ENABLE { VALIDATE | NOVALIDATE } ]

Propriedades de restrição¶

Para compatibilidade com outros bancos de dados e para uso com tabelas híbridas, o Snowflake fornece propriedades de restrição. As propriedades que podem ser especificadas para uma restrição dependem do tipo:

Algumas propriedades se aplicam a todas as chaves (únicas, primárias e estrangeiras).
Outras propriedades se aplicam somente a chaves estrangeiras.

Importante

Para tabelas Snowflake padrão, essas propriedades são fornecidas para facilitar a migração de outros bancos de dados. Elas não são impostas ou mantidas pelo Snowflake. Isso significa que os padrões podem ser alterados para estas propriedades, mas alterar os padrões resulta no Snowflake não criando a restrição.

Uma exceção é a propriedade RELY. Se você tiver garantido que os dados em suas tabelas padrão estão em conformidade com as restrições UNIQUE, PRIMARY KEY e FOREIGN KEY, é possível definir a propriedade RELY para essas restrições. Consulte também Configuração da propriedade de restrição RELY para eliminar junções desnecessárias.

Se você estiver criando ou alterando tabelas híbridas, as regras e os requisitos serão diferentes. Consulte Visão geral das restrições.

A maioria das propriedades de restrição compatíveis são propriedades padrão ANSI SQL; no entanto, as seguintes propriedades são extensões do Snowflake:

ENABLE | DISABLE
VALIDATE | NOVALIDATE
RELY | NORELY

Você também pode definir um comentário dentro de uma definição de restrição fora de linha; consulte Comentários sobre restrições.

Propriedades (para todas as restrições)¶

As seguintes propriedades se aplicam a todas as restrições (a ordem das propriedades é intercambiável):

[ NOT ] ENFORCED
[ NOT ] DEFERRABLE
INITIALLY { DEFERRED | IMMEDIATE }
{ ENABLE | DISABLE }
{ VALIDATE | NOVALIDATE }
{ RELY | NORELY }

{ ENFORCED | NOT ENFORCED }

Especifica se a restrição é aplicada em uma transação. Para tabelas padrão, NOT NULL é o único tipo de restrição imposta por Snowflake, independentemente desta propriedade.

Para tabelas híbridas, não é possível definir a propriedade NOT ENFORCED nas restrições PRIMARY KEY, FOREIGN KEY e UNIQUE. Definir esta propriedade resulta em um erro de “propriedade de restrição inválida”.

Consulte também Restrições de integridade referencial.

Padrão: NOT ENFORCED

{ DEFERRABLE | NOT DEFERRABLE }

Especifica se, em transações subsequentes, a verificação de restrição pode ser adiada até o final da transação.

Padrão: NOT DEFERRABLE

INITIALLY { DEFERRED | IMMEDIATE }

Para restrições DEFERRABLE, especifica se a verificação das restrições pode ser adiada, a partir da próxima transação.

Padrão: INITIALLY DEFERRED

{ ENABLE | DISABLE }

Especifica se a restrição está ativada ou desativada. Essas propriedades são fornecidas para compatibilidade com a Oracle.

Padrão: DISABLE

{ VALIDATE | NOVALIDATE }

Especifica se os dados existentes na tabela devem ser validados quando uma restrição é criada. Aplica-se somente quando { ENFORCED | NOT ENFORCED } ou { ENABLE | DISABLE } é especificado.

Padrão para restrições PRIMARY KEY e FOREIGN KEY: NOVALIDATE

Padrão para restrições CHECK: VALIDATE

{ RELY | NORELY }

Especifica se uma restrição no modo NOVALIDATE é levada em conta durante a reescrita da consulta.

Se você tiver garantido que os dados na tabela estão em conformidade com as restrições, poderá alterar esta propriedade para RELY para indicar que o otimizador de consulta deve esperar essa integridade de dados. Para tabelas padrão, é sua responsabilidade impor restrições RELY. Caso contrário, você pode correr o risco de comportamento não intencional e resultados inesperados.

Se a propriedade RELY for definida para uma restrição e ocorrer uma violação da integridade referencial, as instruções DML e CTAS poderão inserir dados incorretos.

Definir a propriedade RELY pode melhorar o desempenho da consulta (por exemplo, eliminando junções desnecessárias).

Para as restrições PRIMARY KEY e FOREIGN KEY relacionadas, defina essa propriedade em ambas. Por exemplo:

ALTER TABLE table_with_primary_key ALTER CONSTRAINT a_primary_key_constraint RELY;
ALTER TABLE table_with_foreign_key ALTER CONSTRAINT a_foreign_key_constraint RELY;

Padrão: NORELY

Propriedades (somente para restrições FOREIGN KEY)¶

As seguintes propriedades de restrição se aplicam somente a chaves estrangeiras (a ordem das propriedades é intercambiável):

MATCH { FULL | SIMPLE | PARTIAL }
ON [ UPDATE { CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION } ]
   [ DELETE { CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION } ]

MATCH { FULL | PARTIAL | SIMPLE }

Especifica se a restrição FOREIGN KEY é satisfeita em relação aos valores NULL em uma ou mais colunas.

Padrão: MATCH FULL

UPDATE { CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION }

Especifica a ação executada quando a chave primária ou exclusiva da chave estrangeira é atualizada.

Padrão: UPDATE NO ACTION

DELETE { CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION }

Especifica a ação executada quando a chave primária ou exclusiva da chave estrangeira é excluída.

Padrão: DELETE NO ACTION

Propriedades (somente para restrições CHECK)¶

As seguintes propriedades de restrição se aplicam somente a restrições CHECK:

CHECK ( <expr> )

CHECK ( expr )

Uma expressão que define a condição a ser imposta.

A expressão pode conter qualquer um dos seguintes itens:

Colunas da tabela definidas na tabela na qual a restrição CHECK opera.
Valores constantes.
Funções escalares que não dependem do ambiente ou do contexto de execução.

A expressão não pode conter nenhum dos seguintes itens:

Funções definidas pelo usuário (user-defined functions, UDFs).
Funções de agregação, funções de janela, funções de tabela ou subconsultas.
Funções definidas pelo sistema que alteram o estado do banco de dados, como a função SYSTEM$CANCEL_ALL_QUERIES.
Funções definidas pelo sistema não determinísticas, como a função RANDOM.
Funções definidas pelo sistema que dependem do ambiente ou do contexto de execução, como a função CURRENT_DATE ou a CURRENT_ROLE.

Para obter mais informações, consulte Restrições CHECK.

Valores padrão para e propriedades ENABLE e VALIDATE¶

Para compatibilidade de sintaxe com outros bancos de dados, o Snowflake oferece suporte à especificação de valores padrão para propriedades de restrição.

No entanto, para as restrições PRIMARY KEY, UNIQUE e FOREIGN KEY, se você especificar ENABLE ou VALIDATE (os valores não padrão para essas propriedades) ao criar uma nova restrição, a restrição não será criada. Isso não se aplica a RELY. Especificar RELY resulta na criação da nova restrição.

Para restrições CHECK, ENABLE é o padrão e é obrigatório. Se você especificar DISABLE, a restrição CHECK não será criada. Tanto NOVALIDATE quanto VALIDATE são compatíveis com novas tabelas. VALIDATE não é compatível com tabelas existentes.

O Snowflake fornece um parâmetro de sessão, UNSUPPORTED_DDL_ACTION, que determina se a especificação de valores não padrão durante a criação da restrição gera um erro.

Comentários sobre restrições¶

Semelhante a outros objetos e construções de banco de dados, o Snowflake oferece suporte a comentários sobre restrições:

Restrições fora de linha dão suporte à cláusula COMMENT dentro da definição de restrição.
```
CREATE OR REPLACE TABLE uni (c1 INT, c2 int, CONSTRAINT uni1 UNIQUE(C1) COMMENT 'Unique column');
```
Uma cláusula COMMENT dentro da definição da coluna pode ser usada para comentar na coluna em si ou em sua restrição:
```
CREATE OR REPLACE TABLE uni (c1 INT UNIQUE COMMENT 'Unique column', c2 int);
```

Observe as seguintes limitações:

Não é possível definir comentários em restrições usando o comando COMMENT.
O comando DESCRIBE TABLE mostra comentários definidos em colunas, mas não comentários definidos em restrições. Para ver comentários em restrições, selecione Exibição TABLE_CONSTRAINTS ou Exibição REFERENTIAL_CONSTRAINTS.
A cláusula COMMENT em definições de coluna e restrição não aceita o sinal de igual (=). Não especifique:
```
COMMENT = 'My comment'
```
Use a sintaxe mostrada nos exemplos anteriores:
```
COMMENT 'My comment'
```

Notas de uso¶

NOT NULL especifica que a coluna não permite valores NULL:
- Para tabelas Snowflake padrão, essa é a única restrição aplicada. Consulte Restrições de integridade referencial.
- Ela pode ser especificada apenas como uma restrição em linha dentro da definição da coluna.
- O padrão é permitir valores NULL em colunas.
Restrições de múltiplas colunas (chaves primárias ou exclusivas compostas) só podem ser definidas fora de linha.
Ao definir chaves estrangeiras, em linha ou fora de linha, o(s) nome(s) da(s) coluna(s) da tabela referenciada não precisa(m) ser especificado(s) se a assinatura (nome e tipo de dados) da(s) coluna(s) de chave estrangeira e da(s) coluna(s) de chave primária da tabela referenciada corresponderem exatamente.

Se você criar uma chave estrangeira, as colunas na cláusula REFERENCES deverão ser listadas na mesma ordem em que foram listadas para a chave primária. Por exemplo:
```
CREATE TABLE parent ... CONSTRAINT primary_key_1 PRIMARY KEY (c_1, c_2) ...
CREATE TABLE child  ... CONSTRAINT foreign_key_1 FOREIGN KEY (...) REFERENCES parent (c_1, c_2) ...
```
Em ambos os casos, a ordem das colunas é c_1, c_2. Se a ordem das colunas na chave estrangeira fosse diferente (por exemplo, c_2, c_1), a tentativa de criar a chave estrangeira teria falhado.

Requisitos de controle de acesso¶

Para criar restrições PRIMARY KEY ou UNIQUE:

Ao alterar uma tabela existente para adicionar a restrição, você deve usar uma função com o privilégio OWNERSHIP na tabela.
Ao criar uma nova tabela, é necessário usar uma função que tenha privilégio CREATE TABLE no esquema em que a tabela será criada.

Para criar restrições FOREIGN KEY:

Você deve usar uma função que tenha o privilégio OWNERSHIP na tabela de chave estrangeira.
Você deve usar uma função que tenha o privilégio REFERENCES na tabela de chave primária ou exclusiva.

O privilégio REFERENCES pode ser concedido e revogado de funções usando os comandos GRANT <privilégios> … TO ROLE e REVOKE <privilégios> … FROM ROLE:

GRANT REFERENCES ON TABLE <pk_table_name> TO ROLE <role_name>

REVOKE REFERENCES ON TABLE <pk_table_name> FROM ROLE <role_name>

Exemplos de restrições com tabelas padrão¶

Para exemplos de restrições com tabelas híbridas, consulte CREATE HYBRID TABLE.

O exemplo abaixo mostra como criar uma simples restrição NOT NULL ao criar uma tabela, e outra restrição NOT NULL ao alterar uma tabela:

Criar uma tabela e, ao mesmo tempo, criar uma restrição:

CREATE TABLE table1 (col1 INTEGER NOT NULL);

Alterar a tabela para adicionar uma coluna com uma restrição:

ALTER TABLE table1 ADD COLUMN col2 VARCHAR NOT NULL;

O exemplo a seguir especifica que a intenção da coluna é manter valores únicos, mas deixa claro que a restrição não é realmente aplicada. Este exemplo também demonstra como especificar um nome para a restrição (“uniq_col3”, neste caso).

ALTER TABLE table1 
  ADD COLUMN col3 VARCHAR NOT NULL CONSTRAINT uniq_col3 UNIQUE NOT ENFORCED;

O exemplo a seguir cria uma tabela pai com uma restrição PRIMARY KEY e outra tabela com uma restrição FOREIGN KEY que aponta para as mesmas colunas que a restrição PRIMARY KEY da primeira tabela.

CREATE TABLE table2 (
  col1 INTEGER NOT NULL,
  col2 INTEGER NOT NULL,
  CONSTRAINT pkey_1 PRIMARY KEY (col1, col2) NOT ENFORCED
);
CREATE TABLE table3 (
  col_a INTEGER NOT NULL,
  col_b INTEGER NOT NULL,
  CONSTRAINT fkey_1 FOREIGN KEY (col_a, col_b) REFERENCES table2 (col1, col2) NOT ENFORCED
);

O exemplo a seguir especifica uma restrição em linha CHECK em uma instrução CREATE TABLE:

CREATE TABLE test_check_constraint_orders (
  order_id INT,
  quantity INT CHECK (quantity > 0),
  price NUMBER(10, 2));

Essa restrição CHECK falha para as seguintes operações DML porque a quantidade é um valor negativo ou zero:

INSERT INTO test_check_constraint_orders (order_id, quantity, price)
  VALUES (101, -5, 25.35);

UPDATE test_CHECK_constraint_orders
  SET quantity = 0
  WHERE order_id = 101;

O exemplo a seguir especifica uma restrição CHECK fora de linha em várias colunas:

CREATE TABLE test_check_constraint_max_orders (
  order_id INT,
  quantity INT,
  price NUMBER(10, 2),
  max_price NUMBER(10, 2),
  CONSTRAINT chk_price_max CHECK (price < max_price));

A restrição CHECK garante que o preço não exceda o preço máximo.

O exemplo a seguir especifica uma restrição CHECK em linha em uma instrução CTAS:

CREATE TABLE high_value_products (
  product_id INT,
  product_name VARCHAR(100),
  list_price NUMBER(10, 2),
  CONSTRAINT high_price CHECK (list_price > 100)
  )
  AS SELECT product_id,
            product_name,
            list_price
  FROM products
  WHERE list_price > 100;

A restrição CHECK garante que a nova tabela high_value_products contenha apenas itens considerados de alto preço.