Your PRIMARY KEY isn't protecting you

Your RDBMS reflex will cost you

If you migrated from Postgres or SQL Server, your muscle memory says: declare a PRIMARY KEY and duplicates get rejected. On Databricks, that reflex will cost you.

PRIMARY KEY, FOREIGN KEY, and UNIQUE constraints on Databricks are informational only — metadata the optimizer can use, never guardrails that block writes. The official docs are blunt (last verified September 29, 2026): “Primary key, foreign key, and unique constraints are informational only and aren't enforced.”

Only two constraint types actually bite: NOT NULL and CHECK. Violate one and the entire write fails atomically — Delta Lake raises InvariantViolationException and nothing is committed.

Reproducible example — illustrative scenario with sample data

CREATE TABLE demo.orders (
  order_id BIGINT NOT NULL,
  amount DECIMAL(10,2),
  CONSTRAINT orders_pk PRIMARY KEY (order_id)
) USING DELTA;

INSERT INTO demo.orders VALUES (1, 99.50), (1, 41.00);
-- Succeeds. No error. Both rows land.

SELECT order_id, COUNT(*) AS n
FROM demo.orders
GROUP BY order_id
HAVING COUNT(*) > 1;

1. Where this bites: the RDBMS migration

A team declares PKs and FKs across their bronze tables and discovers — usually in an audit, sometimes from a downstream customer — that duplicate business keys have been flowing in for weeks. The Databricks Community forums carry this exact story: “I created a table with Primary Key, though it doesnt act like Primary Key and is taking duplicate values on the key column.”

2. The enforcement pattern: enforce business keys in pipeline code, not in DDL

Dedupe the source before you write (still the cheapest duplicate fix), or guard domain invariants with a real CHECK:

ALTER TABLE demo.orders
ADD CONSTRAINT positive_amount CHECK (amount > 0);

INSERT INTO demo.orders VALUES (2, -5.00);
-- Fails: InvariantViolationException.
-- The entire write is rejected; nothing commits.

But CHECK has four sharp edges, all verified against the official docs on September 29, 2026:

1. Existing data is scanned. Adding a CHECK validates every existing row before the constraint is added — on a large table, ADD CONSTRAINT is a full scan, not instant DDL, so plan a window.
2. NULL fails. The condition must evaluate to TRUE — NULL counts as a violation, stricter than three-valued logic, so for nullable columns write amount IS NULL OR amount > 0 or tighten with NOT NULL.
3. Expressions are restricted. Deterministic boolean expressions only — no aggregates, no window functions, no subqueries.
4. The protocol can move. Adding a CHECK bumps the table's minimum writer protocol version if it is below 3, which can stop older runtimes and external Delta writers from writing to the table — readers are unaffected. The official escape hatch is DROP FEATURE on Databricks Runtime 15.4 LTS and above, which removes check constraints and downgrades the protocol.

3. RELY is the foot-gun

Declaring a constraint with RELY (for example PRIMARY KEY (order_id) RELY) tells the optimizer your data satisfies it, unlocking rewrites like join elimination and DISTINCT simplification on Photon. The default is NORELY, and NORELY is the safe setting. If the data violates a RELY constraint, queries silently return wrong results — and per the docs, “It is the user's responsibility to ensure the constraint is satisfied.” Never set RELY on a constraint you have not proven with the audit query below.

4. The DLT contrast

In Delta Live Tables, dp.expect's default action is warn — invalid records are still written to the target; only metrics are logged. If you want the bad rows gone, spell it out: dp.expect_or_drop drops them before data is written, and dp.expect_or_fail fails the whole update (manual intervention is required before reprocessing).

5. Constraint house rules

Verified September 29, 2026: Unity Catalog only — hive_metastore relations do not support primary key constraints. Primary key columns must be NOT NULL; at most one primary key per table. A foreign key must reference a PRIMARY KEY — or, on Databricks Runtime 18.2 and above, a UNIQUE constraint. UNIQUE is still Public Preview on 18.2+, and NULLs are treated as distinct from each other. CTAS statements don't support the constraint clause.

“I added a PRIMARY KEY but my Unity Catalog table still has duplicate keys — is something corrupt?”

Nothing is corrupt. Declared primary keys on Databricks are informational only — never enforced, by design. Run the audit query below to measure the damage, fix the pipeline (dedupe the source before you write, or MERGE on the key), and keep declared constraints as documentation and lineage metadata. Leave RELY off until you have proven the constraint against the data. If you need a hard block at write time, NOT NULL and CHECK are your only real guardrails.

Use this when

• Declaring PK/FK as documentation and lineage metadata for tools and readers.
• Setting RELY after you have proven the constraint holds on the data, to unlock optimizer rewrites.
• Guarding domain invariants at write time with NOT NULL and CHECK.

Avoid this when

• Assuming a declared PRIMARY KEY blocks duplicates — it never does.
• Setting RELY on unverified constraints (wrong results, and per the docs that's on you).
• Adding CHECK to a huge table without a maintenance window (it validates all existing rows).
• Depending on UNIQUE for anything production (Public Preview on 18.2+, still informational, not enforced).
• Treating dp.expect's default warn as a data-quality gate (invalid records are still written).