What is the difference between a primary key and a unique key?
Quick Answer
Both enforce uniqueness. A primary key uniquely identifies a row, does not allow NULL, and there is exactly one per table (it creates a clustered index by default). A unique key/constraint also guarantees uniqueness but allows a single NULL (in SQL Server), you can have several per table, and it creates a non-clustered index by default.
They both enforce uniqueness. The primary key is the row's identity — it can't be null, there's only one per table, and by default it's the clustered index. A unique constraint enforces a uniqueness rule on some other column, like an email address; you can have several, they allow one null in SQL Server, and they're non-clustered by default. Both are candidate keys — you promote one to primary key and the rest stay as unique constraints, and a foreign key can point at either.
| Primary key | Unique constraint | |
|---|---|---|
| Per table | One | Many |
| NULLs | Not allowed | One NULL allowed (SQL Server) |
| Default index | Clustered | Non-clustered |
| Purpose | The row's identity; target of foreign keys | Enforce a business uniqueness rule (e.g. email) |
Both are candidate keys — columns that could identify a row. You pick one as the primary key (often a surrogate identity/GUID), and the others become unique constraints. Foreign keys can reference either a primary key or a unique constraint.