Primary Key vs Candidate Key — SQL Technical Interview Questions & Answers

Primary Key vs Candidate Key

Primary Key vs Candidate Key

  • A candidate key is any column set that could be the PK (unique + not null). You pick one candidate as the PRIMARY KEY. The others stay UNIQUE (alternate keys). roll and (aadhaar) might both be candidates — you pick roll as PK and UNIQUE on aadhaar.
  • This is theory (DBMS textbooks). In MySQL you implement: one PRIMARY KEY, other candidates as UNIQUE NOT NULL.
  • Trap: reciting ‘candidate key’ without an example table.
  • On the example next to this theory: Primary Key vs Candidate Key — keys and constraints protect data integrity; indexes speed lookups.

Technical Interview Tip: Candidates: roll or aadhaar. Pick one PK, UNIQUE the other.

Code Examples for Primary Key vs Candidate Key

Primary Key vs Candidate Key — sample query

CREATE TABLE authors (
  id INTEGER PRIMARY KEY,
  email TEXT UNIQUE NOT NULL
);
CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  author_id INTEGER NOT NULL,
  title TEXT NOT NULL,
  FOREIGN KEY (author_id) REFERENCES authors(id)
);
INSERT INTO authors VALUES (1, 'a@x.com');
INSERT INTO books VALUES (1, 1, 'SQL Basics');
SELECT * FROM books;

Primary Key vs Candidate Key — keys and constraints protect data integrity; indexes speed lookups.

How to Explain Primary Key vs Candidate Key to an Interviewer

Candidate vs primary?

All possible ids vs the one you picked