SQLite Primary and Foreign Keys

Keys define relationships between tables. A primary key uniquely identifies each row in a table. A foreign key links a column in one table to the primary key of another, creating a connection between them and preventing orphaned data.

Primary Key — The Unique Identifier

Think of a primary key like a student ID number at a university. Two students can share the same name, but each student card carries a unique number. That number is the primary key — it pinpoints exactly one record.

students
────────────────────────────────────
id  (PK) │ name    │ dept
─────────┼─────────┼──────────
1        │ Alice   │ Engineering
2        │ Bob     │ Marketing
3        │ Carol   │ Engineering
4        │ Alice   │ HR          ← same name as row 1, different id

Declaring a Primary Key

-- INTEGER PRIMARY KEY: auto-increments
CREATE TABLE students (
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT
);

-- TEXT PRIMARY KEY: you supply the value
CREATE TABLE countries (
  code TEXT PRIMARY KEY,   -- e.g. 'US', 'IN', 'DE'
  name TEXT NOT NULL
);

-- Composite Primary Key: combination must be unique
CREATE TABLE enrollments (
  student_id INTEGER,
  course_id  INTEGER,
  grade      REAL,
  PRIMARY KEY (student_id, course_id)
);

ROWID and INTEGER PRIMARY KEY

SQLite secretly assigns every row a 64-bit integer called rowid. When you declare an INTEGER PRIMARY KEY column, it becomes an alias for rowid — they point to the exact same value.

INSERT INTO students (name) VALUES ('Alice');
INSERT INTO students (name) VALUES ('Bob');

SELECT rowid, id, name FROM students;
rowid │ id │ name
──────┼────┼───────
1     │ 1  │ Alice   ← rowid and id are identical
2     │ 2  │ Bob

AUTOINCREMENT vs INTEGER PRIMARY KEY

-- INTEGER PRIMARY KEY (recommended)
-- Reuses gaps only if the deleted id was the largest ever
CREATE TABLE t1 (id INTEGER PRIMARY KEY, val TEXT);

-- AUTOINCREMENT (stricter, never reuses any deleted id)
CREATE TABLE t2 (id INTEGER PRIMARY KEY AUTOINCREMENT, val TEXT);

For most use cases, INTEGER PRIMARY KEY is sufficient and faster.
AUTOINCREMENT is only needed when reusing IDs would be a problem
(e.g. audit trails where deleted IDs must never reappear).

Foreign Key — Linking Tables

A foreign key column in one table references the primary key in another. It enforces referential integrity: you cannot add a row that references a non-existent parent record, and you cannot delete a parent row that still has children pointing to it.

RELATIONSHIP DIAGRAM
────────────────────────────────────────────────────────
students                    courses
─────────────────────       ──────────────────────
id  (PK) ◄──────────┐       id  (PK)
name                │       title
dept                │
                    │       enrollments
                    │       ────────────────────
                    └────── student_id  (FK → students.id)
                            course_id   (FK → courses.id)
                            grade

Creating Tables with Foreign Keys

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

CREATE TABLE employees (
  id      INTEGER PRIMARY KEY,
  name    TEXT NOT NULL,
  dept_id INTEGER REFERENCES departments(id)
  --                    ↑ foreign key declaration
);

Enabling Foreign Key Enforcement

Foreign key enforcement is OFF by default in SQLite. You must turn it on each session.

PRAGMA foreign_keys = ON;

-- Now FK constraints are enforced:
INSERT INTO departments VALUES (1, 'Engineering');

-- Works — dept_id 1 exists:
INSERT INTO employees (name, dept_id) VALUES ('Alice', 1);

-- FAILS — dept_id 99 does not exist:
INSERT INTO employees (name, dept_id) VALUES ('Bob', 99);
-- Error: FOREIGN KEY constraint failed

ON DELETE and ON UPDATE Actions

Define what happens to child rows when a parent row is deleted or its primary key changes.

CREATE TABLE employees (
  id      INTEGER PRIMARY KEY,
  name    TEXT,
  dept_id INTEGER REFERENCES departments(id)
            ON DELETE SET NULL    -- clear dept_id if dept deleted
            ON UPDATE CASCADE     -- update dept_id if dept id changes
);

OPTIONS:
─────────────────────────────────────────────────────────────
NO ACTION     Default: error if parent is deleted while child exists
RESTRICT      Same as NO ACTION but checked immediately
SET NULL      Set FK column to NULL when parent is deleted
SET DEFAULT   Set FK column to its DEFAULT when parent is deleted
CASCADE       Delete/update child rows when parent is deleted/updated

Checking Foreign Keys in PRAGMA

-- See all foreign keys in a table
PRAGMA foreign_key_list(employees);

id │ seq │ table       │ from    │ to │ on_update │ on_delete
───┼─────┼─────────────┼─────────┼────┼───────────┼──────────
0  │ 0   │ departments │ dept_id │ id │ CASCADE   │ SET NULL

Key Takeaways

• PRIMARY KEY uniquely identifies every row; duplicate values are rejected
• INTEGER PRIMARY KEY auto-assigns incrementing numbers
• FOREIGN KEY links a column to a primary key in another table
• Run PRAGMA foreign_keys = ON each session to enforce FK constraints
• Use ON DELETE CASCADE or ON DELETE SET NULL to control child behavior when a parent row is removed