How foreign keys work

Reference another row

A foreign key value points to a candidate key—usually the primary key—of another table.

CREATE TABLE enrollments (
  student_id INTEGER NOT NULL,
  course_id INTEGER NOT NULL,
  FOREIGN KEY (student_id) REFERENCES students(id),
  FOREIGN KEY (course_id) REFERENCES courses(id)
);

Protect integrity

The constraint prevents references to rows that do not exist and can define what happens when a referenced row changes or is deleted.

Choose delete behavior

• RESTRICT or NO ACTION rejects unsafe deletion
• CASCADE removes dependent rows
• SET NULL clears an optional reference
• Choose deliberately; cascading deletion can remove much data

Enable SQLite checks

SQLite applications should enable enforcement for each connection with PRAGMA foreign_keys = ON;. PostgreSQL and MySQL enforce supported foreign keys by default.

PRAGMA foreign_keys = ON;