Declare and optimize with UNIQUE constraints on Databricks | Databricks Blog
Skip to main content
Databricks UNIQUE constraints let teams declare multiple nullable or composite uniqueness guarantees beyond a table’s single PRIMARY KEY, and reference them with foreign keys.
Validate and continuously maintain these informational constraints before using RELY, which enables the optimizer to eliminate redundant joins and DISTINCT aggregations.
In 10 TB TPC-DS benchmarks, UNIQUE with RELY removed over 99.99% of shuffle and delivered up to 12× faster queries.
Data teams often maintain uniqueness guarantees in their pipelines, but without a flexible way to represent all of them in the table schema, users and tools cannot discover them and the query optimizer cannot safely leverage them. Business entities often have more than one value, or combination of values, known to be unique: a users table might use user_id as its primary row identifier while its data model also records separate uniqueness guarantees for username, an email address, and (workspace_id, external_user_id).
Databricks already supports PRIMARY KEY and FOREIGN KEY constraints: a primary key identifies a table's rows, and foreign keys can reference it to describe relationships. A table can have only one primary key, however, and its columns must be non-nullable. UNIQUE constraints, now available in Public Preview for Unity Catalog Delta tables in Databricks SQL and Databricks Runtime 18.2 and above, add a way to record the other uniqueness guarantees. A table can declare multiple UNIQUE constraints, their columns can be nullable, and foreign keys can reference them. After you validate these constraints and mark them with RELY, the query optimizer can also use them to eliminate unnecessary joins and aggregations.
When one primary key can't cover every unique column
PRIMARY KEY and UNIQUE both record uniqueness, but they serve different schema roles:
CapabilityPRIMARY KEYUNIQUE
Schema rolePrimary row identifierAdditional uniqueness guarantee
Constraints per tableOneMultiple
Nullable columnsNoYes
Put your pipeline’s uniqueness guarantees in the schema
The following table has a primary key and two UNIQUE constraints:
The two unique constraints illustrate different kinds of uniqueness. users_external_uq applies to a column combination: each (workspace_id, external_user_id) pair must be unique even though either value may appear in other pairs. users_optional_id_uq applies to a nullable column. The following rows show which values satisfy that guarantee:
user_idoptional_identifierSatisfies UNIQUE (optional_identifier)?
1email:[email protected]
2NULLYes
3NULLYes
4email:[email protected]
5email:[email protected] - duplicates row 1
Record the guarantee without paying a cost on every write
Like primary and foreign key constraints on Databricks, UNIQUE constraints are informational and are not enforced when data is written. Declaring UNIQUE (optional_identifier), for example, does not cause a write containing duplicate non-null values to fail.
This separation lets the schema record a uniqueness guarantee maintained by an ingestion pipeline, an upstream system, or another data-quality process. The guarantee also becomes table metadata that users, tools, and applications can inspect.
Reference a UNIQUE constraint from a foreign key
A foreign key can reference a UNIQUE constraint in addition to a primary key. For example, the composite UNIQUE constraint declared above can be referenced as follows:
The referenced parent columns identify which unique constraint the foreign key targets. Child and parent columns correspond by position: workspace_id maps to workspace_id, and source_user_id maps to external_user_id. Their names do not need to match, but their types and positions must correspond. The REFERENCES (parent) columns must be listed in the same order as the columns in the UNIQUE constraint's definition; otherwise the constraint is still valid but join elimination silently won't fire.
To reference a single-column UNIQUE constraint, explicitly name its parent column, for example, REFERENCES users (username). If the parent column list is omitted, REFERENCES users targets the table’s primary key instead.
Optimize queries with RELY
By default, a unique constraint is created with NORELY. It records the intended schema semantics, but Databricks does not use it for correctness-dependent query rewrites. The following statement creates a constraint with this default:
After you have verified the data and ensured that your pipelines will continue to maintain the guarantee, you can drop and re-create the constraint with RELY:
In this example, CASCADE also drops any foreign keys that reference users_username_uq, and they must be re-created after the unique constraint is added back. Without CASCADE, DROP CONSTRAINT uses the default RESTRICT behavior and is rejected when dependent foreign keys exist.
A foreign key that references the unique constraint can also be declared with RELY:
RELY is set independently on each constraint. It does not validate existing data or enforce the constraint on future writes. It allows Databricks to assume that the constraint is valid when optimizing queries. If the data violates a relied-upon constraint, a rewrite based on that constraint can produce incorrect results. You should therefore use RELY only for guarantees that are validated and continuously maintained.
With RELY, unique constraints can provide the information needed for eligible join and aggregation rewrites. Eligibility also depends on other guarantees required by the query, including nullability, as the examples below illustrate. This optimization support is available on Photon-enabled compute. The primary and foreign key blog describes constraint-based join and aggregation optimizations that UNIQUE constraints can also enable. Since that post was published, RELY support has become available for foreign keys and now for UNIQUE constraints.
Eliminate joins using UNIQUE and foreign key constraints
Some dashboards built on the same data need attributes from users, so teams often create a shared view that always joins user_events with users. A dashboard that needs only information from user_events may still inherit that join from the view. Its generated SQL can therefore look like the query below, even though it does not select any columns from users:
Although the query returns no columns from users, the optimizer cannot normally remove the join. An inner join could remove an event if no matching user exists, or duplicate it if multiple users have the same username.
The relied-upon constraints rule out both possibilities. The UNIQUE constraint guarantees at most one matching user, while the foreign key guarantees that a match exists. Because user_events.username is also NOT NULL, every event has exactly one match. The join therefore cannot change the result, and the optimizer can read event_id directly from user_events.
The earlier primary and foreign key blog demonstrates the related LEFT OUTER JOIN case, which needs only the at-most-one guarantee. This INNER JOIN example also needs the foreign key to establish that a match exists.
With NORELY constraints, the optimized plan retains the join:
When both constraints use RELY, the optimizer can know that every event has exactly one matching user and remove the join:
In this example, user_events.username is NOT NULL, but the optimization also applies when it is nullable: Databricks will remove the join and add an IS NOT NULL filter on the user_events.username to preserve the inner join's handling of nulls.
We benchmarked this optimization on the TPC-DS SF10000 dataset, which represents a 10 TB scale factor. On the 28.8-billion-row store_sales table joined to the 65-million-row customer table, we declared customer.c_customer_sk as UNIQUE and defined a foreign key from store_sales.ss_customer_sk. With RELY on both constraints, Databricks eliminated the join, removed more than 99.99% of shuffle, and reduced median engine execution time from 23.6 seconds to 3.0 seconds - a 7.9x speedup.
Skip DISTINCT when values are already unique
BI tools and query builders often add DISTINCT defensively because joins may produce duplicate rows. When the selected values are already unique, DISTINCT removes nothing, but it can still require an aggregation and shuffle unless the optimizer can prove that the operation is unnecessary. A relied-upon UNIQUE constraint can provide that proof.
Consider a user_identifiers table with these two constraints:
UNIQUE (user_id) RELY, where user_id is NOT NULL
UNIQUE (optional_identifier) RELY, where optional_identifier is nullable
The table contains the following data, which satisfies both constraints:
For user_id, the optimizer can use the uniqueness and non-null guarantees together:
The optimized plan contains no aggregation:
The aggregation is gone because the relied-upon constraint establishes that every user_id is already distinct.
The equivalent query over optional_identifier shows why the optimizer also considers nullability:
The optimized plan retains the aggregation:
Both constraints use RELY, but the nullable constraint permits the two NULL rows shown above. SQL DISTINCT must collapse those rows into one, so the aggregation is still necessary.
Nullable UNIQUE constraints can still enable the optimization when the query supplies the missing non-null guarantee:
The optimizer can remove the aggregation:
The filter removes the values that may repeat under nullable UNIQUE semantics. The remaining values are guaranteed to be distinct, so Databricks can again avoid the aggregation.
We benchmarked this optimization on the same TPC-DS SF10000 dataset, which represents a 10 TB scale factor. On the 28.8-billion-row store_sales table, we declared (ss_item_sk, ss_ticket_number) as a composite UNIQUE constraint and ran a DISTINCT query over those columns. With RELY, Databricks eliminated the DISTINCT aggregation, removed more than 99.99% of shuffle, and reduced median engine execution time from 33.7 seconds to 2.8 seconds - a 12.0x speedup.
Validate your data before optimizer uses a constraint
Find duplicate and orphaned rows
Before using RELY, check that the data satisfies the guarantee. For a nullable UNIQUE constraint, exclude rows containing NULL because UNIQUE permits those rows to repeat. This query finds duplicate non-null identifiers:
For a composite UNIQUE constraint, group by every constrained column and add an IS NOT NULL condition for each nullable column. You can also find child rows with no matching parent by using a left anti join:
These checks validate the current data. Your ingestion or data-quality pipeline must continue to maintain the guarantees after you enable RELY.
Confirm the declared constraints
After declaring or re-creating a constraint, you can confirm its definition in several ways:
SHOW CREATE TABLE
DESCRIBE TABLE EXTENDED
Unity Catalog INFORMATION_SCHEMA relations:
TABLE_CONSTRAINTS
KEY_COLUMN_USAGE
CONSTRAINT_TABLE_USAGE
CONSTRAINT_COLUMN_USAGE
REFERENTIAL_CONSTRAINTS
See Constraints on Databricks and the CONSTRAINT clause for complete create, alter, inspect, and drop syntax.
Roll out UNIQUE and RELY incrementally
You can adopt UNIQUE incrementally:
Declare existing uniqueness guarantees with the default NORELY.
Reference unique keys from foreign keys where the relationship is part of the schema.
Validate and continuously maintain uniqueness and referential integrity in your data pipelines.
Use RELY on the UNIQUE constraint only after verifying uniqueness, and on the foreign key only after verifying that every applicable child value has a matching parent row.
Compare plans with EXPLAIN to confirm eligible rewrites.
Conclusion
UNIQUE constraints let a table record multiple uniqueness guarantees in addition to its single, non-nullable PRIMARY KEY, and the constrained columns can be nullable. Foreign keys can reference a UNIQUE constraint as well as a primary key. UNIQUE constraints are informational by default; after validation, RELY lets Databricks use them to optimize eligible joins and DISTINCT queries.
Related tags:
Data Warehousing
Get the latest posts in your inbox
Subscribe to our blog and get the latest posts delivered to your inbox.
Sign up
View all blogs