SQL PRIMARY KEY

A primary key uniquely identifies each row of a table. Every well-designed table has one.

Single-column primary key

CREATE TABLE customers (
  id   INT PRIMARY KEY,
  name VARCHAR(80)
);

Composite primary key

Useful for pure join tables — no surrogate ID needed:

CREATE TABLE memberships (
  user_id INT,
  team_id INT,
  PRIMARY KEY (user_id, team_id)
);

Surrogate vs natural

Modern apps almost always use surrogate keys, with a separate UNIQUE on the natural key.

Auto-increment

-- MySQL
CREATE TABLE t (id INT PRIMARY KEY AUTO_INCREMENT, ...);

-- PostgreSQL
CREATE TABLE t (id BIGSERIAL PRIMARY KEY, ...);   -- legacy
CREATE TABLE t (id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, ...);  -- standard SQL

-- SQL Server
CREATE TABLE t (id INT PRIMARY KEY IDENTITY(1,1), ...);

UUID as primary key?

Use UUID when IDs need to be generated client-side, or when you don't want to leak row counts. Trade-off: bigger storage, slower B-tree inserts than sequential integers.

Example

CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(80)
);

Exercise

Test yourself