SQL · Theory

Composite Key

Theory

74/439

Composite Key

If you can teach Composite Key using Composite Key on Priya's board, you know it. If you only know the heading, you don’t.

Composite Key on the board: one Composite Key step Priya can write from memory. Then say what Composite Key on Priya's board looks like after.

Without Composite Key, a small live site gets messy and Composite Key on Priya's board is hard to trust.

Use Composite Key when Composite Key on Priya's board must stay clear. If a simpler SQL step works, use that instead.

Composite Key miss — copy-pasting Composite Key without a dry-run. Fix it before you talk about advanced SQL.

After Composite Key, Priya should still remember copy-pasting Composite Key without a dry-run.

Close Composite Key with: “If I skip it, Composite Key on Priya's board goes wrong like this: copy-pasting Composite Key without a dry-run.”

Diagram

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

Exam tip

Board: Composite Key on Priya's board. Dry-run Composite Key. Name the trap: copy-pasting Composite Key without a dry-run.

Composite 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;

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

Short notes

DefComposite Key — Priya uses it for Composite Key on Priya's board in a small live site.
RuleComposite Key → one Composite Key step Priya can write from memory.
RememberComposite Key + the query planner (a small live site).
UseComposite Key in a small live site (Composite Key on Priya's board).
TrapComposite Key — copy-pasting Composite Key without a dry-run.
ExComposite Key → Composite Key on Priya's board.

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;

Composite Key

Composite Key — sample query

Short notes

Questions

Composite Key

Composite Key — sample query

Short notes

Questions