Table constraints - NOT NULL, CHECK, and Identity Columns

Estimated read: 11 min

Schema enforcement (two lectures back) validates types. It says nothing about whether a value is allowed to be null, or whether it falls in a sane range. Constraints close that gap – the direct Delta Lake equivalent of the NOT NULL, CHECK, and identity-column definitions you already rely on in a warehouse DDL.

NOT NULL

CREATE TABLE main.default.orders (
    order_id     BIGINT NOT NULL,
    customer_id  BIGINT NOT NULL,
    order_total  DECIMAL(10,2)
);

-- Or added to an existing table
ALTER TABLE main.default.orders ALTER COLUMN customer_id SET NOT NULL;

Adding NOT NULL to an existing column validates every existing row first – if any current row has a null in that column, the ALTER TABLE fails rather than silently accepting a constraint the data doesn’t actually satisfy. This is an enforced constraint: every future write that violates it is rejected, the same way it would be in any warehouse engine.

Caveat for nested columns: for struct columns, the parent struct itself must also be NOT NULL for a nested field’s NOT NULL to be meaningful; array and map element types don’t support NOT NULL directly.

CHECK constraints

ALTER TABLE main.default.orders
ADD CONSTRAINT valid_order_total CHECK (order_total >= 0);

ALTER TABLE main.default.orders
ADD CONSTRAINT valid_status CHECK (order_status IN ('pending', 'shipped', 'cancelled', 'delivered'));

-- Removing one
ALTER TABLE main.default.orders DROP CONSTRAINT valid_order_total;

CHECK constraints are arbitrary boolean SQL expressions – any deterministic function is allowed (same input always produces the same output), but user-defined functions, aggregate functions, window functions, and anything returning multiple rows are not. A CHECK constraint has to be evaluable row-by-row, deterministically, at write time.

Key term: both NOT NULL and CHECK are enforced constraints – Delta actively rejects violating writes. This is meaningfully different from the next category.

Informational constraints: primary key, foreign key, unique

ALTER TABLE main.default.orders
ADD CONSTRAINT orders_pk PRIMARY KEY (order_id);

ALTER TABLE main.default.order_items
ADD CONSTRAINT fk_order
FOREIGN KEY (order_id) REFERENCES main.default.orders (order_id);

Primary key, foreign key, and unique constraints in Delta Lake are informational, not enforced – Databricks records that this relationship should hold and can use that information to optimize query plans, but it does not reject a write that violates it. This is the single biggest surprise for anyone coming from a warehouse where a primary key violation is a hard write- time error.

Why the distinction matters in practice

Constraint	Enforced at write time?	Purpose
NOT NULL	Yes	Data integrity
CHECK	Yes	Data integrity, business rules
PRIMARY KEY	No – informational only	Documents intent, aids query optimizer
FOREIGN KEY	No – informational only	Documents relationships, aids query optimizer
UNIQUE	No – informational only	Documents intent

If your legacy warehouse used primary/foreign keys as an actual enforcement mechanism (rejecting duplicate or orphaned inserts), you cannot rely on Delta Lake’s declared keys to do that job – you need MERGE’s single-match semantics, an explicit CHECK, or a data-quality validation step (the pattern Part 2’s StepRight project builds out fully in its data quality monitoring section) to actually enforce uniqueness or referential integrity.

Identity columns

CREATE TABLE main.default.audit_log (
    log_id BIGINT GENERATED ALWAYS AS IDENTITY,
    event_type STRING,
    logged_at TIMESTAMP
);

GENERATED ALWAYS AS IDENTITY auto-generates a unique, incrementing BIGINT for every inserted row – the Delta equivalent of a warehouse’s auto-increment/identity column or sequence-backed surrogate key. Use GENERATED BY DEFAULT AS IDENTITY if you need the option to explicitly supply a value on some inserts while auto-generating on others.

For the complete official reference on all constraint types and their exact enforcement guarantees, see Constraints on Databricks.

That closes out the hands-on Delta Lake content. The section’s knowledge check is next, before Section 6 moves into Unity Catalog – the governance layer that sits on top of every table you’ve just learned to build.

---

| ← Previous: Type Widening and the Variant Data Type | Next: Check Your Knowledge → | |:—|—:|