Referential integrity enforcement in SQL - Step-by-Step Execution

Concept Flow - Referential integrity enforcement

Start: Insert/Update/Delete in Child Table

↓

Check Foreign Key Value Exists in Parent Table?

↓

Allow Operation

↓

Operation Done

When you change data in a table with a foreign key, the database checks if the related value exists in the parent table. If yes, it allows the change; if no, it rejects it to keep data correct.

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

INSERT INTO Departments VALUES (1, 'Sales');
INSERT INTO Employees VALUES (101, 'Alice', 1);

This code creates two tables with a foreign key from Employees to Departments, then inserts a department and an employee linked to it.

Execution Table

Step	Operation	Foreign Key Check	Result	Notes
1	Insert DeptID=1 into Departments	No FK check needed	Success	Parent table insert always allowed
2	Insert EmpID=101, DeptID=1 into Employees	Check DeptID=1 exists in Departments	Success	DeptID=1 found, insert allowed
3	Insert EmpID=102, DeptID=2 into Employees	Check DeptID=2 exists in Departments	Fail	DeptID=2 not found, insert rejected
4	Delete DeptID=1 from Departments	Check Employees referencing DeptID=1	Fail	Child rows exist, delete rejected
5	Update EmpID=101 DeptID to 3	Check DeptID=3 exists in Departments	Fail	DeptID=3 not found, update rejected
6	Update EmpID=101 DeptID to NULL	Depends on FK constraint (if allowed)	Success or Fail	If FK allows NULL, update allowed; else rejected

💡 Operations stop when foreign key checks fail to maintain referential integrity.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	After Step 5	After Step 6
Departments	empty	[(1, 'Sales')]	[(1, 'Sales')]	[(1, 'Sales')]	[(1, 'Sales')]	[(1, 'Sales')]	[(1, 'Sales')]
Employees	empty	empty	[(101, 'Alice', 1)]	unchanged	unchanged	unchanged	depends on NULL allowance

Key Moments - 3 Insights

Why does inserting an employee with DeptID=2 fail even if Employees table is empty?

Because DeptID=2 does not exist in Departments, the foreign key check fails (see execution_table step 3). The database rejects the insert to keep data consistent.

Why can't we delete a department if employees reference it?

Deleting a department with existing employees referencing it breaks referential integrity, so the database rejects the delete (see execution_table step 4).

What happens if we update an employee's DeptID to a value not in Departments?

The update is rejected because the new DeptID does not exist in the parent table, violating referential integrity (see execution_table step 5).

Visual Quiz - 3 Questions

Look at the execution_table, what happens when inserting an employee with DeptID=1 at step 2?

A Insert fails because DeptID=1 is missing

B Insert is delayed until DeptID=1 is added

C Insert succeeds because DeptID=1 exists

D Insert succeeds without checking DeptID

💡 Hint

Check the 'Foreign Key Check' and 'Result' columns at step 2 in execution_table

At which step does the database reject an operation due to missing DeptID in Departments?

A Step 3

B Step 1

C Step 4

D Step 2

💡 Hint

Look for 'Fail' results with missing DeptID in execution_table

If the foreign key allowed NULLs, what would happen at step 6 when updating DeptID to NULL?

A Update rejected because NULL is not allowed

B Update succeeds if FK allows NULL

C Update causes deletion of employee

D Update ignored by database

💡 Hint

See notes in step 6 of execution_table about NULL allowance

Concept Snapshot

Referential integrity means foreign keys must match existing parent keys.
When inserting or updating child rows, DB checks parent table for matching key.
If no match, operation is rejected to keep data consistent.
Deleting parent rows is blocked if children reference them.
NULL foreign keys may be allowed depending on constraint settings.

Full Transcript

Referential integrity enforcement ensures that foreign key values in a child table always match existing primary key values in a parent table. When you insert or update a row in the child table, the database checks if the foreign key value exists in the parent table. If it does, the operation proceeds; if not, the database rejects it to prevent broken links. Similarly, deleting a parent row is blocked if child rows reference it. This keeps data consistent and prevents orphaned records. Some foreign keys allow NULL values, which means the child row can have no parent reference if allowed by the constraint. The execution table shows examples of inserts, updates, and deletes with their foreign key checks and results, illustrating how referential integrity is enforced step-by-step.

Practice

1. What is the main purpose of referential integrity in a database?

easy

A. To speed up query execution

B. To ensure relationships between tables remain consistent

C. To store large amounts of data efficiently

D. To create backup copies of the database

Solution

1. Step 1: Understand referential integrity concept

   Referential integrity ensures that foreign keys in one table correctly reference existing rows in another table.

2. Step 2: Identify the main purpose

   This prevents orphan records and keeps data relationships consistent and safe.

3. Final Answer:

   To ensure relationships between tables remain consistent -> Option B

4. Quick Check:

   Referential integrity = consistent relationships [OK]

Hint: Referential integrity means keeping table links correct [OK]

Common Mistakes:

• Confusing referential integrity with performance optimization
• Thinking it creates backups
• Assuming it stores data efficiently

2. Which SQL statement correctly defines a foreign key with referential integrity enforcement?

easy

A. INSERT INTO Orders (OrderID, CustomerID) VALUES (1, 100);

B. CREATE TABLE Orders (OrderID INT, CustomerID INT PRIMARY KEY);

C. ALTER TABLE Orders ADD FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);

D. SELECT * FROM Orders WHERE CustomerID = Customers.CustomerID;

Solution

1. Step 1: Identify foreign key syntax

   The correct syntax to add a foreign key is using ALTER TABLE with ADD FOREIGN KEY referencing another table's column.

2. Step 2: Check each option

   ALTER TABLE Orders ADD FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID); correctly adds a foreign key constraint. CREATE TABLE Orders (OrderID INT, CustomerID INT PRIMARY KEY); wrongly sets CustomerID as primary key without foreign key. INSERT INTO Orders (OrderID, CustomerID) VALUES (1, 100); is an insert, not a constraint. SELECT * FROM Orders WHERE CustomerID = Customers.CustomerID; is a select query, not a constraint definition.

3. Final Answer:

   ALTER TABLE Orders ADD FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID); -> Option C

4. Quick Check:

   Foreign key syntax = ALTER TABLE Orders ADD FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID); [OK]

Hint: Foreign keys use ALTER TABLE ADD FOREIGN KEY ... REFERENCES [OK]

Common Mistakes:

• Confusing primary key with foreign key syntax
• Using INSERT or SELECT instead of constraint definition
• Missing REFERENCES keyword

3. Given these tables:

CREATE TABLE Customers (CustomerID INT PRIMARY KEY, Name VARCHAR(50));
CREATE TABLE Orders (OrderID INT PRIMARY KEY, CustomerID INT, FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) ON DELETE CASCADE);

What happens if a customer with CustomerID = 5 is deleted?

medium

A. All orders with CustomerID = 5 are also deleted

B. The delete fails due to foreign key constraint

C. Orders with CustomerID = 5 remain unchanged

D. CustomerID in Orders is set to NULL for those orders

Solution

1. Step 1: Understand ON DELETE CASCADE

   The ON DELETE CASCADE option means deleting a referenced row causes all related rows to be deleted automatically.

2. Step 2: Apply to the scenario

   Deleting customer with CustomerID=5 will delete all orders linked to that customer in Orders table.

3. Final Answer:

   All orders with CustomerID = 5 are also deleted -> Option A

4. Quick Check:

   ON DELETE CASCADE = delete related rows [OK]

Hint: ON DELETE CASCADE deletes related rows automatically [OK]

Common Mistakes:

• Thinking delete will fail due to constraint
• Assuming related rows remain unchanged
• Confusing CASCADE with SET NULL

4. You have this foreign key constraint:

FOREIGN KEY (ProductID) REFERENCES Products(ProductID) ON DELETE SET NULL

Which error will occur if you try to delete a product that is referenced by an order, but the ProductID column in Orders is defined as NOT NULL?

medium

A. Delete fails due to NOT NULL constraint violation

B. Delete succeeds and sets ProductID to NULL

C. Delete succeeds and removes the order row

D. Delete succeeds without affecting Orders

Solution

1. Step 1: Understand ON DELETE SET NULL behavior

   This option sets the foreign key column to NULL in referencing rows when the referenced row is deleted.

2. Step 2: Check NOT NULL constraint conflict

   If the foreign key column is NOT NULL, setting it to NULL violates the column constraint, causing the delete to fail.

3. Final Answer:

   Delete fails due to NOT NULL constraint violation -> Option A

4. Quick Check:

   SET NULL + NOT NULL column = delete fails [OK]

Hint: SET NULL fails if foreign key column is NOT NULL [OK]

Common Mistakes:

• Assuming delete succeeds and sets NULL anyway
• Thinking delete removes referencing rows
• Ignoring NOT NULL constraint on foreign key

5. You want to enforce referential integrity between Employees and Departments tables. When a department is deleted, you want all employees in that department to be reassigned to department ID 0 (which means 'Unassigned'). Which foreign key option should you use?

hard

A. FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID) ON DELETE RESTRICT

B. FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID) ON DELETE CASCADE

C. FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID) ON DELETE SET NULL

D. FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID) ON DELETE SET DEFAULT

Solution

1. Step 1: Understand ON DELETE SET DEFAULT

   This option sets the foreign key column to its default value when the referenced row is deleted.

2. Step 2: Match requirement

   Since you want employees reassigned to department ID 0, set DepartmentID column default to 0 and use ON DELETE SET DEFAULT to assign that value automatically.

3. Final Answer:

   FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID) ON DELETE SET DEFAULT -> Option D

4. Quick Check:

   Reassign on delete = ON DELETE SET DEFAULT [OK]

Hint: Use ON DELETE SET DEFAULT to assign default on delete [OK]

Common Mistakes:

• Using CASCADE deletes employees instead of reassigning
• Using SET NULL when column disallows NULL
• Using RESTRICT blocks deletion