SQL · Theory

Primary Key vs Foreign Key

← All stacks

Theory

269/439

Primary Key vs Foreign Key

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

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

Without Primary Key vs Foreign Key, a big table gets messy and lookup by roll is hard to trust.

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

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

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

Close Primary Key vs Foreign 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 vs Foreign Key. Name the trap: indexing a low-cardinality flag only.

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

Short notes

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

Primary Key vs Foreign Key — sample query

Short notes

Questions

Primary Key vs Foreign Key

Primary Key vs Foreign Key — sample query

Short notes

Questions