11.3 Constraint Enforcement & Relationships in Unity Catalog

Overview of Governance & Integrity in Unity Catalog

In modern cloud lakehouses, ensuring data integrity, schema consistency, and clear relational context is essential for enterprise governance. Unity Catalog—the unified governance layer for data and AI on Databricks—provides centralized management of catalog metadata, table definitions, security privileges, and structural constraints.

When building analytical models on Delta Lake, defining explicit constraints and table relationships within Unity Catalog serves two vital purposes:

1. Operating as an active data quality gatekeeper on data write operations.
2. Providing rich semantic metadata to the Databricks SQL query optimizer (for join elimination and execution plan optimization) and to AI/BI tools (such as AI/BI Genie) for automatic relationship discovery.

Enforced Constraints: NOT NULL and CHECK

Unity Catalog and Delta Lake support two types of constraints that are actively enforced on write operations: NOT NULL constraints and CHECK constraints. If a batch or stream attempts to write data that violates these constraints, the transaction immediately fails and raises a runtime error, preventing corrupt data from contaminating the table.

1. NOT NULL Constraints: Guarantees that a specified column cannot contain NULL values. This is critical for primary keys, foreign keys, event timestamps, and mandatory business attributes.
2. CHECK Constraints: Asserts a boolean SQL expression that every row in the table must satisfy. If an inserted or updated row causes the expression to evaluate to FALSE, the transaction aborts.

Common examples of CHECK constraint expressions include:

• Validating non-negative monetary amounts: CHECK (unit_price > 0)
• Restricting values to an allowed set: CHECK (status IN ('PENDING', 'SHIPPED', 'DELIVERED', 'CANCELLED'))
• Validating date ranges: CHECK (ship_date >= order_date)

-- Adding Enforced Constraints to an Existing Delta Table
ALTER TABLE main.finance.invoices 
ALTER COLUMN invoice_id SET NOT NULL;

ALTER TABLE main.finance.invoices 
ADD CONSTRAINT check_invoice_amount CHECK (total_amount >= 0.00);

ALTER TABLE main.finance.invoices 
ADD CONSTRAINT check_status_validity CHECK (status IN ('DRAFT', 'ISSUED', 'PAID', 'VOID'));

Informational Constraints: PRIMARY KEY and FOREIGN KEY

Unlike traditional relational database management systems (RDBMS) like PostgreSQL or SQL Server, Unity Catalog supports PRIMARY KEY and FOREIGN KEY constraints as Informational Constraints.

An informational constraint means that Unity Catalog records and maintains the relational metadata in the catalog schema, but Delta Lake does NOT enforce uniqueness or referential integrity during write operations. For instance, inserting a duplicate primary key value or an orphaned foreign key value will not trigger a runtime error during INSERT or MERGE operations.

Why use Informational Constraints if they are not enforced on write?

1. Query Optimizer Acceleration (Join Elimination): The Databricks SQL query optimizer leverages primary and foreign key metadata to streamline execution plans. For example, if a query joins a fact table to a dimension table to retrieve a column that is guaranteed unique via a primary key constraint, the optimizer can perform join elimination if the dimension columns are ultimately unused in the projection list.
2. AI/BI Genie & Dashboard Intelligence: AI/BI Genie spaces analyze Unity Catalog foreign key relationships to automatically infer how tables should be joined when natural language questions are asked by business users, eliminating manual semantic mapping.
3. Documentation & Entity-Relationship Diagrams (ERD): Data catalog tools and Unity Catalog UI automatically render visual ERD diagrams showing primary-foreign key links across catalogs.

How Query Optimizers & AI/BI Use Relationships

When primary and foreign key constraints are declared in Unity Catalog, the Databricks SQL query compiler evaluates these constraints during query optimization.

Consider a query that joins orders with customers:

SELECT o.order_id, o.order_date, o.total_amount
FROM main.gold.fact_orders o
JOIN main.gold.dim_customers c ON o.customer_id = c.customer_id;

If dim_customers.customer_id is declared as a PRIMARY KEY and fact_orders.customer_id is declared as a FOREIGN KEY REFERENCES dim_customers(customer_id), the query optimizer recognizes that joining with dim_customers will neither multiply rows nor drop matching rows. Because no columns from dim_customers appear in the SELECT list, the optimizer eliminates the JOIN operation altogether! This eliminates unnecessary table scans and shuffle operations, resulting in dramatic query speedups.

DDL Syntax & Constraint Management in Databricks SQL

Primary and foreign key constraints can be declared during table creation (CREATE TABLE) or added later using ALTER TABLE.

-- Creating Tables with Primary and Foreign Key Constraints
CREATE TABLE main.gold.dim_store (
    store_id INT NOT NULL,
    store_name STRING,
    region STRING,
    CONSTRAINT pk_dim_store PRIMARY KEY (store_id)
) USING DELTA;

CREATE TABLE main.gold.fact_daily_sales (
    sales_id BIGINT NOT NULL,
    store_id INT NOT NULL,
    sales_date DATE,
    revenue DECIMAL(12,2),
    CONSTRAINT pk_fact_sales PRIMARY KEY (sales_id),
    CONSTRAINT fk_sales_store FOREIGN KEY (store_id) REFERENCES main.gold.dim_store(store_id)
) USING DELTA;

To drop constraints when data structures evolve:

-- Dropping Constraints in Databricks SQL
ALTER TABLE main.finance.invoices DROP CONSTRAINT check_invoice_amount;
ALTER TABLE main.finance.invoices ALTER COLUMN invoice_id DROP NOT NULL;
ALTER TABLE main.gold.fact_daily_sales DROP CONSTRAINT fk_sales_store;

Practical Scenarios & Best Practices

1. Combine CDC Merge Logic with Informational Constraints: Because foreign keys are informational, ensure that Silver and Gold ingestion pipelines (e.g., via MERGE INTO or Delta Live Tables) perform deduplication upstream to maintain true primary key uniqueness.
2. Enforce Critical Rules with CHECK Constraints: Place CHECK constraints on Silver tables to trap invalid data early in the Medallion pipeline before corrupt values propagate to Gold reporting.
3. Explicitly Declare PK/FK Relationships for AI/BI: Always declare primary and foreign key constraints on Gold star schemas so AI/BI Genie spaces can correctly join tables without requiring extensive custom SQL queries.