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

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

Où :

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

Syntaxe des contraintes hors ligne¶

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

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

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

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

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 }

{ 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 : NOT 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 pour les contraintes PRIMARY KEY et FOREIGN KEY : NOVALIDATE

Par défaut pour les contraintes CHECK : VALIDATE

{ 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 ou des résultats inattendus.

Si la propriété RELY est définie pour une contrainte et qu’une violation de l’intégrité référentielle se produit, les instructions DML et CTAS peuvent insérer des données incorrectes.

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 PRIMARY KEY et FOREIGN KEY associées, définissez cette propriété sur les deux contraintes. 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;

Par défaut : NORELY

Propriétés (pour les contraintes FOREIGN KEY 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 } ]

MATCH { FULL | PARTIAL | SIMPLE }

Indique si la contrainte FOREIGN KEY 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 ou unique de la clé étrangère est mise à jour.

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 ou unique de la clé étrangère est supprimée.

Par défaut : DELETE NO ACTION

Propriétés (pour les contraintes CHECK uniquement)¶

Les propriétés de contraintes suivantes s’appliquent uniquement aux contraintes CHECK :

CHECK ( <expr> )

CHECK ( expr )

Une expression qui définit la condition à imposer.

L’expression peut contenir l’un des éléments suivants :

Colonnes de table définies dans la table sur laquelle la contrainte CHECK opère.
Valeurs constantes.
Fonctions scalaires qui ne dépendent pas de l’environnement ou du contexte d’exécution.

L’expression ne peut pas contenir l’un des éléments suivants :

Fonctions définies par l’utilisateur (UDFs).
Fonctions d’agrégation, fonctions de fenêtre, fonctions de table ou sous-requêtes.
Fonctions définies par le système qui modifient l’état de la base de données, comme la fonction SYSTEM$CANCEL_ALL_QUERIES.
Fonctions non déterministes définies par le système, comme la fonction RANDOM.
Fonctions définies par le système qui dépendent de l’environnement ou du contexte d’exécution, comme la fonction CURRENT_DATE ou la fonction CURRENT_ROLE.

Pour plus d’informations, voir Contraintes CHECK.

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, pour les contraintes PRIMARY KEY, UNIQUE et FOREIGN KEY, si vous spécifiez ENABLE ou VALIDATE (c’est-à-dire des valeurs autres que celles par défaut pour 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.

Pour les contraintes CHECK, ENABLE est la valeur par défaut et est obligatoire. Si vous spécifiez DISABLE, alors la contrainte CHECK n’est pas créée. NOVALIDATE et VALIDATE sont tous deux pris en charge pour les nouvelles tables. VALIDATE n’est pas pris en charge sur les tables existantes.

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

Notez les limitations suivantes :

Vous ne pouvez pas définir de commentaires sur les contraintes en utilisant 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'
```
Utilisez la syntaxe indiquée dans les exemples précédents :
```
COMMENT 'My comment'
```

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) ...
```
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 des contraintes PRIMARY KEY ou UNIQUE :

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 FOREIGN KEY :

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 ou uniques.

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

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

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

Exemples de contraintes 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);

Modifier le tableau pour ajouter une colonne avec une contrainte :

ALTER TABLE table1 ADD COLUMN col2 VARCHAR NOT NULL;

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;

Le code suivant crée une table parente avec une contrainte PRIMARY KEY et une autre table avec une contrainte FOREIGN KEY qui pointe vers les mêmes colonnes que la contrainte PRIMARY KEY 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
);

L’exemple suivant spécifie une contrainte CHECK en ligne dans une instruction CREATE TABLE :

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

Cette contrainte CHECK échoue pour les opérations DML suivantes, car la quantité est une valeur négative ou égale à zéro :

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;

L’exemple suivant spécifie une contrainte CHECK hors ligne sur plusieurs colonnes :

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

La contrainte CHECK garantit que le prix ne dépasse pas le prix maximum.

L’exemple suivant spécifie une contrainte CHECK en ligne dans une instruction 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;

La contrainte CHECK garantit que la nouvelle table high_value_products contient uniquement des éléments considérés comme étant à prix élevé.