How to enforce delta table column to have unique values?

[Mado]

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:

 

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?

[Ajay-Pandey · Accepted solution]

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.

[darthdickhead]

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?

[Steve_Lyle_BPCS]

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.

[SibbirSihan]

CREATE TABLE table_name (
id_col1 BIGINT GENERATED ALWAYS AS IDENTITY,
id_col2 BIGINT GENERATED ALWAYS AS IDENTITY (START WITH -1 INCREMENT BY 1),
id_col3 BIGINT GENERATED BY DEFAULT AS IDENTITY,
id_col4 BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH -1 INCREMENT BY 1)
)

This is the only way you can create an identity column in sparkSQL for delta lake table.