What are the main differences between a primary key and a unique constraint?

Question

I answered that tables had only one primary key but could have many unique constraints. But what else?

Answer 1

Primary column can never be null, a unique column can be.

Answer 2

Some differences I could think of:

1. Primary Key can't be null whereas unique will allow one null value.
2. You can have multiple unique keys on a table but only one Primary Key.

Some taken from WikiPedia - Unique key - Differences from primary key constraints:

Primary Key constraint

1. A Primary Key cannot allow null (a primary key cannot be defined on columns that allow nulls).
2. Each table cannot have more than one primary key.
3. On some RDBMS a primary key generates a clustered index by default.

Unique constraint

1. A unique constraint can be defined on columns that allow nulls.
2. Each table can have multiple unique keys.
3. On some RDBMS a unique key generates a nonclustered index by default.

Answer 3

It's hard to say what the interviewer might have been looking for. There are lots of options.

In standard SQL, a constraint declared primary key and a constraint declared not null unique behave the same at the logical level. For example, both of those can be the target of foreign key references. The interviewer might have wanted to know about how null fits into that. A bare unique constraint allows nulls; a primary key constraint implicitly declares each column not null in T-SQL.

Or the interviewer might have been looking to see whether you distinguished a unique constraint from an unique index. AFAIK, every dbms implements unique constraints by using a unique index. But a constraint expresses something about the database at a logical level, and a unique index expresses something about the database at the physical level.

SQL Server in particular

The interviewer might have wanted to see whether you knew that some computed columns, but not all of them, can be indexed. (That one's a long shot.)

Maybe the interviewer wanted to see if you'd say anything about clustering. A primary key constraint defaults to clustered in SQL Server, but an index defaults to nonclustered.

Maybe the interviewer wanted to see whether you'd say anything about permissions. You typically need broader permissions to add a constraint than you need to add an index.