SQL · Theory

Primary Key

← All stacks

Theory

72/439

Primary Key

If you can teach Primary Key using lookup by roll, you know it. If you only know the heading, you don’t.

Do Primary Key once by hand. PRIMARY KEY(roll) + INDEX. Change one input. Say the new result out loud.

Primary Key exists so Meera can keep lookup by roll correct in a big table.

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

Don’t do this with Primary Key — indexing a low-cardinality flag only. Interviewers spot it in ten seconds.

Primary Key is not a lonely heading. Meera ties it to lookup by roll.

Close Primary Key with — “If I skip it, lookup by roll goes wrong like this: indexing a low-cardinality flag only.”

Diagram

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

Exam tip

Board: lookup by roll. Dry-run Primary Key. Name the trap: indexing a low-cardinality flag only.

Primary 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 — keys and constraints protect data integrity; indexes speed lookups.

Short notes

DefPrimary Key — Meera uses it for lookup by roll in a big table.
RulePrimary Key → PRIMARY KEY(roll) + INDEX.
RememberPrimary Key + the query planner (a big table).
UsePrimary Key in a big table (lookup by roll).
TrapPrimary Key — indexing a low-cardinality flag only.
ExPrimary 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

Primary Key — sample query

Short notes

Questions

Primary Key

Primary Key — sample query

Short notes

Questions