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