What is the difference between a primary key and a unique key?

Question

Accepted Answer

A primary key and a unique key both enforce uniqueness on a column or set of columns, but a primary key is the single, mandatory identifier for a table's rows, while a unique key is an additional, optional uniqueness constraint.

Key Points:
• A table can have only one primary key, but it can have multiple unique keys.
• A primary key cannot contain NULL values; a unique key can typically allow one or more NULLs, depending on the database.
• A primary key is automatically indexed and often used as the target of foreign key relationships.
• Unique keys are commonly used for natural identifiers like email addresses or usernames that must be unique but aren't the table's main identifier.

Example:
In a users table, the auto-generated user_id would be the primary key, while the email column would carry a unique key constraint to prevent duplicate sign-ups without making email the row's core identifier.

Code Example:
```
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(100) UNIQUE,
    username VARCHAR(50)
);
```

Interview Tip:
A concise interview answer is:

"A primary key is the one mandatory, non-null identifier for a table's rows, while a unique key enforces uniqueness on other columns and a table can have several of them, often allowing NULLs. For example, user_id would be the primary key and email would be a unique key on the same users table."