PrismitaLEARN · PRACTICE · GROW

What is the difference between a UNIQUE constraint and a UNIQUE index?

SQL Server Keys & Constraints Intermediate 2–3 Yrs3–5 Yrs Conceptual 1 min read

Quick Answer

They enforce the same rule and are implemented the same way — a UNIQUE constraint creates a unique index behind the scenes. The differences are cosmetic and feature-level: an index can have INCLUDE columns and a WHERE filter (a filtered unique index); a constraint is the “intent” declaration and is what a foreign key can reference.

Functionally they're the same thing — a unique constraint is implemented as a unique index. The reasons to pick one are practical. I use a unique constraint to state a business rule, because it reads clearly in the schema and a foreign key can point at it. I use a unique index directly when I need index features a constraint can't have — INCLUDE columns, or a filtered index so uniqueness only applies to, say, non-deleted rows. That filter trick is also how you allow multiple nulls, since SQL Server otherwise only permits one.

  • Use a UNIQUE constraint to express a business rule (“email must be unique”) — it shows up clearly in the schema and FKs can target it.
  • Use a UNIQUE INDEX when you need index features: INCLUDE to cover a query, or a filtered index like WHERE IsDeleted = 0 to enforce uniqueness only among active rows.
  • In SQL Server a unique index/constraint allows exactly one NULL (treats NULL = NULL for uniqueness). A filtered index WHERE col IS NOT NULL gets around that.
All SQL Server questions More on Keys & Constraints