SQL · Theory

Foreign Key

← All stacks

Theory

73/439

Foreign Key

Amit only reaches for Foreign Key when lookup by roll has to stay correct in a big table.

Foreign Key order — meaning → PRIMARY KEY(roll) + INDEX → one limit. Dumping ten features helps nobody.

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

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

Foreign Key miss — indexing a low-cardinality flag only. Fix it before you talk about advanced SQL.

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

One breath for Foreign 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

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

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;

Foreign Key — keys and constraints protect data integrity; indexes speed lookups.

Short notes

DefForeign Key — Amit uses it for lookup by roll in a big table.
RuleForeign Key → PRIMARY KEY(roll) + INDEX.
RememberForeign Key + an index (a big table).
UseForeign Key in a big table (lookup by roll).
TrapForeign Key — indexing a low-cardinality flag only.
ExForeign 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;

Foreign Key

Foreign Key — sample query

Short notes

Questions

Foreign Key

Foreign Key — sample query

Short notes

Questions