cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

How to enforce delta table column to have unique values?

Mado
Valued Contributor II

Hi,

I have defined a delta table with a primary key:

%sql
 
CREATE TABLE IF NOT EXISTS test_table_pk (
  table_name STRING NOT NULL,
  label STRING NOT NULL,
  table_location STRING NOT NULL,
 
  CONSTRAINT test_table_pk_col PRIMARY KEY(table_name)  
  
  ) USING DELTA
LOCATION "abfss://raw@Table_Path"

I want column "table_name" to be unique. However, I can insert rows with the same "table_name" as below:

%sql
 
INSERT INTO test_table_pk 
VALUES ('table_2', 'label_2', 'path_2'), 
       ('table_2', 'label_2', 'path_3');

In the table:

image 

I tried to add a unique constraint for column "table_name": 

"CONSTRAINT test_table_unique_col UNIQUE(table_name)

but I get the error:

Only PRIMARY KEY and FOREIGN KEY constraints are currently supported

How can I add a constraint to the column "table_name" to accept only unique values?

1 ACCEPTED SOLUTION

Accepted Solutions

Ajay-Pandey
Esteemed Contributor III

Hi @Mohammad Saber​ ,

For now, there is no way to enforce delta table column to have unique values.

You can use primary key and foreign key relationships on fields in Unity Catalog tables. Primary and foreign keys are informational only and are not enforced. Foreign keys must reference a primary key in another table.

Ajay Kumar Pandey

View solution in original post

3 REPLIES 3

Ajay-Pandey
Esteemed Contributor III

Hi @Mohammad Saber​ ,

For now, there is no way to enforce delta table column to have unique values.

You can use primary key and foreign key relationships on fields in Unity Catalog tables. Primary and foreign keys are informational only and are not enforced. Foreign keys must reference a primary key in another table.

Ajay Kumar Pandey

This doesn't make much sense, as the role of a primary key is to have a unique constraint. Is there something on the roadmap to have unique constraints for primary keys in the future? Also how can the current PK constraints be used in any way?

SteveL
New Contributor II

I'm with you.  But it DOES make sense because DBx databases are not application databases.  DBx is not intended to be used like this.  DBx databases are repositories for any ingested abstract data.  To manage the ingestion is purpose-built databases architecture implies using an external DBMS such as AzureDB.
For instance, you would build a point of sale (POS) system to run on DBx.  But you would put your data analytics of the POS on DBx. 
All that said there isn't anything that prevents you from using a MERGE query, or writing additional query code, to guaranteeing uniqueness.

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you won’t want to miss the chance to attend and share knowledge.

If there isn’t a group near you, start one and help create a community that brings people together.

Request a New Group