SQL · Theory

Composite Key

Theory

74/439

Composite Key

A composite key is two or more columns together as the uniqueness rule. PRIMARY KEY (roll, subject) on marks: the same student can have Maths and English, but not two Maths rows.

FOREIGN KEYs to a composite PK must include all those columns. Harder to join. An AUTO_INCREMENT id as PK plus UNIQUE (roll, subject) is often easier.

Trap: composite PK when a simple id would do. Trap: only putting roll as PK on marks so a student can have only one subject.

On the example next to this theory — Composite Key — keys and constraints protect data integrity; indexes speed lookups.

Diagram

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

Exam tip

PRIMARY KEY (roll, subject) — one row per student per subject.

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

DefKey made of more than one column.
Rule(roll, subject) unique together. Each column may repeat alone.
TrapUsing composite PK when a surrogate id is simpler.

SQL · Theory

Composite Key

← All stacks

Theory

74/439

Composite Key

A composite key is two or more columns together as the uniqueness rule. PRIMARY KEY (roll, subject) on marks: the same student can have Maths and English, but not two Maths rows.

FOREIGN KEYs to a composite PK must include all those columns. Harder to join. An AUTO_INCREMENT id as PK plus UNIQUE (roll, subject) is often easier.

Trap: composite PK when a simple id would do. Trap: only putting roll as PK on marks so a student can have only one subject.

On the example next to this theory — Composite Key — keys and constraints protect data integrity; indexes speed lookups.

Diagram

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

Exam tip

PRIMARY KEY (roll, subject) — one row per student per subject.

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

DefKey made of more than one column.
Rule(roll, subject) unique together. Each column may repeat alone.
TrapUsing composite PK when a surrogate id is simpler.

Composite Key

Composite Key — sample query

Short notes

Questions

Composite Key

Composite Key — sample query

Short notes

Questions