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 }
                     [ , <col2_name> <col2_type> [ NOT NULL ] { inlineUniquePK | inlineFK } ]
                     [ , ... ] )

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

Copy

Onde:

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

Copy

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 } ]

Copy

Sintaxe para restrições fora de linha¶

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

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

Copy

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>' ]

Copy

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>' ]

Copy

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 }

Copy

{ 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: 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: NOVALIDATE

{ 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/ou resultados inesperados.

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

Para as restrições primárias e de chave estrangeira, defina essa propriedade para a restrição de chave primária e para a restrição de chave estrangeira. 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;

Copy

Padrão: NORELY

Propriedades (apenas para restrições de chave estrangeira)¶

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 } ]

Copy

MATCH { FULL | PARTIAL | SIMPLE }

Especifica se a restrição de chave estrangeira é satisfeita com relação aos valores NULL em uma ou mais das colunas.

Padrão: MATCH FULL

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

Especifica a ação executada quando a chave primária/única 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/única da chave estrangeira é excluída.

Padrão: DELETE NO ACTION

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.

Entretanto, 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.

Observe que 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');

Copy

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);
```
Copy

Observe as seguintes limitações:

Você não pode definir comentários sobre 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 dentro das definições de coluna e restrição não oferece suporte ao sinal de igual (=). Não especifique:
```
COMMENT = 'My comment'
```
Copy
Use a sintaxe mostrada nos exemplos anteriores:
```
COMMENT 'My comment'
```
Copy

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) ...
```
Copy
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 únicas ou de chave primária:

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 de chave estrangeira:

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 exclusiva/primária.

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

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

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

Copy

Exemplos 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);

Copy

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

ALTER TABLE table1 ADD COLUMN col2 VARCHAR NOT NULL;

Copy

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;

Copy

O seguinte cria uma tabela pai com uma restrição de chave primária e outra tabela com uma restrição de chave estrangeira que aponta para as mesmas colunas da primeira restrição de chave primária da 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
);

Copy