Databricks speeds up queries with UNIQUE constraints

Multiple uniqueness guarantees in one table

According to Databricks’ announcement on November 3, 2026, UNIQUE constraints are available in Public Preview for Unity Catalog Delta tables, in Databricks SQL and in Databricks Runtime 18.2 or later. The feature records in a table’s schema the uniqueness guarantees maintained by data-loading processes, source systems or other data-quality procedures.

A PRIMARY KEY is a table’s primary row identifier, and only one can be defined for a table. Its columns cannot contain NULL values. UNIQUE, by contrast, describes additional uniqueness guarantees. Multiple UNIQUE constraints can be declared within a table, and the affected columns may allow NULL values.

For example, a users table could use the user_id column as its primary key while also requiring username, email, and the combination of workspace_id and external_user_id to be unique. A composite UNIQUE constraint means that the combination of the two columns cannot be repeated, even if the individual values can appear in multiple pairs.

Constraints must be checked separately

Like PRIMARY KEY and FOREIGN KEY constraints, UNIQUE constraints are informational. Databricks does not enforce them when writing data, so a data load containing a duplicate, non-NULL value will not automatically fail. In return, the schema preserves the uniqueness expectation, which users, tools and applications can also discover.

A FOREIGN KEY can also reference a UNIQUE constraint. For a composite key, the referenced parent columns must be listed in the same order as in the UNIQUE definition. The column names may differ, but the order and data types must match. If the parent columns are not specified, the reference points to the table’s PRIMARY KEY by default.

The default state of a constraint is NORELY. In this state, Databricks records the meaning of the schema but does not use it for rewrites that depend on query correctness. Databricks recommends enabling RELY only after the data has been checked and continuous maintenance of the guarantee has been ensured.

RELY can reduce the cost of joins

RELY does not check existing data or restrict future writes. It allows the optimizer to assume that the constraint is valid. If the data nevertheless violates a guarantee marked with RELY, a query rewrite based on it may return incorrect results. The optimization is available on Photon-compatible compute resources.

Databricks also presents an example in which a shared view joins the user_events and users tables, while a particular dashboard needs data only from user_events. Together, UNIQUE and FOREIGN KEY can guarantee that each event has at most one matching user row and that the relationship exists. In this case, the optimizer can eliminate the unnecessary JOIN operation.

The test used the TPC-DS SF10000 dataset, representing a 10-terabyte scale, and examined the 28.8-billion-row store_sales table and the 65-million-row customer table. With the constraints marked RELY, Databricks removed the join, reduced the amount of shuffled data by more than 99.99 percent, and cut median execution time from 23.6 seconds to 3.0 seconds, a 7.9-fold speedup.

DISTINCT may also be eliminated

BI tools and query builders often add DISTINCT to queries as a safeguard because a JOIN can produce duplicate rows. If the selected values are already unique, DISTINCT removes nothing, but it may still require aggregation and data shuffling. A UNIQUE constraint marked with RELY can prove that it is unnecessary.

In Databricks’ example, user_id in the user_identifiers table is non-NULL and UNIQUE, while optional_identifier is a UNIQUE column that also allows NULL values. The optimizer can use the uniqueness of user_id together with the non-NULL guarantee and therefore eliminate the aggregation. According to the company’s 10-terabyte TPC-DS measurements, using UNIQUE and RELY reduced data shuffling by more than 99.99 percent and allowed queries to run up to 12 times faster.

The feature could therefore be useful to teams that already maintain uniqueness rules in their data pipelines but have so far been unable to record them in their table schemas. The performance benefit depends on checking the declared guarantees in advance and then maintaining them continuously.