FOREIGN KEY

A FOREIGN KEY is a column that references a PRIMARY KEY in another table. It creates a relationship between tables and ensures that you cannot have orphaned data.

Why Foreign Keys?

Imagine an enrollments table where student_id = 99. If there is no student with id 99 in the students table, that enrollment is meaningless. Foreign keys prevent this.

-- Enable foreign key support in SQLite
PRAGMA foreign_keys = ON;

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

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept_id INTEGER,
  FOREIGN KEY (dept_id) REFERENCES departments(id)
);

INSERT INTO departments VALUES (1, 'Engineering');
INSERT INTO departments VALUES (2, 'Marketing');

-- This works:
INSERT INTO employees VALUES (1, 'Bilal', 1);
INSERT INTO employees VALUES (2, 'Amina', 2);

-- This will fail because department 99 does not exist:
INSERT INTO employees VALUES (3, 'Musa', 99);

ON DELETE Actions

What happens when you delete a department that has employees? You can control this with ON DELETE:

CREATE TABLE employees2 (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept_id INTEGER,
  FOREIGN KEY (dept_id) REFERENCES departments(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

-- CASCADE means: if a department is deleted, delete all employees in it too
-- SET NULL means: set dept_id to NULL if the department is deleted
-- RESTRICT means: prevent deletion if employees still exist
-- NO ACTION means: do nothing (might cause errors later)

CASCADE is convenient but dangerous. SET NULL is safer if you want to keep the records. RESTRICT is the strictest — it forces you to clean up manually.