CREATE | ALTER TABLE … CONSTRAINT¶

Ce chapitre décrit comment créer des contraintes en spécifiant une clause CONSTRAINT dans une instruction CREATE TABLE, CREATE HYBRID 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, voir Contraintes.

Si vous créez ou modifiez des tables hybrides, la syntaxe de définition des contraintes est la même ; cependant, les règles et les exigences sont différentes.

Syntaxe des contraintes 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

Syntaxe des contraintes 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 } ]
  [ 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

Propriétés des contraintes¶

Pour la compatibilité avec d’autres bases de données et pour une utilisation avec des tables hybrides, Snowflake fournit des propriétés de contrainte. 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

Pour les tables Snowflake standard, 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.

Une exception est la propriété RELY. Si vous avez vérifié que les données de vos tables standard sont conformes aux contraintes UNIQUE, PRIMARY KEY et FOREIGN KEY, vous pouvez définir la propriété RELY pour ces contraintes. Voir aussi Définition de la propriété de contrainte RELY pour éliminer les jointures inutiles.

Si vous créez ou modifiez des tables hybrides, les règles et les exigences sont différentes. Voir Aperçu des contraintes.

La plupart des propriétés de contrainte prises en charge sont des propriétés standard ANSI SQL ; cependant, les propriétés suivantes sont des extensions Snowflake :

ENABLE | DISABLE
VALIDATE | NOVALIDATE
RELY | NORELY

Vous pouvez également définir un commentaire dans une définition de contrainte hors ligne ; voir Commentaires au niveau des contraintes.

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

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

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

Copy

{ ENFORCED | NOT ENFORCED }

Spécifie si la contrainte est appliquée dans une transaction. Pour les tables standard, NOT NULL est le seul type de contrainte appliqué par Snowflake, quelle que soit cette propriété.

Pour les tables hybrides, vous ne pouvez pas définir la propriété NOT ENFORCED sur les contraintes PRIMARY KEY, FOREIGN KEY et UNIQUE. La définition de cette propriété entraîne une erreur « propriété de contrainte non valide ».

Voir aussi Contraintes d’intégrité référentielle.

Par défaut : NOT ENFORCED

{ DEFERRABLE | NOT DEFERRABLE }

Spécifie si, dans les transactions suivantes, le contrôle de contrainte peut être différé jusqu’à la fin de la transaction.

Par défaut : DEFERRABLE

INITIALLY { DEFERRED | IMMEDIATE }

Pour les contraintes DEFERRABLE, spécifie si la vérification des contraintes peut être différée à partir de la transaction suivante.

Par défaut : INITIALLY DEFERRED

{ ENABLE | DISABLE }

Spécifie si la contrainte est activée ou désactivée. Ces propriétés sont fournies pour la compatibilité avec Oracle.

Par défaut : DISABLE

{ VALIDATE | NOVALIDATE }

Indique si les données existantes de la table doivent être validées lors de la création d’une contrainte. S’applique uniquement lorsque { ENFORCED | NOT ENFORCED } ou { ENABLE | DISABLE } est spécifié.

Par défaut : NOVALIDATE

{ RELY | NORELY }

Indique si une contrainte en mode NOVALIDATE est prise en compte lors de la réécriture de la requête.

Si vous avez vérifié que les données du tableau sont conformes aux contraintes, vous pouvez modifier cette propriété sur RELY pour indiquer que l’optimiseur de requêtes doit s’attendre à une telle intégrité des données. Pour les tables standard, il est de votre responsabilité d’appliquer les contraintes RELY ; sinon, vous risquez un comportement imprévu et/ou des résultats inattendus.

La définition de la propriété RELY peut améliorer la performance des requêtes (par exemple, en éliminant les jointures inutiles).

Pour les contraintes de clé primaire et de clé étrangère, définissez cette propriété à la fois sur la contrainte de clé primaire et sur la contrainte de clé étrangère. Par exemple :

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

Par défaut : NORELY

Propriétés (pour les contraintes de clé étrangère uniquement)¶

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 { FULL | PARTIAL | SIMPLE }

Indique si la contrainte de clé étrangère est satisfaite en ce qui concerne les valeurs NULL dans une ou plusieurs colonnes.

Par défaut : MATCH FULL

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

Spécifie l’action effectuée lorsque la clé primaire / unique d’une clé étrangère est mise à jour ou supprimée.

Par défaut : UPDATE NO ACTION

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

Spécifie l’action effectuée lorsque la clé primaire / unique de la clé étrangère est supprimée.

Par défaut : DELETE NO ACTION

Valeurs autres que par défaut pour les propriétés ENABLE et VALIDATE¶

Pour la compatibilité syntaxique avec d’autres bases de données, Snowflake prend en charge la spécification de valeurs autres que celles par défaut pour les propriétés des contraintes.

Cependant, si vous spécifiez ENABLE ou VALIDATE (les valeurs autres que celles par défaut de ces propriétés) lors de la création d’une nouvelle contrainte, la contrainte n’est pas créée. Ceci ne s’applique pas à RELY. La spécification de RELY entraîne la création de la nouvelle contrainte.

Notez que Snowflake fournit un paramètre de session, UNSUPPORTED_DDL_ACTION, qui détermine si la spécification de valeurs autres que les valeurs par défaut lors de la création d’une contrainte génère une erreur.

Commentaires au niveau des contraintes¶

Comme pour d’autres objets et constructions de bases de données, Snowflake prend en charge les commentaires sur les contraintes.

Les contraintes hors ligne prennent en charge la clause COMMENT dans la définition de contrainte.

CREATE OR REPLACE TABLE uni (c1 INT, c2 int, CONSTRAINT uni1 UNIQUE(C1) COMMENT 'Unique column');

Copy

Une clause COMMENT dans la définition de la colonne peut être utilisée pour commenter la colonne elle-même ou sa contrainte :
```
CREATE OR REPLACE TABLE uni (c1 INT UNIQUE COMMENT 'Unique column', c2 int);
```
Copy

Notez les limitations suivantes :

Vous ne pouvez pas définir de commentaires sur les contraintes avec la commande COMMENT.
La commande DESCRIBE TABLE affiche les commentaires définis sur les colonnes, mais pas les commentaires définis sur les contraintes. Pour voir les commentaires sur les contraintes, sélectionnez-les dans la liste Vue TABLE_CONSTRAINTS ou Vue REFERENTIAL_CONSTRAINTS.
La clause COMMENT dans les définitions de colonnes et de contraintes ne prend pas en charge le signe égal (=). Ne pas spécifier :
```
COMMENT = 'My comment'
```
Copy
Utilisez la syntaxe indiquée dans les exemples précédents :
```
COMMENT 'My comment'
```
Copy

Notes sur l’utilisation¶

NOT NULL spécifie que la colonne n’autorise pas les valeurs NULL :
- Pour les tables Snowflake standard, c’est la seule contrainte appliquée. 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 à plusieurs colonnes (par exemple, les clés composées uniques ou primaires) peuvent uniquement être définies 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é.

Exigences en matière de contrôle d’accès¶

Pour créer une clé primaire ou des contraintes uniques :

Lorsque vous modifiez une table existante pour ajouter la contrainte, vous devez utiliser un rôle doté du privilège OWNERSHIP sur la table.
Lors de la création d’une nouvelle table, vous devez utiliser un rôle doté du privilège CREATE TABLE sur le schéma où la table sera créée.

Pour créer des contraintes de clés étrangères :

Vous devez utiliser un rôle qui a le privilège OWNERSHIP sur la table de clés étrangères.
Vous devez utiliser un rôle qui a le privilège REFERENCES sur la table de clés primaires/uniques.

Le privilège REFERENCES peut être accordé aux rôles et révoqués en utilisant les commandes GRANT <privilèges> et REVOKE <privilèges> :

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

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

Copy

Exemples avec des tables standard¶

Pour des exemples de contraintes avec des tables hybrides, voir CREATE HYBRID TABLE.

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