Skip to content
Contact us Go to Customer Portal
  • There are no suggestions because the search field is empty.

Use Unique Constraints (Project)

Applies to
Platform: Databricks · Databricks dbt

Use Unique Constraints (UseUniqueConstraints) decides whether the generated Databricks tables declare unique constraints. Use it to describe business-key uniqueness in Unity Catalog.

Available since Generator 2.4.

Values and default

Set the value in the Implementation group of the Additional Settings in Project Settings:

  • true (default): tables declare unique constraints.
  • false: tables declare primary keys only.

Prerequisites

  • On Databricks, Use Unity Catalog is true. Without Unity Catalog, the tables declare no constraints at all.
  • The configuration describes unique constraints as a preview feature of Databricks SQL and Databricks Runtime 18.2 that does not work on lower versions. See the Databricks documentation CONSTRAINT clause.

Databricks supports UNIQUE table constraints in Databricks SQL and Databricks Runtime 18.2 and above (CONSTRAINT clause). On a lower Databricks Runtime, set Use Unique Constraints to false.

Effect on generated output

With true, a table with business keys, for example a Hub, gets a constraint uc_<table name> on its business-key columns. Databricks dbt declares the same constraints with type: unique in the model YAML files. With false, the unique constraints are left out. Primary keys and the foreign keys of Foreign Key Mode stay unchanged.

Example

A Hub with the business keys month and day on Databricks with Unity Catalog. With true, the deployment notebook creates:

CREATE OR REPLACE TABLE `<catalog>`.`<schema>`.`rdv_hub_example` (
     `bg_loadtimestamp` Timestamp NOT NULL
    ,`hub_hk` Binary NOT NULL
    ,`bg_sourcesystem` String
    ,`month` Decimal(38,0)
    ,`day` Decimal(38,0)
    ,CONSTRAINT `pk_rdv_hub_example` PRIMARY KEY (`hub_hk`)
    ,CONSTRAINT `uc_rdv_hub_example` UNIQUE (`month`, `day`)
)
USING delta

With false, the line with UNIQUE is missing. <catalog> and <schema> stand for the replaced placeholder values.

Related articles