FOREIGN KEY constraint in SQL - Step-by-Step Execution

Concept Flow - FOREIGN KEY constraint

Create Parent Table

↓

Create Child Table with FOREIGN KEY

↓

Insert Data into Parent

↓

Insert Data into Child

↓

Check FOREIGN KEY Validity

↓

Allow

This flow shows how a foreign key links child table data to parent table data, allowing inserts only if the referenced parent data exists.

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 as parent and Employees as child with a foreign key linking DeptID.

Execution Table

Step	Action	Table	Data Inserted	Foreign Key Check	Result
1	Insert	Departments	(1, 'HR')	N/A	Success
2	Insert	Departments	(2, 'Sales')	N/A	Success
3	Insert	Employees	(101, 'Alice', 1)	DeptID=1 exists in Departments	Success
4	Insert	Employees	(102, 'Bob', 3)	DeptID=3 does NOT exist in Departments	Error: Foreign Key Violation
5	Insert	Employees	(103, 'Carol', 2)	DeptID=2 exists in Departments	Success

💡 Step 4 fails because DeptID=3 is not present in Departments, violating the foreign key constraint.

Variable Tracker

Table	Start	After Step 1	After Step 2	After Step 3	After Step 4	After Step 5
Departments	empty	[(1, 'HR')]	[(1, 'HR'), (2, 'Sales')]	[(1, 'HR'), (2, 'Sales')]	[(1, 'HR'), (2, 'Sales')]	[(1, 'HR'), (2, 'Sales')]
Employees	empty	empty	empty	[(101, 'Alice', 1)]	[(101, 'Alice', 1)] (insert failed)	[(101, 'Alice', 1), (103, 'Carol', 2)]

Key Moments - 2 Insights

Why does the insert fail at step 4 even though the Employees table is empty?

Because the foreign key DeptID=3 does not exist in the Departments table, violating the foreign key constraint as shown in execution_table row 4.

Can you insert a row into Employees with a DeptID that is NULL?

Yes, if the foreign key column allows NULLs, because NULL means no reference, so the constraint does not check for a matching parent row.

Visual Quiz - 3 Questions

Look at the execution_table, what is the result of inserting (102, 'Bob', 3) into Employees at step 4?

A Ignored silently

B Success

C Error: Foreign Key Violation

D Inserted with NULL DeptID

💡 Hint

Check the Foreign Key Check and Result columns in execution_table row 4.

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

A Step 3

B Step 2

C Step 1

D Step 4

💡 Hint

Look at the Data Inserted and Result columns in execution_table.

If the Departments table had no rows, what would happen when inserting (101, 'Alice', 1) into Employees?

A Insert succeeds

B Insert fails due to foreign key violation

C Insert succeeds but DeptID is set to NULL

D Insert succeeds but with warning

💡 Hint

Refer to the foreign key rule shown in concept_flow and execution_table step 3.

Concept Snapshot

FOREIGN KEY constraint links a child table column to a parent table's primary key.
It ensures child rows reference existing parent rows.
Inserts or updates violating this are rejected.
NULL values are allowed if the foreign key column permits.
Syntax: FOREIGN KEY (child_col) REFERENCES parent_table(parent_col).

Full Transcript

The FOREIGN KEY constraint connects a column in one table (child) to a primary key in another table (parent). This ensures data integrity by allowing only values in the child column that exist in the parent. The flow starts by creating the parent table, then the child table with the foreign key. When inserting data, the database checks if the referenced parent value exists. If yes, the insert succeeds; if not, it fails with an error. For example, inserting an employee with a department ID that does not exist in Departments causes a foreign key violation error. NULL values are allowed if the foreign key column is nullable, meaning no reference is made. This constraint helps keep related data consistent across tables.