Data modeling · Keys & Relationships

Primary key vs unique constraint

Easyprimary keyuniqueconstraintsidentity

Interview question

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

How to answer

The short answer

Both enforce uniqueness, but they play different roles.

Primary key (PK)

  • Identifies each row
  • One per table
  • Columns are NOT NULL
  • What 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
Output
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."