Foreign Keys and Relationships
Connecting Tables in Relational Databases

Introduction

In the real world, data is connected. Students enroll in subjects, teachers teach courses, and parents contact the school. In relational databases, these connections are managed using foreign keys. A foreign key links one table to another and keeps the relationships between data accurate and trustworthy.

What is a Foreign Key?

A foreign key is a column (or set of columns) in one table that refers to the primary key of another table. It establishes a link between two tables and enforces referential integrity — meaning you can't insert unrelated or orphaned data.

Basic School Example

1. Create students table

CREATE TABLE students (
  roll_no INT PRIMARY KEY,
  name VARCHAR(50),
  class VARCHAR(10)
);

2. Create marks table with a foreign key

CREATE TABLE marks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  roll_no INT,
  subject VARCHAR(30),
  marks INT,
  FOREIGN KEY (roll_no) REFERENCES students(roll_no)
);

This means every row in marks must match an existing roll_no in students.

Inserting Valid and Invalid Data

Valid insert:

INSERT INTO students VALUES (1, 'Aarav Sharma', '10A');
INSERT INTO marks (roll_no, subject, marks) VALUES (1, 'Maths', 85);

Invalid insert:

-- Error: roll_no 5 doesn't exist in students table
INSERT INTO marks (roll_no, subject, marks) VALUES (5, 'Science', 90);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails

Types of Relationships Using Foreign Keys

1. One-to-Many (Most Common)

A student can have many marks entries, but each mark entry belongs to one student.

2. Many-to-One

Multiple students can belong to the same class or be assigned to the same teacher.

3. Many-to-Many (via Junction Table)

Students can enroll in many subjects, and subjects can have many students.

CREATE TABLE enrollments (
  roll_no INT,
  subject_code VARCHAR(10),
  PRIMARY KEY (roll_no, subject_code),
  FOREIGN KEY (roll_no) REFERENCES students(roll_no),
  FOREIGN KEY (subject_code) REFERENCES subjects(subject_code)
);

ON DELETE and ON UPDATE Options

Foreign keys can respond to changes in the parent table using:

• ON DELETE CASCADE: Deletes dependent rows if parent is deleted
• ON DELETE SET NULL: Sets FK to NULL if parent is deleted
• ON DELETE RESTRICT: Prevents deletion if child rows exist

CREATE TABLE marks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  roll_no INT,
  subject VARCHAR(30),
  marks INT,
  FOREIGN KEY (roll_no) REFERENCES students(roll_no)
    ON DELETE CASCADE
);

Real-World School Use Cases

• students → marks: Track subject-wise performance
• students → attendance: Link attendance records to students
• subjects → teachers: Assign subjects to teachers
• students ↔ subjects: Use a join table for student enrollments

Best Practices

• Always index foreign key columns for better performance
• Name constraints explicitly if needed (for debugging)
• Use CASCADE only when deletions should flow down
• Plan your relationships before schema creation (ER diagrams help!)

Summary

Foreign Keys are the glue that binds your tables together. They prevent meaningless or broken data and allow you to reflect real-world relationships — like a student's roll number tying all academic records together. In any relational database, using foreign keys smartly is the key to data consistency.

What’s Next?

Coming up: SQL Best Practices — coding habits that make your queries cleaner, safer, and easier to maintain.