Context

At companies like Notion, interviewers often ask this to test whether you understand table design, data integrity, and constraint behavior in relational databases.

Question

Explain the difference between a primary key and a unique key in PostgreSQL. Your answer should cover:

1. What each constraint does
2. Whether each allows NULL values
3. How many of each can exist on a table
4. When you would choose one over the other
5. A simple example showing both on the same table

Scope Guidance

Keep the explanation practical rather than theoretical. The interviewer is usually looking for a clear comparison, correct handling of NULL, and an understanding that both enforce uniqueness but serve different modeling purposes. A strong answer should also mention that a primary key is the main row identifier, while a unique key is commonly used for alternate candidate identifiers such as email or username.

Primary Key

A primary key uniquely identifies each row in a table and cannot contain NULL values. A table can have only one primary key, though that key may consist of multiple columns.

CREATE TABLE users (
  user_id INT PRIMARY KEY,
  email VARCHAR(255)
);

Unique Constraint

A unique key, implemented in PostgreSQL as a UNIQUE constraint, ensures that non-NULL values are not duplicated. A table can have multiple unique constraints, which makes them useful for alternate identifiers such as email, username, or external reference codes.

CREATE TABLE users (
  user_id INT PRIMARY KEY,
  email VARCHAR(255) UNIQUE,
  username VARCHAR(100) UNIQUE
);

NULL Behavior

A primary key always implies NOT NULL, so every row must have a value. A UNIQUE constraint does not automatically forbid NULL unless you also declare the column as NOT NULL, and PostgreSQL allows multiple NULLs under a standard UNIQUE constraint.

CREATE TABLE users (
  user_id INT PRIMARY KEY,
  email VARCHAR(255) UNIQUE,
  phone VARCHAR(50) UNIQUE
);

Modeling Intent

The primary key represents the main identity of the row and is typically referenced by foreign keys from other tables. Unique constraints are used for business rules that require uniqueness but are not the table's main identifier.

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  email VARCHAR(255) UNIQUE,
  loyalty_number VARCHAR(50) UNIQUE
);