Composite Key — SQL Technical Interview Questions & Answers

Composite Key

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.

Technical Interview Tip: PRIMARY KEY (roll, subject) — one row per student per subject.

Code Examples for Composite Key

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.

How to Explain Composite Key to an Interviewer

When composite key?

Uniqueness needs two columns Example: roll + subject