Interview Question of the Week #010 – What is the Difference Between Primary Key Constraints and Unique Key Constraints?

I often see primary keys and unique constraints treated as the same thing. They both enforce uniqueness, but this interview question has two important differences.

Question: What is the difference between a primary key and a unique constraint in SQL Server?

Answer: A table has one primary key. Its key columns cannot be NULL. A table can have other unique constraints for alternate business keys, and a unique constraint can be defined on a nullable column. For a single nullable column, SQL Server permits one NULL under an ordinary unique constraint, not unlimited NULL rows.

The index detail often comes up as a follow-up. SQL Server enforces both kinds of constraint with a unique index. A primary key commonly gets a clustered index by default when the table has none; a unique constraint commonly gets a nonclustered index. These are defaults, not definitions. You can explicitly make the primary key nonclustered, and the table still has exactly one primary key.

I start with the data rule, then discuss the physical index choice. If an interviewer answers only “one is clustered and the other is nonclustered,” I would ask them to think about a primary key declared NONCLUSTERED. That small counterexample reveals the real distinction.

For a longer treatment, see my earlier notes on primary and unique constraints and on a nonclustered primary key.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.