Foreign key linking mental model in SQL

Introduction

A foreign key connects two tables by linking a column in one table to a column in another. This helps keep data organized and related.

When you want to connect customer orders to the customers who made them.

When you need to link employees to the departments they work in.

When tracking which books belong to which authors in a library database.

When ensuring that product reviews are tied to existing products.

When you want to prevent deleting a record that is still used in another table.

Syntax

SQL

CREATE TABLE ChildTable (
  id INT PRIMARY KEY,
  parent_id INT,
  FOREIGN KEY (parent_id) REFERENCES ParentTable(id)
);

The foreign key column (parent_id) must match the data type of the referenced column (id).

The referenced column is usually a primary key in the parent table.

Examples

This links each order to a customer by customer_id.

SQL

CREATE TABLE Orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

Each employee is linked to a department.

SQL

CREATE TABLE Employees (
  emp_id INT PRIMARY KEY,
  dept_id INT,
  FOREIGN KEY (dept_id) REFERENCES Departments(dept_id)
);

Adds a foreign key to an existing Reviews table linking to Products.

SQL

ALTER TABLE Reviews
ADD CONSTRAINT fk_product
FOREIGN KEY (product_id) REFERENCES Products(product_id);

Sample Program

This example creates two tables, Authors and Books, linked by author_id. It inserts authors and books, then shows book titles with their authors.

SQL

CREATE TABLE Authors (
  author_id INT PRIMARY KEY,
  name VARCHAR(100)
);

CREATE TABLE Books (
  book_id INT PRIMARY KEY,
  title VARCHAR(100),
  author_id INT,
  FOREIGN KEY (author_id) REFERENCES Authors(author_id)
);

INSERT INTO Authors VALUES (1, 'Jane Austen');
INSERT INTO Authors VALUES (2, 'Mark Twain');

INSERT INTO Books VALUES (101, 'Pride and Prejudice', 1);
INSERT INTO Books VALUES (102, 'Adventures of Huckleberry Finn', 2);

SELECT Books.title, Authors.name FROM Books
JOIN Authors ON Books.author_id = Authors.author_id;

Important Notes

Foreign keys help keep data consistent by preventing invalid links.

Trying to insert a child record with a non-existing parent_id will cause an error.

Deleting a parent record that has children may be blocked or cascade depending on settings.

Summary

Foreign keys connect tables by linking columns.

They keep data organized and prevent mistakes.

They are essential for relational database design.