Primary keys

A primary key uniquely identifies each row. Single-column integer keys are common; composite keys appear when natural business identifiers combine (order_id + line_no).

Single-column key

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  username TEXT NOT NULL UNIQUE
);

INSERT INTO users (username) VALUES ('ada');
SELECT last_insert_rowid();  -- SQLite auto-increment helper

Practice: Run DDL in a fresh SQLite file or DB Fiddle schema pane. Drop test tables when experimenting.

Composite primary key

CREATE TABLE order_items (
  order_id INTEGER NOT NULL,
  line_no INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  qty INTEGER NOT NULL,
  PRIMARY KEY (order_id, line_no)
);

Both columns together must be unique; either alone may repeat.

Natural vs surrogate keys

Surrogate keys (auto id) stay stable when business data changes. Natural keys (email, ISBN) encode domain meaning but may change or collide—teams often still add surrogate ids.

Important interview questions and answers

1. Q: Can a table have two primary keys?
   A: No—one primary key constraint, which may span multiple columns.
2. Q: UNIQUE vs PRIMARY KEY?
   A: Both enforce uniqueness; PRIMARY KEY implies NOT NULL and is the main row identifier.

Self-check

1. When is a composite primary key appropriate?
2. Why do ORMs often prefer surrogate integer ids?

Tip: Surrogate integer keys simplify ORMs; natural keys still get UNIQUE constraints when needed.