CREATE | ALTER TABLE … CONSTRAINT¶

Ce chapitre décrit comment créer des contraintes en spécifiant une clause CONSTRAINT dans une instruction CREATE TABLE ou ALTER TABLE :

Une contrainte en ligne est spécifiée dans le cadre de la définition d’une colonne particulière.
Une contrainte hors ligne est spécifiée comme une clause indépendante :
- Lors de la création d’une table, la clause fait partie des définitions de colonne de la table.
- Lors de la modification d’une table, la clause est spécifiée comme une action explicite ADD pour la table.

Pour plus d’informations, y compris des exemples détaillés, voir Contraintes.

Syntaxe¶

Clé unique/primaire/étrangère en ligne¶

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

Où :

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

Clé unique/primaire/étrangère hors ligne¶

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

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

Copy

Où :

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

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

Copy

Propriétés des contraintes¶

Pour la compatibilité avec d’autres bases de données, Snowflake fournit des propriétés de contraintes. Les propriétés qui peuvent être spécifiées pour une contrainte dépendent du type :

Certaines propriétés s’appliquent à toutes les clés (unique, primaire et étrangère).
D’autres propriétés ne s’appliquent qu’aux clés étrangères.

Important

Ces propriétés sont fournies pour faciliter la migration à partir d’autres bases de données. Elles ne sont pas appliquées de force ni gérées par Snowflake. Cela signifie que les valeurs par défaut peuvent être modifiées pour ces propriétés, mais si vous modifiez les valeurs par défaut, Snowflake ne crée pas de contrainte.

Pour plus de détails, voir Propriétés des contraintes.

Propriétés (pour toutes les clés)¶

Les propriétés de contraintes suivantes s’appliquent à toutes les clés (l’ordre des propriétés est interchangeable) :

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

Copy

{ VALIDATE | NOVALIDATE } ne s’applique que lorsque { ENFORCED | NOT ENFORCED } ou { ENABLE | DISABLE } est spécifié.
{ RELY | NORELY } ne s’applique que lorsque NOVALIDATE est spécifié.
Valeurs par défaut :

NOT ENFORCED DEFERRABLE INITIALLY DEFERRED DISABLE NOVALIDATE NORELY

Propriétés (pour clés étrangères seulement)¶

Les propriétés de contraintes suivantes s’appliquent seulement aux clés étrangères (l’ordre des propriétés est interchangeable) :

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 ... spécifie si la contrainte de clé étrangère est satisfaite en ce qui concerne les valeurs NULL dans une ou plusieurs colonnes.
ON ... spécifie les actions effectuées lorsque la clé primaire d’une clé étrangère est mise à jour ou supprimée.
Valeurs par défaut :

MATCH FULL UPDATE NO ACTION DELETE NO ACTION

Notes sur l’utilisation¶

NOT NULL spécifie que la colonne n’autorise pas les valeurs NULL :
- C’est la seule contrainte imposée par Snowflake. Voir Contraintes d’intégrité référentielle.
- Elle ne peut être spécifiée qu’en tant que contrainte en ligne dans la définition de colonne.
- La valeur par défaut est d’autoriser les valeurs NULL dans les colonnes.
Les contraintes de plusieurs colonnes (par exemple, les clés composées uniques ou primaires) ne peuvent être définies qu’hors ligne.
Lors de la définition des clés étrangères, qu’elles soient en ligne ou hors ligne, il n’est pas nécessaire de spécifier le(s) nom(s) de colonne(s) de la table de référence si la signature (c’est-à-dire le nom et le type de données) de la ou des colonne(s) de clés étrangères et la ou les colonne(s) de clés principales de la table de référence correspondent exactement.

Si vous créez une clé étrangère, les colonnes de la clause REFERENCES doivent être répertoriées dans le même ordre que les celles répertoriées pour la clé primaire. Par exemple :
```
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
Dans les deux cas, l’ordre des colonnes est c_1, c_2. Si l’ordre des colonnes de la clé étrangère avait été différent (par exemple, c_2, c_1), la tentative de création de la clé étrangère aurait échoué.

Exemples¶

L’exemple ci-dessous montre comment créer une simple contrainte NOT NULL lors de la création d’une table et une autre contrainte NOT NULL lors de la modification d’une table :

Créer une table et créer une contrainte en même temps :
CREATE TABLE table1 (
    col1 INTEGER NOT NULL
    );
Copy
Modifier le tableau pour ajouter une colonne avec une contrainte :
ALTER TABLE table1 
    ADD COLUMN col2 VARCHAR NOT NULL;
Copy

L’exemple suivant spécifie que l’intention de la colonne est de contenir des valeurs uniques, mais indique clairement que la contrainte n’est pas réellement appliquée. Cet exemple montre également comment spécifier un nom pour la contrainte (« uniq_col3 » dans ce cas.)

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

Copy

Ce qui suit crée une table parent avec une contrainte de clé primaire et une autre table avec une contrainte de clé étrangère qui pointe vers les mêmes colonnes que la contrainte de clé primaire de la première table.

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