Contraintes sur Databricks
Databricks prend en charge les clauses standard de gestion des contraintes SQL :
- Les contraintes appliquées vérifient l'intégrité des données avant d'ajouter des lignes à une table.
- Les contraintes informatives (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 Databricks nécessitent Delta Lake.
Pour un concept connexe dans les LakeFlow Pipelines, consultez Gérer la qualité des données avec les attentes de pipeline.
Contraintes appliquées
Lorsqu'une contrainte est violée, la transaction échoue avec une erreur. Databricks prend en charge deux types de contraintes :
NOT NULL: indique que les valeurs dans des colonnes spécifiques ne peuvent pas être nulles.CHECK: indique qu'une expression booléenne spécifiée doit être vraie pour chaque ligne d'entrée.
L'ajout d'une contrainte met à niveau le protocole d'écriture de table si la version actuelle du protocole d'écriture est inférieure à 3. Cela pourrait affecter la compatibilité avec les clients Delta Lake externes. Voir Compatibilité des fonctionnalités et protocoles de Delta Lake.
NOT NULL contrainte
Lorsque vous créez une table, spécifiez NOT NULL contraintes dans le schéma. Pour supprimer ou ajouter 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;
Databricks vérifie que toutes les lignes existantes satisfont à 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 être non nul. Les colonnes imbriquées dans des types de tableau ou de carte n'acceptent pas les contraintes NOT NULL.
Voir CREATE TABLE [USING] et ALTER TABLE ALTER COLUMN.
CHECK contrainte
Gérez CHECK contraintes avec les commandes ALTER TABLE ADD CONSTRAINT et ALTER TABLE DROP CONSTRAINT. ALTER TABLE ADD CONSTRAINT vérifie que toutes les lignes existantes satisfont la contrainte avant d'ajouter la contrainte à la table.
Les restrictions suivantes s'appliquent aux contraintes de vérification :
- Une expression de contrainte
CHECKpeut utiliser n'importe quelle fonction SQL de Spark qui renvoie toujours le même résultat lorsque les mêmes valeurs d'argument sont fournies, à l'exception des types de fonctions suivants :- Fonctions définies par l'utilisateur.
- Fonctions d’agrégation.
- Fonctions de fenêtre.
- Fonctions renvoyant 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.
Affichez les propriétés de la table de contrainte de vérification
Utilisez les commandes DESCRIBE DETAIL et SHOW TBLPROPERTIES pour afficher les contraintes CHECK d'une table.
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 supérieures, utilisez la commande DROP FEATURE pour supprimer les contraintes de vérification d'une table et rétablir le protocole de table.
Consultez Supprimer une fonctionnalité de table Delta Lake et rétrograder le protocole de table.
Déclarer les clés primaires, les clés étrangères et les contraintes uniques
Les contraintes de clé primaire, de clé étrangère et d'unicité sont uniquement informatives et ne sont pas appliquées. Ils pourraient améliorer les performances grâce à des optimisations de query.
- Clé primaire et clé étrangère : disponibles 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 versions ultérieures. Les clés étrangères doivent faire référence à une clé primaire ou à une contrainte d'unicité dans une autre table.
- Unique : disponible en Aperçu public pour les tables Unity Catalog et Delta Lake dans Databricks SQL et Databricks Runtime 18.2 et versions supérieures. Une table peut avoir plusieurs contraintes uniques. Les clés étrangères peuvent référencer une colonne unique à l'aide de
REFERENCES parent_table(unique_col). Les colonnes uniques peuvent être nullables car les valeursNULLsont traitées comme distinctes les unes des autres.
Query information_schema, utilisez DESCRIBE TABLE EXTENDED ou utilisez SHOW CREATE TABLE pour obtenir des détails sur la manière dont les contraintes sont appliquées dans un catalogue donné.
Ajouter aux nouvelles tables
Déclarez les clés primaires, les clés étrangères et les contraintes d'unicité dans la clause de spécification de la 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 aux tables existantes
Pour ajouter des contraintes aux 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);