Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing
Back
  1. Home
  2. Interview prep
  3. Primary key vs unique constraint

Data modeling · Keys & Relationships

Primary key vs unique constraint

Easymodel-13
primary keyuniqueconstraintsidentity

Question

What is the difference between a primary key and a unique constraint?

Solution

Both enforce uniqueness, but they play different roles.

Primary key (PK)

  • Identifies each row
  • One primary key per table
  • Columns are NOT NULL
  • What foreign keys typically reference

Unique constraint / unique index

  • Also prevents duplicate values
  • Table can have many unique constraints
  • Columns may allow NULL depending on the database (NULL handling differs by engine)
  • Often used for alternate business keys (email, sku) alongside a surrogate PK
dim_customer
  customer_sk   PRIMARY KEY
  email         UNIQUE        -- alternate business identifier
  customer_id   UNIQUE        -- source natural key (Type 1 / current table)

In warehouses

Many cloud warehouses historically under-enforced PK/unique at write time; teams still declare them for documentation and dbt tests even when the engine does not hard-reject duplicates.

Interview tip

> "One primary key, not null, the main identity. Unique can appear many times and often marks alternate keys."

PreviousNext