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
NULLdepending 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."