Surrogate Key

A surrogate key is a system-generated identifier with no business meaning — it exists solely to serve as the Primary Key. Common implementations include auto-incrementing integers (SERIAL, AUTO_INCREMENT), UUIDs, and sequences.

-- PostgreSQL
CREATE TABLE products (
    id SERIAL PRIMARY KEY,        -- Surrogate key
    sku VARCHAR(20) UNIQUE,       -- Natural key (business identifier)
    name VARCHAR(255)
);
 
-- SQLite (implicit rowid)
CREATE TABLE products (
    id INTEGER PRIMARY KEY,       -- Aliases the built-in rowid
    sku TEXT UNIQUE,
    name TEXT
);

Surrogate vs Natural Key

Aspect	Surrogate	Natural
Stability	Never changes	May change (email, SSN policy)
Size	Small integer (4-8 bytes)	Variable (strings can be large)
Business meaning	None	Meaningful to users
Join performance	Fast (integer comparison)	Slower (string comparison)
Cross-system	Unique only within this DB	May be globally unique (ISBN, SSN)
Exposure	Safe to expose (meaningless)	May be sensitive (SSN)

Best practice: use a surrogate key as the Primary Key, enforce the natural key with a UNIQUE constraint as a logical key.

SQLite: ROWID vs AUTOINCREMENT

SQLite has a unique approach to surrogate keys:

• ROWID — Every table (unless WITHOUT ROWID) has an implicit rowid column. A 64-bit integer, automatically assigned and unique. Aliases: _rowid_, oid. Reuses deleted values.
• INTEGER PRIMARY KEY — Aliases the rowid. No separate column created.
• AUTOINCREMENT — Guarantees the value is always larger than any previously used value (never reuses deleted IDs). Slightly slower due to extra tracking in sqlite_sequence table.

-- AUTOINCREMENT: never reuses values
CREATE TABLE lessons (
    lid INTEGER PRIMARY KEY AUTOINCREMENT,
    lname TEXT NOT NULL
);
 
-- WITHOUT ROWID: must specify PK explicitly, no implicit rowid
CREATE TABLE config (
    key TEXT PRIMARY KEY,
    value TEXT
) WITHOUT ROWID;

Generally prefer application-generated keys or INTEGER PRIMARY KEY (rowid alias) over AUTOINCREMENT for better performance.