SQL · Theory

Primary Key vs Unique key

← All stacks

Theory

270/439

Primary Key vs Unique key

Primary Key vs Unique key is a SQL idea you prove with lookup by roll, not with a slogan.

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

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

Primary Key vs Unique key shows up in a big table. Name lookup by roll, not “a real-world scenario”.

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

Place Primary Key vs Unique key next to nearby SQL work — PRIMARY KEY(roll) + INDEX is the link.

One breath for Primary Key vs Unique 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 Unique key: definition + a big table + one failure.

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

Short notes

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

Primary Key vs Unique key — sample query

Short notes

Questions

Primary Key vs Unique key

Primary Key vs Unique key — sample query

Short notes

Questions