Skip to content
Quantoros

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

SQLLevel: IntermediateDatabase Design

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

Hints
  • Define one output row and the source-table keys.
  • Check duplicates, NULLs and the order of filtering and aggregation.

Answer

A candidate key is any column or set of columns that could serve as a unique identifier for rows (NOT NULL and UNIQUE). A table can have multiple candidate keys. The primary key is the candidate key you choose as the main identifier.

CREATE TABLE users (
    user_id  SERIAL PRIMARY KEY,  -- chosen primary key
    email    VARCHAR(255) NOT NULL UNIQUE,  -- alternate candidate key
    username VARCHAR(50)  NOT NULL UNIQUE   -- alternate candidate key
);

Source and licence: SQL Interview Questions — Arda Kocadoru · MIT

Related questions