Contraintes sur Azure Databricks

Azure Databricks prend en charge les clauses de gestion des contraintes SQL standard :

• Les contraintes appliquées vérifient l’intégrité des données avant d’ajouter des lignes à une table.
• Les contraintes d’information (clé primaire, clé étrangère et contraintes uniques) définissent les relations entre les champs des tables et ne sont pas appliquées.

Toutes les contraintes sur Azure Databricks nécessitent Delta Lake.

Pour un concept associé dans les pipelines déclaratifs Spark Lakeflow, consultez Gérer la qualité des données avec des attentes de pipeline.

Contraintes appliquées

En cas de violation d’une contrainte, la transaction échoue avec une erreur. Azure Databricks prend en charge deux types de contraintes :

• NOT NULL : indique que les valeurs de colonnes spécifiques ne peuvent pas être Null.
• CHECK : indique qu’une expression booléenne spécifiée doit avoir la valeur true pour chaque ligne d’entrée.

Contrainte NOT NULL

Lorsque vous créez une table, spécifiez NOT NULL des contraintes dans le schéma. Pour ajouter ou supprimer des contraintes NOT NULL, utilisez la commande ALTER TABLE ... ALTER COLUMN.

CREATE TABLE people10m (
  id INT NOT NULL,
  firstName STRING,
  middleName STRING NOT NULL,
  lastName STRING,
  gender STRING,
  birthDate TIMESTAMP,
  ssn STRING,
  salary INT
);

ALTER TABLE people10m ALTER COLUMN middleName DROP NOT NULL;
ALTER TABLE people10m ALTER COLUMN ssn SET NOT NULL;

Azure Databricks vérifie que toutes les lignes existantes répondent à la contrainte avant d’ajouter une contrainte NOT NULL à une table.

Si vous spécifiez une contrainte NOT NULL sur une colonne imbriquée dans un struct, le struct parent doit également ne pas avoir la valeur Null. Les colonnes imbriquées dans des types de tableau ou de mappage n’acceptent pas de NOT NULL contraintes.

Voir CREATE TABLE [USING] et ALTER TABLE ALTER COLUMN.

Contrainte CHECK

Gérez les contraintes CHECK avec les commandes ALTER TABLE ADD CONSTRAINT et ALTER TABLE DROP CONSTRAINT. ALTER TABLE ADD CONSTRAINT vérifie que toutes les lignes existantes répondent à la contrainte avant d’ajouter la contrainte à la table.

Les restrictions suivantes s’appliquent aux contraintes de vérification :

• Une CHECK expression de contrainte peut utiliser toutes les fonctions SQL dans Spark qui retournent toujours le même résultat lorsqu’elles reçoivent les mêmes valeurs d’argument, à l’exception des types de fonctions suivants :
  • Fonctions définies par l’utilisateur.
  • Fonctions d’agrégation.
  • Fonctions de fenêtre.
  • Les fonctions retournent plusieurs lignes.

Ajouter à une table existante

CREATE TABLE people10m (
  id INT,
  firstName STRING,
  middleName STRING,
  lastName STRING,
  gender STRING,
  birthDate TIMESTAMP,
  ssn STRING,
  salary INT
);

ALTER TABLE people10m ADD CONSTRAINT dateWithinRange CHECK (birthDate > '1900-01-01');
ALTER TABLE people10m DROP CONSTRAINT dateWithinRange;

Voir ALTER TABLE ADD CONSTRAINT et ALTER TABLE DROP CONSTRAINT.

Afficher les propriétés de la table de contraintes de vérification

Utilisez les commandes DESCRIBE DETAIL et SHOW TBLPROPERTIES pour afficher les contraintes de la table CHECK.

ALTER TABLE people10m ADD CONSTRAINT validIds CHECK (id > 1 and id < 99999999);

DESCRIBE DETAIL people10m;

SHOW TBLPROPERTIES people10m;

Supprimer les contraintes de vérification

Dans Databricks Runtime 15.4 LTS et versions ultérieures, utilisez la DROP FEATURE commande pour supprimer les contraintes de vérification d’une table et rétrograder le protocole de table.

Consultez Supprimer une fonctionnalité de table Delta Lake et passer à une version antérieure du protocole de table.

Déclarer la clé primaire, la clé étrangère et les contraintes uniques

La clé primaire, la clé étrangère et les contraintes uniques sont uniquement informationnelles et ne sont pas appliquées. Ils peuvent améliorer les performances par le biais d’optimisations des requêtes.

• Clé primaire et clé étrangère : disponible pour les tables Unity Catalog et Delta Lake dans Databricks Runtime 13.3 LTS et versions ultérieures. Disponibilité générale dans Databricks Runtime 15.2 et les versions ultérieures. Les clés étrangères doivent référencer une clé primaire ou une contrainte unique dans une autre table.
• Unique : disponible en préversion publique pour les tables Unity Catalog et Delta Lake dans Databricks SQL et Databricks Runtime 18.2 et versions ultérieures. Une table peut comprendre plusieurs contraintes uniques. Les clés étrangères peuvent faire référence à une colonne unique à l’aide de REFERENCES parent_table(unique_col). Les colonnes uniques peuvent être nullables, car NULL les valeurs sont traitées comme distinctes les unes des autres.

Interrogez le information_schema, utilisez DESCRIBE TABLE EXTENDED ou utilisez SHOW CREATE TABLE pour obtenir des détails sur la façon dont les contraintes sont appliquées à l’échelle d’un catalogue donné.

Ajouter à de nouvelles tables

Déclarez les clés primaires, les clés étrangères et les contraintes uniques dans le cadre de la clause de spécification de table lors de la création de la table :

CREATE TABLE T(pk1 INTEGER NOT NULL, pk2 INTEGER NOT NULL,
                CONSTRAINT t_pk PRIMARY KEY(pk1, pk2));
CREATE TABLE S(pk INTEGER NOT NULL PRIMARY KEY,
                fk1 INTEGER, fk2 INTEGER,
                CONSTRAINT s_t_fk FOREIGN KEY(fk1, fk2) REFERENCES T);
CREATE TABLE U(id INTEGER NOT NULL, email STRING NOT NULL,
                CONSTRAINT u_uq_email UNIQUE(email));

Les instructions CTAS ne prennent pas en charge cette clause de contrainte.

Ajouter à des tables existantes

Pour ajouter des contraintes à des tables existantes :

ALTER TABLE T ADD CONSTRAINT t_pk PRIMARY KEY(pk1, pk2);
ALTER TABLE S ADD CONSTRAINT s_t_fk FOREIGN KEY(fk1, fk2) REFERENCES T;
ALTER TABLE U ADD CONSTRAINT u_uq_email UNIQUE(email);

Ressources supplémentaires

• ALTER TABLE
• ADD CONSTRAINT
• DROP CONSTRAINT
• Clause CONSTRAINT