Primary Keys

Every durable table needs a stable way to identify one row from all others.

That is the job of the primary key.

Core Concepts

flowchart TD
  A[Table rows] --> B[Primary key column(s)]
  B --> C[Unique row identity]
  C --> D[Reliable references from other tables]

Primary key style	Example	Use case
Single-column key	id INTEGER PRIMARY KEY	Most app tables
Composite key	PRIMARY KEY (student_id, course_id)	Join/association tables

Code Examples

-- Single-column primary key.
CREATE TABLE users (
  user_id INTEGER PRIMARY KEY,
  name    TEXT NOT NULL
);

Expected output
Table users is created successfully.

-- Composite primary key example.
CREATE TABLE enrollments (
  student_id INTEGER,
  course_id  INTEGER,
  enrolled_on TEXT,
  PRIMARY KEY (student_id, course_id)
);

Expected output
Table enrollments is created with composite primary key.

-- Inspect key markers in metadata.
PRAGMA table_info(enrollments);

cid	name	type	notnull	dflt_value	pk
0	student_id	INTEGER	0	null	1
1	course_id	INTEGER	0	null	2
2	enrolled_on	TEXT	0	null	0

SQLite-Specific Nuances

SQLite Nuance

INTEGER PRIMARY KEY has special behavior in SQLite: it aliases the internal row identifier.

This makes it efficient and a strong default choice for many tables.

Common Pitfalls / Best Practices

Pitfall

Skipping primary keys in early prototypes and trying to retrofit identity later.

That often creates messy migrations and duplicate row problems.

Best Practice

Always define a primary key when creating a table, even for temporary learning schemas.

Quick Challenge

Create a departments table with:

• department_id as primary key
• name as required text

View Solution

CREATE TABLE departments (
  department_id INTEGER PRIMARY KEY,
  name          TEXT NOT NULL
);

Expected output
Table is created with a single-column primary key.