Créer et gérer des contraintes de vérification

Une contrainte CHECK vous permet de spécifier que les valeurs d'une ou de plusieurs colonnes doivent respecter une expression booléenne. Cet article explique comment gérer ce type de contrainte dans votre base de données.

Ajouter une contrainte de vérification à une nouvelle table

Dans l'extrait de code CREATE TABLE suivant, nous allons créer une table pour stocker des informations sur les concerts. Pour exiger que l'heure de fin d'un concert soit postérieure à l'heure de début, nous incluons une contrainte de vérification.

GoogleSQL 

CREATE TABLE Concerts (
  ConcertId INT64,
  StartTime Timestamp,
  EndTime Timestamp,
  CONSTRAINT start_before_end CHECK(StartTime < EndTime),
) PRIMARY KEY (ConcertId);

PostgreSQL 

CREATE TABLE Concerts (
  ConcertId BIGINT,
  StartTime TIMESTAMPTZ,
  EndTime TIMESTAMPTZ,
  CONSTRAINT start_before_end CHECK(StartTime < EndTime),
  PRIMARY KEY (ConcertId)
);

La définition de la contrainte commence par le mot clé CONSTRAINT. Dans cet exemple, nous avons explicitement nommé la contrainte en tant que start_before_end, ce qui permet de l'identifier facilement dans les messages d'erreur et chaque fois que nous devons y faire référence. Si aucun nom n'est indiqué, Spanner en fournit un, en ajoutant le préfixe CK_ au nom généré. Les noms de contrainte sont limités au schéma, ainsi qu'aux noms des tables et des index, et doivent être uniques dans le schéma. La définition de la contrainte de contrôle se compose du mot clé CHECK, suivi d'une expression entre parenthèses. L'expression ne peut faire référence qu'aux colonnes de cette table. Dans cet exemple, il s'agit des valeurs StartTime et EndTime, et la vérification effectuée garantit que l'heure de début d'un concert est toujours antérieure à l'heure de fin.

La valeur de l'expression de contrainte de vérification est évaluée lors de l'insertion d'une nouvelle ligne ou lors de la mise à jour des valeurs StartTime ou EndTime d'une ligne existante. Si l'expression renvoie la valeur TRUE ou NULL, la modification des données est autorisée par la contrainte de vérification. Si l'expression renvoie la valeur FALSE, la modification de données n'est pas autorisée.

  • Les restrictions suivantes s'appliquent à une condition de contrainte de vérification expression.

    • L'expression ne peut référencer que des colonnes dans la même table.

    • L'expression doit référencer au moins une colonne non générée, directement ou via une colonne générée faisant référence à une colonne non générée.

    • L'expression ne peut pas faire référence à des colonnes pour lesquelles l'option allow_commit_timestamp est définie.

    • L'expression ne peut pas contenir de sous-requêtes.

    • L'expression ne peut pas contenir de fonctions non déterministes, telles que CURRENT_DATE() et CURRENT_TIMESTAMP().

Ajouter une contrainte de vérification à une table existante

À l'aide de l'instruction ALTER TABLE suivante, nous ajoutons une contrainte pour garantir que tous les ID de concert sont supérieurs à zéro.

ALTER TABLE Concerts
ADD CONSTRAINT concert_id_gt_0 CHECK (ConcertId > 0);

Nous avons de nouveau attribué un nom à la contrainte, concert_id_gt_0. L'ajout d'une contrainte CHECK à une table existante lance immédiatement la contrainte pour les nouvelles données, ainsi qu'une opération de longue durée pour confirmer que les données existantes sont conformes à la nouvelle contrainte. Comme cette validation est effectuée en tant qu'opération de longue durée, les transactions en cours sur la table ne sont pas affectées. Pour en savoir plus, consultez la page Performances des mises à jour de schéma. En cas de non-respect des données existantes, la contrainte est annulée.

Supprimer une contrainte de vérification

L'instruction LDD suivante supprime une contrainte CHECK de la table Concerts.

ALTER TABLE Concerts
DROP CONSTRAINT concert_id_gt_0;

Modifier une expression de contrainte de vérification

Vous ne pouvez pas modifier l'expression d'une contrainte CHECK. À la place, vous devez supprimer la contrainte existante et en créer une autre avec la nouvelle expression.

Afficher les propriétés d'une contrainte de vérification

Le schéma INFORMATION_SCHEMA de Spanner contient des informations sur la vérification. les contraintes de votre base de données. Voici quelques exemples de questions auxquelles vous pouvez répondre en interrogeant le schéma d'information.

Quelles contraintes de vérification sont définies dans ma base de données ?

SELECT tc.CONSTRAINT_NAME, tc.TABLE_NAME, tc.CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
WHERE tc.CONSTRAINT_TYPE = 'CHECK';

Quel est l'état actuel des contraintes de vérification dans ma base de données ?

Si vous avez ajouté une contrainte de vérification à une table existante, vous pouvez afficher son état actuel pour déterminer, par exemple, si toutes les données existantes ont été validées par rapport à la contrainte. Si SPANNER_STATE renvoie VALIDATING_DATA dans la requête suivante, cela signifie que la validation des données existantes sur cette contrainte par Spanner est en cours.

SELECT cc.CONSTRAINT_NAME, cc.SPANNER_STATE
FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS as cc;

Étape suivante