SQL Foreign Key | Academic Block

SQL FOREIGN KEY

Learn how to connect related SQL tables using FOREIGN KEY and maintain referential integrity between records.

What is a FOREIGN KEY?

A FOREIGN KEY is a column or group of columns that creates a relationship between two tables. It references a key in another table, usually its PRIMARY KEY.

In SQLite, foreign-key enforcement is enabled with PRAGMA foreign_keys = ON;. The examples below are designed to run directly in a SQLite-based SQL editor.

Basic FOREIGN KEY Example

Here, each delivery belongs to a warehouse. The warehouse_id column in the deliveries table references the warehouse_id primary key.

PRAGMA foreign_keys = ON;

DROP TABLE IF EXISTS deliveries;
DROP TABLE IF EXISTS warehouses;

CREATE TABLE warehouses (
  warehouse_id INTEGER PRIMARY KEY,
  warehouse_name TEXT NOT NULL
);

CREATE TABLE deliveries (
  delivery_id INTEGER PRIMARY KEY,
  warehouse_id INTEGER NOT NULL,
  item_name TEXT NOT NULL,
  quantity INTEGER,
  FOREIGN KEY (warehouse_id)
    REFERENCES warehouses(warehouse_id)
);

INSERT INTO warehouses VALUES
  (1, 'Central Warehouse'),
  (2, 'East Warehouse'),
  (3, 'West Warehouse');

INSERT INTO deliveries VALUES
  (101, 1, 'Steel Rods', 40),
  (102, 2, 'Copper Sheets', 25),
  (103, 1, 'Aluminum Tubes', 60);

SELECT
  deliveries.delivery_id,
  warehouses.warehouse_name,
  deliveries.item_name,
  deliveries.quantity
FROM deliveries
JOIN warehouses
  ON deliveries.warehouse_id = warehouses.warehouse_id
ORDER BY deliveries.delivery_id;

FOREIGN KEY with NOT NULL

Using NOT NULL with a foreign key ensures that every child record must be connected to a parent record.

PRAGMA foreign_keys = ON;

DROP TABLE IF EXISTS service_requests;
DROP TABLE IF EXISTS technicians;

CREATE TABLE technicians (
  technician_id INTEGER PRIMARY KEY,
  technician_name TEXT NOT NULL
);

CREATE TABLE service_requests (
  request_id INTEGER PRIMARY KEY,
  technician_id INTEGER NOT NULL,
  issue TEXT NOT NULL,
  FOREIGN KEY (technician_id)
    REFERENCES technicians(technician_id)
);

INSERT INTO technicians VALUES
  (11, 'Maya'),
  (12, 'Arjun'),
  (13, 'Tara');

INSERT INTO service_requests VALUES
  (201, 11, 'Air conditioner inspection'),
  (202, 13, 'Motor replacement'),
  (203, 12, 'Pump maintenance');

SELECT
  service_requests.request_id,
  technicians.technician_name,
  service_requests.issue
FROM service_requests
JOIN technicians
  ON service_requests.technician_id = technicians.technician_id
ORDER BY service_requests.request_id;

FOREIGN KEY with Product Categories

A product can reference a category using a foreign key. Multiple products can belong to the same category.

PRAGMA foreign_keys = ON;

DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS categories;

CREATE TABLE categories (
  category_id INTEGER PRIMARY KEY,
  category_name TEXT NOT NULL
);

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  category_id INTEGER NOT NULL,
  product_name TEXT NOT NULL,
  price REAL,
  FOREIGN KEY (category_id)
    REFERENCES categories(category_id)
);

INSERT INTO categories VALUES
  (1, 'Workshop Tools'),
  (2, 'Safety Equipment'),
  (3, 'Measuring Instruments');

INSERT INTO products VALUES
  (501, 1, 'Torque Wrench', 3200.00),
  (502, 2, 'Safety Helmet', 850.00),
  (503, 3, 'Digital Caliper', 1450.00),
  (504, 1, 'Bench Vise', 2800.00);

SELECT
  products.product_name,
  categories.category_name,
  products.price
FROM products
JOIN categories
  ON products.category_id = categories.category_id
ORDER BY products.product_id;

FOREIGN KEY with ON DELETE CASCADE

ON DELETE CASCADE automatically removes related child records when their parent record is deleted.

PRAGMA foreign_keys = ON;

DROP TABLE IF EXISTS lesson_notes;
DROP TABLE IF EXISTS lessons;

CREATE TABLE lessons (
  lesson_id INTEGER PRIMARY KEY,
  lesson_title TEXT NOT NULL
);

CREATE TABLE lesson_notes (
  note_id INTEGER PRIMARY KEY,
  lesson_id INTEGER NOT NULL,
  note_text TEXT NOT NULL,
  FOREIGN KEY (lesson_id)
    REFERENCES lessons(lesson_id)
    ON DELETE CASCADE
);

INSERT INTO lessons VALUES
  (1, 'SQL Basics'),
  (2, 'SQL Filtering'),
  (3, 'SQL Sorting');

INSERT INTO lesson_notes VALUES
  (101, 1, 'Learn SELECT syntax'),
  (102, 1, 'Practice simple queries'),
  (103, 2, 'Use WHERE to filter rows'),
  (104, 3, 'Use ORDER BY to sort results');

DELETE FROM lessons
WHERE lesson_id = 1;

SELECT *
FROM lesson_notes
ORDER BY note_id;

FOREIGN KEY with ON DELETE SET NULL

ON DELETE SET NULL keeps the child row but removes its parent reference when the parent record is deleted.

PRAGMA foreign_keys = ON;

DROP TABLE IF EXISTS articles;
DROP TABLE IF EXISTS editors;

CREATE TABLE editors (
  editor_id INTEGER PRIMARY KEY,
  editor_name TEXT NOT NULL
);

CREATE TABLE articles (
  article_id INTEGER PRIMARY KEY,
  editor_id INTEGER,
  title TEXT NOT NULL,
  FOREIGN KEY (editor_id)
    REFERENCES editors(editor_id)
    ON DELETE SET NULL
);

INSERT INTO editors VALUES
  (1, 'Nitin'),
  (2, 'Sara'),
  (3, 'Vivek');

INSERT INTO articles VALUES
  (101, 1, 'Introduction to Robotics'),
  (102, 2, 'How Solar Panels Work'),
  (103, 1, 'Understanding Gear Systems');

DELETE FROM editors
WHERE editor_id = 1;

SELECT
  article_id,
  editor_id,
  title
FROM articles
ORDER BY article_id;

Multiple FOREIGN KEYS in One Table

A table can contain multiple foreign keys. Each foreign key can reference a different table.

PRAGMA foreign_keys = ON;

DROP TABLE IF EXISTS equipment_bookings;
DROP TABLE IF EXISTS equipment;
DROP TABLE IF EXISTS employees;

CREATE TABLE employees (
  employee_id INTEGER PRIMARY KEY,
  employee_name TEXT NOT NULL
);

CREATE TABLE equipment (
  equipment_id INTEGER PRIMARY KEY,
  equipment_name TEXT NOT NULL
);

CREATE TABLE equipment_bookings (
  booking_id INTEGER PRIMARY KEY,
  employee_id INTEGER NOT NULL,
  equipment_id INTEGER NOT NULL,
  booking_date TEXT NOT NULL,
  FOREIGN KEY (employee_id)
    REFERENCES employees(employee_id),
  FOREIGN KEY (equipment_id)
    REFERENCES equipment(equipment_id)
);

INSERT INTO employees VALUES
  (1, 'Aisha'),
  (2, 'Ravi');

INSERT INTO equipment VALUES
  (101, '3D Printer'),
  (102, 'Laser Cutter');

INSERT INTO equipment_bookings VALUES
  (1001, 1, 101, '2026-08-20'),
  (1002, 2, 102, '2026-08-21');

SELECT
  equipment_bookings.booking_id,
  employees.employee_name,
  equipment.equipment_name,
  equipment_bookings.booking_date
FROM equipment_bookings
JOIN employees
  ON equipment_bookings.employee_id = employees.employee_id
JOIN equipment
  ON equipment_bookings.equipment_id = equipment.equipment_id
ORDER BY equipment_bookings.booking_id;

FOREIGN KEY with Self-Referencing Data

A foreign key can reference another row in the same table. This is useful for hierarchical data such as employees and their managers.

PRAGMA foreign_keys = ON;

DROP TABLE IF EXISTS staff;

CREATE TABLE staff (
  employee_id INTEGER PRIMARY KEY,
  employee_name TEXT NOT NULL,
  manager_id INTEGER,
  FOREIGN KEY (manager_id)
    REFERENCES staff(employee_id)
);

INSERT INTO staff VALUES
  (1, 'Anil', NULL),
  (2, 'Bhavna', 1),
  (3, 'Chetan', 1),
  (4, 'Diya', 2);

SELECT
  employee.employee_name AS employee,
  manager.employee_name AS manager
FROM staff AS employee
LEFT JOIN staff AS manager
  ON employee.manager_id = manager.employee_id
ORDER BY employee.employee_id;

FOREIGN KEY with Composite Keys

A foreign key can reference multiple columns when the parent table uses a composite primary key.

PRAGMA foreign_keys = ON;

DROP TABLE IF EXISTS attendance;
DROP TABLE IF EXISTS class_schedule;

CREATE TABLE class_schedule (
  class_id INTEGER,
  room_id INTEGER,
  subject TEXT NOT NULL,
  PRIMARY KEY (class_id, room_id)
);

CREATE TABLE attendance (
  attendance_id INTEGER PRIMARY KEY,
  class_id INTEGER NOT NULL,
  room_id INTEGER NOT NULL,
  student_name TEXT NOT NULL,
  FOREIGN KEY (class_id, room_id)
    REFERENCES class_schedule(class_id, room_id)
);

INSERT INTO class_schedule VALUES
  (10, 201, 'Physics'),
  (10, 202, 'Mathematics'),
  (20, 301, 'Chemistry');

INSERT INTO attendance VALUES
  (1001, 10, 201, 'Ishaan'),
  (1002, 10, 202, 'Mira'),
  (1003, 20, 301, 'Kunal');

SELECT
  attendance.attendance_id,
  attendance.student_name,
  class_schedule.subject
FROM attendance
JOIN class_schedule
  ON attendance.class_id = class_schedule.class_id
 AND attendance.room_id = class_schedule.room_id
ORDER BY attendance.attendance_id;

Checking FOREIGN KEY Information

SQLite provides PRAGMA foreign_key_list() to inspect the foreign keys defined on a table.

PRAGMA foreign_keys = ON;

DROP TABLE IF EXISTS reservations;
DROP TABLE IF EXISTS rooms;

CREATE TABLE rooms (
  room_id INTEGER PRIMARY KEY,
  room_name TEXT NOT NULL
);

CREATE TABLE reservations (
  reservation_id INTEGER PRIMARY KEY,
  room_id INTEGER NOT NULL,
  guest_name TEXT NOT NULL,
  FOREIGN KEY (room_id)
    REFERENCES rooms(room_id)
);

SELECT
  id,
  seq,
  "table",
  "from",
  "to"
FROM pragma_foreign_key_list('reservations');

FOREIGN KEY with UPDATE CASCADE

ON UPDATE CASCADE automatically updates matching child references when the parent key changes.

PRAGMA foreign_keys = ON;

DROP TABLE IF EXISTS shipments;
DROP TABLE IF EXISTS hubs;

CREATE TABLE hubs (
  hub_id INTEGER PRIMARY KEY,
  hub_name TEXT NOT NULL
);

CREATE TABLE shipments (
  shipment_id INTEGER PRIMARY KEY,
  hub_id INTEGER NOT NULL,
  package_name TEXT NOT NULL,
  FOREIGN KEY (hub_id)
    REFERENCES hubs(hub_id)
    ON UPDATE CASCADE
);

INSERT INTO hubs VALUES
  (501, 'North Hub'),
  (502, 'South Hub');

INSERT INTO shipments VALUES
  (9001, 501, 'Machine Parts'),
  (9002, 502, 'Electronic Components');

UPDATE hubs
SET hub_id = 503
WHERE hub_id = 501;

SELECT
  shipments.shipment_id,
  shipments.hub_id,
  hubs.hub_name,
  shipments.package_name
FROM shipments
JOIN hubs
  ON shipments.hub_id = hubs.hub_id
ORDER BY shipments.shipment_id;

FOREIGN KEY vs PRIMARY KEY

Feature	PRIMARY KEY	FOREIGN KEY
Purpose	Uniquely identifies a row	Connects related tables
Duplicate values	Not allowed	Allowed
NULL	Not allowed	Allowed unless NOT NULL is specified
Relationship	Can be referenced	References another key

Important Points

A FOREIGN KEY creates a relationship between tables.
It normally references a PRIMARY KEY in another table.
Foreign keys can contain duplicate values.
A foreign key may contain NULL unless NOT NULL is specified.
SQLite foreign-key enforcement should be enabled with PRAGMA foreign_keys = ON.
ON DELETE CASCADE can remove dependent records automatically.
ON DELETE SET NULL can preserve child records while removing their parent reference.
A table can contain multiple foreign keys.
A foreign key can reference the same table or multiple columns.

Best Practices

Use clear names such as customer_id, product_id, or department_id.
Use NOT NULL when every child record must have a parent.
Enable foreign-key enforcement when using SQLite.
Choose cascade actions carefully because they can modify multiple rows.
Use foreign keys to maintain reliable relationships between tables.
Keep the data types of related key columns compatible.

The FOREIGN KEY constraint connects related tables and protects referential integrity. It ensures that relationships between records remain valid as your database grows.

Ctrl + Enter to run SQL code Esc to close editor

🧪 Test Your SQL Code

Edit the SQL code on the left and click “Run Code” to see the result on the right.

📝 SQL Code

👁️ Preview (query result)

Click “Run Code” to see the result here.