Unity Catalog Constraints

Unity Catalog Primary Key and Foreign Key Constraints

Overview

Unity Catalog supports informational constraints (NOT ENFORCED) that enrich metadata, improve query optimization, and enable BI tool relationship discovery. This skill standardizes PK/FK implementation for star schema dimensional models following Kimball dimensional modeling best practices.

Core Principle: Surrogate keys are PRIMARY KEYS. Facts reference surrogate PKs via FOREIGN KEY constraints. Business keys have UNIQUE constraints for alternate lookups.

When to Use This Skill

Use this skill when:

• Creating Gold layer tables with star schema dimensional models
• Implementing PRIMARY KEY and FOREIGN KEY constraints in Unity Catalog
• Setting up SCD Type 1 or Type 2 dimension tables
• Creating fact tables that reference dimension tables
• Troubleshooting constraint application errors
• Preventing common production deployment failures

Most Common Failure

FK constraints referencing business keys instead of surrogate PK columns.

This is the single highest-impact failure observed in production. The Gold design YAML may generate references: dim_user(user_id) but the PK is user_key. Unity Catalog will reject the FK with: foreign key parent columns do not match the referenced primary key.

Before applying ANY FK constraint, verify the target column is a PRIMARY KEY. See the Pre-Apply Checklist below.

Critical Rules

1. Surrogate Keys as Primary Keys

❌ WRONG: Business keys as PKs, facts reference business keys

✅ CORRECT: Surrogate keys as PKs, facts reference surrogate keys

CREATE TABLE dim_store (
    store_key STRING NOT NULL,  -- ✅ Surrogate PK
    store_number STRING NOT NULL,  -- Business key (UNIQUE)
    CONSTRAINT pk_dim_store PRIMARY KEY (store_key) NOT ENFORCED,
    CONSTRAINT uk_store_number UNIQUE (store_number) NOT ENFORCED
)

CREATE TABLE fact_sales (
    store_key STRING NOT NULL,  -- ✅ Surrogate FK
    CONSTRAINT fk_sales_store FOREIGN KEY (store_key) 
        REFERENCES dim_store(store_key) NOT ENFORCED
)

2. NOT NULL Requirement for Primary Keys

All surrogate key columns used in PKs MUST be NOT NULL.

Error: Cannot create the primary key because its child column(s) is nullable.

3. Foreign Keys Must Reference Primary Keys

Foreign keys MUST reference PRIMARY KEY columns, even when NOT ENFORCED.

• FKs can only reference PK columns (not any column)
• FKs must reference exact column(s) in PK definition

4. Never Define FK Constraints Inline in CREATE TABLE

❌ WRONG: FK constraints inline during table creation

✅ CORRECT: Define PKs inline, apply FKs later via ALTER TABLE

-- Step 1: CREATE TABLE with PK only
CREATE TABLE fact_sales (
    CONSTRAINT pk_fact_sales PRIMARY KEY (...) NOT ENFORCED  -- ✅ OK
)

-- Step 2: Apply FKs after all tables exist (separate script)
ALTER TABLE fact_sales
    ADD CONSTRAINT fk_sales_store 
    FOREIGN KEY (store_key) 
    REFERENCES dim_store(store_key) NOT ENFORCED  -- ✅ Safe

Key Principles:

• ✅ Inline PKs are safe - Define PRIMARY KEY constraints directly in CREATE TABLE
• ❌ Inline FKs fail - Never define FOREIGN KEY constraints in CREATE TABLE
• ✅ Separate constraint script - Create dedicated constraints.py to apply all FKs after tables exist
• ✅ Dependency order matters - Reference tables (dims) before fact tables, constraints last

5. Never Use DEFAULT Column Clauses in DDL

❌ FORBIDDEN: a DEFAULT <expr> clause on any column in CREATE TABLE (Delta / serverless).

-- ❌ WRONG — fails with "...requires table feature allowColumnDefaults..." by default
is_active BOOLEAN NOT NULL DEFAULT true

-- ✅ CORRECT — declare the column, set the default at INSERT time
is_active BOOLEAN NOT NULL          -- in CREATE TABLE
... INSERT ... SELECT ..., true AS is_active, ...   -- default supplied on write

Column DEFAULT expressions require the delta.feature.allowColumnDefaults table feature, which is off by default — DDL using DEFAULT fails on a standard table. Do NOT enable the feature flag to make DEFAULT work; set defaults at INSERT time instead (and do not add columns the design/template did not call for — this rule exists because an agent invented an is_active BOOLEAN NOT NULL DEFAULT true column that was never in the template). This applies to every layer's DDL (Bronze, Silver dq_rules, Gold), not just constraints.

Quick Reference

Dimension Table (SCD Type 1)

• Surrogate key (*_key) is PRIMARY KEY
• Business key has UNIQUE constraint
• All PK columns are NOT NULL
• PK defined inline in CREATE TABLE

Dimension Table (SCD Type 2)

• Surrogate key (*_key) is PRIMARY KEY
• Business key does NOT have UNIQUE (multiple versions allowed)
• is_current flag indicates current version
• Facts join to surrogate key, filter WHERE is_current = TRUE

Fact Table

• Composite PK on surrogate FKs (defines grain)
• Surrogate FKs reference dimension PKs
• Business keys included for readability
• NO inline FK constraints (apply separately via ALTER TABLE)

Date Type Casting

Rule: Always CAST(DATE_TRUNC(...) AS DATE) when populating DATE columns.

Constraint Naming Conventions

Quick Reference Checklist

Table Creation Phase

• All dimension tables define PRIMARY KEY inline in CREATE TABLE
• NO fact tables define FOREIGN KEY inline in CREATE TABLE
• NO DEFAULT column clauses anywhere in DDL (set defaults at INSERT time)
• Date columns use explicit CAST(DATE_TRUNC(...) AS DATE)
• All surrogate key columns are NOT NULL
• Reference dimension (dim_date) created before facts that reference it

Constraint Application Phase

• Separate constraints.py script applies ALL FKs via ALTER TABLE
• Constraint script runs AFTER all table creation tasks
• Primary keys applied to dimensions before foreign keys applied to facts
• Each FK references an existing PK column
• Constraint names are descriptive (e.g., pk_dim_date, fk_sales_date)

Validation Phase

• All surrogate key columns are NOT NULL
• PKs defined on surrogate keys (proper dimensional modeling)
• FKs reference surrogate PK columns exactly
• Facts have surrogate FK columns (store_key, product_key, date_key)
• Business keys have UNIQUE constraints (where applicable)
• All constraints include NOT ENFORCED

Common Mistakes to Avoid

1. PK on business key - Use surrogate keys as PKs
2. Nullable surrogate key - All PK columns must be NOT NULL
3. FK references non-PK column - FKs must reference PK columns
4. Inline FK constraints - Never define FKs in CREATE TABLE
5. DATE_TRUNC without casting - Always CAST to DATE
6. Module imports in notebooks - Inline helper functions or use pure Python
7. Widget parameter name mismatch - YAML parameters must match dbutils.widgets.get()

Serverless Limitations

UNIQUE constraints require spark.databricks.sql.dsv2.unique.enabled = true. This config cannot be set in serverless compute (Spark config is read-only).

Impact: spark.conf.set(...) will crash the job in serverless.

Workarounds:

• Use classic (non-serverless) compute for the constraint application job
• Skip UNIQUE constraints on serverless; rely on surrogate PK + FK only
• Use a DAB job definition that targets a classic job cluster for constraint tasks

Pre-Apply Checklist

Before executing any FK ALTER TABLE statement:

1. Verify target column is a PK -- run SHOW CONSTRAINTS IN <schema> or DESCRIBE EXTENDED <dim_table> to confirm the referenced column has a PRIMARY KEY constraint
2. Verify PK constraint exists -- if the dimension table was just created but PK not yet applied, the FK will fail
3. Verify serverless compatibility -- if running on serverless, skip UNIQUE constraints (see Serverless Limitations above)
4. Verify YAML FK format -- the references: field must use surrogate key columns (e.g., dim_store(store_key)), NOT business keys (e.g., dim_store(store_number))

Cross-Skill Dependencies

• Gold Design skill (gold/00-gold-layer-design): The YAML schema generator may produce FK references to business keys. During Gold implementation, validate that all references: fields in YAML point to surrogate PK columns before applying constraints.
• Gold Setup skill (gold/01-gold-layer-setup): The fk-constraint-patterns.md reference defers to this skill for PK/FK rules. Ensure the FK application script verifies PK existence first.

Reference Files

constraint-patterns.md

Detailed SQL patterns including:

• Standard dimensional model patterns (SCD Type 1/2, date dimensions, fact tables)
• Correct vs incorrect pattern examples
• Common mistakes and solutions
• Production error prevention patterns
• Benefits of NOT ENFORCED constraints
• Design philosophy

validation-guide.md

Validation patterns and troubleshooting:

• NOT NULL requirement for primary keys
• Databricks FK constraint requirements
• Validation checklists (pre-deployment, post-deployment)
• Common error patterns and solutions
• Production deployment checklist
• Benefits of NOT ENFORCED constraints

Scripts

apply_constraints.py

Python utility for applying constraints:

• verify_pk_exists() - Verify PK constraint exists before applying FK (pre-flight check)
• add_primary_key() - Add PRIMARY KEY constraint
• add_foreign_key() - Add FOREIGN KEY constraint (with optional PK verification)
• add_unique_constraint() - Add UNIQUE constraint (for business keys)
• drop_constraint() - Drop constraint (for idempotency)
• apply_all_constraints() - Example function applying all constraints

Usage:

from scripts.apply_constraints import add_primary_key, add_foreign_key

add_primary_key(spark, catalog, schema, "dim_store", ["store_key"])
add_foreign_key(spark, catalog, schema, "fact_sales", ["store_key"], 
                "dim_store", ["store_key"])

Assets

constraints-template.sql

SQL template for creating Gold layer tables with constraints:

• Dimension table templates (SCD Type 1 and Type 2)
• Date dimension template
• Fact table template (PK only, no inline FKs)
• Foreign key application examples (ALTER TABLE statements)

Usage: Copy template, replace placeholders (<dimension_name>, <business_key>, etc.), customize for your schema.

References

• Unity Catalog Constraints
• Primary and Foreign Keys
• Dimensional Modeling Best Practices
• Star Schema Design