Does anyone have an idea about databricks delta tables in unity catalog supporting primary and foreign key constraints by enforcing them and not just declaring for informational purposes?
I'm trying to create delta tables in unity with foreign key constraints and need to validate the data put into those tables for consistency. As of today, could not find any supporting documentation or examples of this on databricks or anywhere else on how to implement these constraints and not just declare them to show on table description.
I've tried creating the tables with constraints defined but they do not work. For example, a table created with primary key constraint allow insertion of duplicate values in the defined column. Same with foreign key reference which allows insertion of records which are absent in the referencing column.
This functionality doesn't exist yet - as you mentioned right now primary & foreign keys are primarily for the information purpose, and it's really hard problem for distributed/big data systems. You may implement some workarounds for example by implementing SCD Type 1 functionality using Delta Live Tables, validate data before inserting, etc.