Foreign key linking mental model in SQL - Step-by-Step Execution

Concept Flow - Foreign key linking mental model

Create Parent Table

↓

Create Child Table with Foreign Key

↓

Insert Data into Parent

↓

Insert Data into Child

↓

Foreign Key Checks: Does Child value exist in Parent?

Yes/No↓

Allow Insert

↓

Referential Integrity Maintained

This flow shows how a foreign key links a child table to a parent table, ensuring child values exist in the parent to keep data consistent.

Execution Sample

SQL

CREATE TABLE Departments (
  DeptID INT PRIMARY KEY,
  DeptName VARCHAR(50)
);

CREATE TABLE Employees (
  EmpID INT PRIMARY KEY,
  EmpName VARCHAR(50),
  DeptID INT,
  FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)
);

Creates two tables: Departments (parent) and Employees (child) with a foreign key linking Employees.DeptID to Departments.DeptID.

Execution Table

Step	Action	Table	Value Inserted	Foreign Key Check	Result
1	Insert into Departments	Departments	DeptID=1, DeptName='HR'	N/A	Success
2	Insert into Departments	Departments	DeptID=2, DeptName='Sales'	N/A	Success
3	Insert into Employees	Employees	EmpID=101, EmpName='Alice', DeptID=1	Check DeptID=1 in Departments	Exists - Success
4	Insert into Employees	Employees	EmpID=102, EmpName='Bob', DeptID=3	Check DeptID=3 in Departments	Does not exist - Fail
5	Insert into Employees	Employees	EmpID=103, EmpName='Carol', DeptID=2	Check DeptID=2 in Departments	Exists - Success

💡 Step 4 fails because DeptID=3 does not exist in Departments, enforcing foreign key constraint.

Variable Tracker

Table	Start	After Step 1	After Step 2	After Step 3	After Step 4	After Step 5
Departments	Empty	[{DeptID:1, DeptName:'HR'}]	[{DeptID:1, DeptName:'HR'}, {DeptID:2, DeptName:'Sales'}]	[{DeptID:1, DeptName:'HR'}, {DeptID:2, DeptName:'Sales'}]	[{DeptID:1, DeptName:'HR'}, {DeptID:2, DeptName:'Sales'}]	[{DeptID:1, DeptName:'HR'}, {DeptID:2, DeptName:'Sales'}]
Employees	Empty	Empty	Empty	[{EmpID:101, EmpName:'Alice', DeptID:1}]	[No change - insert failed]	[{EmpID:101, EmpName:'Alice', DeptID:1}, {EmpID:103, EmpName:'Carol', DeptID:2}]

Key Moments - 2 Insights

Why did the insert fail at step 4 even though the data looks correct?

Because the foreign key DeptID=3 does not exist in the parent Departments table, the database rejects the insert to keep data consistent. See execution_table row 4.

Does the foreign key column in Employees have to be a primary key?

No, the foreign key column in Employees references the primary key in Departments but does not have to be a primary key itself. It just must match existing values in Departments. See concept_flow.

Visual Quiz - 3 Questions

Look at the execution_table, what is the result of inserting EmpID=102 with DeptID=3?

A Fail, because DeptID=3 does not exist in Departments

B Success, because DeptID=3 is valid

C Success, because foreign keys are optional

D Fail, because EmpID=102 already exists

💡 Hint

Check execution_table row 4 for foreign key check and result.

At which step does the Employees table first get a new row inserted?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Look at execution_table rows for Employees insert success.

If we insert a new Department with DeptID=3 before step 4, what would happen at step 4?

A Insert would still fail due to foreign key

B Insert would succeed because DeptID=3 exists

C Insert would fail because DeptID=3 is duplicate

D Insert would succeed but foreign key ignored

💡 Hint

Foreign key checks if referenced DeptID exists in Departments table.

Concept Snapshot

Foreign key links a child table column to a parent table's primary key.
It ensures child values exist in parent to keep data consistent.
Inserts fail if foreign key value not found in parent.
Foreign key column need not be primary key itself.
Used to maintain referential integrity between tables.

Full Transcript

This visual execution shows how foreign keys link two tables in a database. First, a parent table Departments is created with a primary key DeptID. Then a child table Employees is created with a DeptID column referencing Departments.DeptID as a foreign key. When inserting data, Departments accepts new rows freely. Employees can only insert rows if the DeptID value exists in Departments. For example, inserting an employee with DeptID=1 succeeds because Departments has DeptID=1. But inserting with DeptID=3 fails because Departments does not have that value. This enforces referential integrity, preventing orphaned child rows. The variable tracker shows how tables grow after each step. Key moments clarify why inserts fail and foreign key rules. The quiz tests understanding of these steps and constraints. This mental model helps beginners see how foreign keys keep data linked and consistent across tables.