SQL Server Interview Question #32
What is the difference between PRIMARY KEY and UNIQUE?
Data Types, Keys & Constraints Junior Beginner
Quick Interview Answer
Both PRIMARY KEY and UNIQUE enforce uniqueness, but they serve different relational purposes.
A PRIMARY KEY identifies the principal key chosen to identify each row. A table can have only one primary-key constraint, and its key columns cannot be NULL. A table can have multiple UNIQUE constraints, which are useful for alternate candidate keys.
In SQL Server, a primary key creates a unique index by default; whether that index is clustered or nonclustered depends on the table's existing indexes and the explicit constraint definition.
Detailed Explanation
Both PRIMARY KEY and UNIQUE enforce uniqueness, but they serve different relational purposes.
A PRIMARY KEY identifies the principal key chosen to identify each row. A table can have only one primary-key constraint, and its key columns cannot be NULL. A table can have multiple UNIQUE constraints, which are useful for alternate candidate keys.
In SQL Server, a primary key creates a unique index by default; whether that index is clustered or nonclustered depends on the table's existing indexes and the explicit constraint definition.