11.3 Constraint Enforcement & Relationships in Unity Catalog

Databricks Certified Data Analyst Associate Exam

Track your progress as you read

Finished sections get a check mark.

1 Platform Overview & Unity Catalog Foundations

1.1 Databricks Architecture & Compute Concepts 1.2 Databricks Marketplace & Data Sharing Basics 1.3 Unity Catalog Namespace & Object Hierarchy 1.4 Governed Asset Discovery & System Tables

2 Managing & Cleaning Governed Data

2.1 Dataset Discovery & Asset Tagging 2.2 Lineage Inspection & Data Traceability 2.3 SQL Data Cleaning & Transformation Techniques

3 Data Ingestion & Import Patterns

3.1 Workspace Uploads & Cloud File Ingestion 3.2 Incremental File Ingestion with Auto Loader 3.3 Ingesting Data via Delta Sharing & External Connections

4 Databricks SQL Warehouses & Core Querying

4.1 Databricks SQL Interface & SQL Assistant 4.2 SQL Warehouses Sizing & Management 4.3 Table Creation & Data Modification in Databricks SQL

5 Advanced SQL Authoring & Time Travel

5.1 Advanced Aggregations, Window Functions & Analytics 5.2 Complex Joins, CTEs & Set Operations 5.3 Views, Materialized Views & Delta Time Travel

6 Query Performance Analysis & Photon Engine

6.1 Photon Engine Architecture & Acceleration 6.2 Query History & Execution Profile Inspection 6.3 Delta Lake Audit History & Commit Logs

7 Liquid Clustering, Caching & Performance Troubleshooting

7.1 Caching Hierarchy in Databricks SQL 7.2 Data Layout Optimization: Liquid Clustering vs Partitioning & Z-Ordering 7.3 Troubleshooting Slow & Failing Queries

8 AI/BI Dashboards & Visualization Design

8.1 AI/BI Dashboards Overview & Canvas Layout 8.2 Chart Types & Visualization Selection 8.3 Notebook & SQL Editor Visualizations

9 Dashboard Interactivity, Publishing & Alerts

9.1 Dashboard Parameters & Interactive Filtering 9.2 Publishing, Sharing & Access Control 9.3 Scheduled Refresh, Email Subscriptions & Alerts

10 Developing & Maintaining AI/BI Genie Spaces

10.1 AI/BI Genie Architecture & Space Setup 10.2 Curation via Instructions, Descriptions & Sample Questions 10.3 Trusted Assets & Parameterized Functions 10.4 Monitoring, Benchmarks, Feedback & Maintenance

11 Data Modeling & Medallion Architecture

11.1 Dimensional Modeling Fundamentals 11.2 Medallion Architecture for Analytics 11.3 Constraint Enforcement & Relationships in Unity Catalog

12 Securing Data & Unity Catalog Governance

12.1 Unity Catalog Privilege Model & Grants 12.2 Ownership, Inheritance & Access Control Patterns 12.3 Fine-Grained Security: Row Filters & Column Masks

11.3 Constraint Enforcement & Relationships in Unity Catalog

Key Takeaways Explain This Topic AI

Unity Catalog supports NOT NULL, CHECK, PRIMARY KEY, and FOREIGN KEY constraints on Delta Lake tables.

NOT NULL and CHECK constraints are actively enforced on write operations, aborting transactions immediately if an inserted or updated row violates the constraint condition.

PRIMARY KEY and FOREIGN KEY constraints in Unity Catalog are informational (not enforced on write), but provide vital semantic metadata to the Databricks SQL query optimizer and AI/BI tools.

Adding a CHECK constraint using ALTER TABLE ADD CONSTRAINT validates incoming data batches during execution, acting as an automated data quality gatekeeper.

Last updated: July 2026

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:

Operating as an active data quality gatekeeper on data write operations.

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.

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.

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?

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.

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.

Documentation & Entity-Relationship Diagrams (ERD) : Data catalog tools and Unity Catalog UI automatically render visual ERD diagrams showing primary-foreign key links across catalogs.

Constraint Type SQL Syntax Keywords Enforced on Write? Primary Purpose & Impact

NOT NULL ALTER COLUMN col SET NOT NULL YES (Aborts transaction on violation) Guarantees required attributes exist; prevents null reference exceptions

CHECK ADD CONSTRAINT name CHECK (expr) YES (Aborts transaction on violation) Enforces field-level business logic and valid data ranges

PRIMARY KEY CONSTRAINT pk PRIMARY KEY (col) NO (Informational only) Identifies entity unique key; enables query join elimination & ERDs

FOREIGN KEY FOREIGN KEY (col) REFERENCES parent(col) NO (Informational only) Defines relational links across tables; powers AI/BI Genie joins

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

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.

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.

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.