cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
cancel
Showing results for 
Search instead for 
Did you mean: 

Referential Integrity (Primary Key / Foreign Key Constraint) - Azure Databricks SQL

AbhishekBreeks
New Contributor II

Hello,

Please suggest how can we implement Referential Integrity (Primary Key / Foreign Key Constraint) - between different tables defined on Azure Databricks Database.

Basically the syntax to add Primary and Foreign Key constraint in the table definitions.

Thanks in advance for your help.

Regards,

Abhishek

9 REPLIES 9

franco_patano
New Contributor III
New Contributor III

You can use constraints: https://docs.microsoft.com/en-us/azure/databricks/delta/delta-constraints

Franco Patano
Stragetic Data and AI Advisor

-werners-
Esteemed Contributor III

according to the docs delta only has not-null and check constraints.

PK/FK constraint support is not mentioned.

Also:

https://issues.apache.org/jira/browse/SPARK-19842

So I'm afraid that it is not yet possible.

Ruby8376
Contributor III

@Werner Stinckens​ Hii! I just posted similar question for my project..Can we do pk/fk constraints on delta tables now?

-werners-
Esteemed Contributor III

nothing has changed so far.

Is it sad that I was excited to see that in preview, lol

The links indicate it would be just informational, so not enforced?

image

If you are using Delta Live Table pipelines, the pipeline will check dependencies via the table_constraint using the primary and foreign key definitions and use that to update tables in the proper order to ensure referential integrity. So though not strongly enforced for performance reasons you still get the main benefit of keys when using pipelines. @Qi Su​ correct me if I'm off here.

elgeo
Valued Contributor II

Is there any alternate way you could suggest to implement and enforce primary key constraint?

Welcome to Databricks Community: Lets learn, network and celebrate together

Join our fast-growing data practitioner and expert community of 80K+ members, ready to discover, help and collaborate together while making meaningful connections. 

Click here to register and join today! 

Engage in exciting technical discussions, join a group with your peers and meet our Featured Members.