topic Re: Referential Integrity (Primary Key / Foreign Key Constraint) - Azure Databricks SQL in Data Engineering

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

AbhishekBreeks — Wed, 28 Jul 2021 14:32:02 GMT

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

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

franco_patano — Thu, 02 Sep 2021 19:58:46 GMT

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

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

-werners- — Fri, 03 Sep 2021 13:21:46 GMT

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.

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

Ruby8376 — Tue, 24 May 2022 17:55:54 GMT

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

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

-werners- — Wed, 25 May 2022 06:22:19 GMT

nothing has changed so far.

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

Dr_G-Guillermo — Mon, 15 Aug 2022 08:29:40 GMT

We have this in preview

Azure: https://docs.microsoft.com/en-us/azure/databricks/spark/latest/spark-sql/language-manual/sql-ref-syntax-ddl-create-table-constraint

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

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

dosborne — Tue, 16 Aug 2022 04:31:40 GMT

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

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

TheOptimizer — Fri, 14 Oct 2022 18:12:45 GMT

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

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

elgeo — Mon, 17 Oct 2022 08:13:04 GMT

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

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

Justin_Stuparit — Wed, 26 Oct 2022 20:06:09 GMT

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.