SQL · Theory

Primary Key vs Candidate Key

← All stacks

Theory

271/439

Primary Key vs Candidate Key

Primary Key vs Candidate Key sits in a big table. Riya’s job is lookup by roll. Write that first.

Keep Primary Key vs Candidate Key small. Riya should finish lookup by roll in a few lines, not a 40-line dump.

Skip Primary Key vs Candidate Key and indexing a low-cardinality flag only shows up in a big table.

Use Primary Key vs Candidate Key when lookup by roll must stay clear. If a simpler SQL step works, use that instead.

If lookup by roll breaks under Primary Key vs Candidate Key, check indexing a low-cardinality flag only first.

After Primary Key vs Candidate Key, Riya should still remember indexing a low-cardinality flag only.

One breath for Primary Key vs Candidate Key, then lookup by roll, then indexing a low-cardinality flag only. Sit down.

Diagram

  students.id  ← PK (unique)
       ▲
       │ FK
  marks.student_id

Exam tip

For Primary Key vs Candidate Key: definition + a big table + one failure.

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.

Short notes

DefPrimary Key vs Candidate Key — Riya uses it for lookup by roll in a big table.
RulePrimary Key vs Candidate Key → PRIMARY KEY(roll) + INDEX.
RememberPrimary Key vs Candidate Key + an index (a big table).
UsePrimary Key vs Candidate Key in a big table (lookup by roll).
TrapPrimary Key vs Candidate Key — indexing a low-cardinality flag only.
ExPrimary Key vs Candidate Key → lookup by roll.

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

Primary Key vs Candidate Key — sample query

Short notes

Questions

Primary Key vs Candidate Key

Primary Key vs Candidate Key — sample query

Short notes

Questions