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

[AbhishekBreeks]

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

[franco_patano]

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

[-werners-]

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]

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

[-werners-]

nothing has changed so far.

[Dr_G-Guillermo]

We have this in preview

Azure: https://docs.microsoft.com/en-us/azure/databricks/spark/latest/spark-sql/language-manual/sql-ref-syn...

AWS : https://docs.databricks.com/spark/latest/spark-sql/language-manual/sql-ref-syntax-ddl-create-table-c...

[dosborne]

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

[TheOptimizer]

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

[Justin_Stuparit]

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]

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