Explain database primary key vs unique key?

Both Primary Key and Unique Key ensure uniqueness, but they are not the same.

1. Primary Key

• Uniquely identifies each row
• Cannot be NULL
• Only one primary key per table
• Automatically creates a clustered index (in most DBs like SQL Server)
• Used as the main identifier and for foreign keys

Example:

UserId INT PRIMARY KEY

2. Unique Key

• Ensures values are unique, but not the main identifier
• Allows NULL values (usually only one NULL, DB-dependent)
• Multiple unique keys allowed per table
• Creates a non-clustered index by default
• Not mandatory for foreign key reference (but can be used)

Example:

Email VARCHAR(100) UNIQUE

Key Differences (Quick Table)

Real-world Example

CREATE TABLE Users (
    UserId INT PRIMARY KEY,
    Email VARCHAR(100) UNIQUE,
    AadhaarNo CHAR(12) UNIQUE
);

• UserId → Primary Key (main identifier)
• Email, AadhaarNo → Unique Keys (no duplicates allowed)

Interview One-Liner

Primary Key uniquely identifies a record and cannot be NULL, while Unique Key only enforces uniqueness and can allow NULL values.