SQL Foreign Keys and Relationships — Connecting Tables in Relational Databases

An exam result should not claim to belong to a student whose roll number does not exist.

A foreign key restricts child key values to permitted references in a parent relation, subject to the schema’s nullability and configured relationship actions.

The child stores the parent key. The database checks the relationship when relevant writes occur. ON DELETE and ON UPDATE actions specify whether parent changes are rejected, cascaded or handled in another supported way. A many-to-many relationship uses a junction table with references to both parents.

Related concepts: LEFT JOIN and Data Constraints and Transactions.

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.

SQL · portable concepts; preserved examples identify their engine

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.

SQL · portable concepts; preserved examples identify their engine

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

SQL · portable concepts; preserved examples identify their engine

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)
);

SQL · portable concepts; preserved examples identify their engine

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
);

• 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

• 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!)

Nullable child keys can permit missing relationships unless NOT NULL is required. Cascading deletion has a real scope and should be chosen intentionally. SQLite requires foreign-key enforcement to be enabled per connection in typical setups; MySQL enforcement depends on a supporting engine such as InnoDB.

Identify the deliberately orphaned result in the practice fixture, before designing an enforced relationship.

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');
CREATE TABLE practice_results (roll_no INTEGER, subject VARCHAR(30), marks INTEGER);
INSERT INTO practice_results VALUES (1,'Maths',85),(3,'Maths',85),(4,'Maths',92);

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');
CREATE TABLE practice_results (roll_no INTEGER, subject VARCHAR(30), marks INTEGER);
INSERT INTO practice_results VALUES (1,'Maths',85),(3,'Maths',85),(4,'Maths',92);

SQL · this practice variation runs with SQLite 3; the preserved examples retain their stated dialect

SELECT r.roll_no FROM practice_results AS r LEFT JOIN practice_students AS s ON s.roll_no = r.roll_no WHERE s.roll_no IS NULL ORDER BY r.roll_no;

The sample results relation intentionally has no enforced foreign key so unmatched join behavior can be learned. An appropriate enforced, non-null parent reference would reject that orphan insertion.

A foreign key restricts child key values to permitted references in a parent relation, subject to the schema’s nullability and configured relationship actions.

The sample results relation intentionally has no enforced foreign key so unmatched join behavior can be learned. An appropriate enforced, non-null parent reference would reject that orphan insertion.

Continue your understanding with LEFT JOIN and Data Constraints and Transactions.

Source for the engine-specific behavior: MySQL 8.4 reference documentation.