Database Management System (DBMS) / Relational Model

Referential Constraints

🎯 What is Referential Constraints?

A Referential Integrity Constraint ensures that the relationship between two tables remains consistent. It is enforced through foreign keys, which link one table’s column to the primary key of another table.

A Referential Integrity Constraint ensures that a value in a foreign key column must either:

  • Match an existing value in the referenced primary key, or

  • Be NULL (if allowed)

🧾 Example: Student & Department

🧩 Department Table (Parent)

DeptID (PK)

DeptName

10

Computer Science

20

Mathematics

👩‍🎓 Student Table (Child)

StudentID (PK)

Name

DeptID (FK)

101

Alice

10

102

Bob

20

103

Zara

99 ❌

  • ✅ DeptID = 10 and 20 are valid because they exist in the Department table.

  • ❌ DeptID = 99 violates referential integrity (no such department).

🧪 SQL Implementation:

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

CREATE TABLE Student (
  StudentID INT PRIMARY KEY,
  Name VARCHAR(50),
  DeptID INT,
  FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
);

This ensures:

  • You cannot insert a student with a non-existing DeptID

  • You cannot delete a department if students are still linked to it (unless using ON DELETE rules)

🎯 Purpose of Referential Constraints:

  • Maintains logical relationships between tables

  • Prevents orphan records

  • Ensures data integrity across tables

✅ Summary:

Feature

Description

🔑 Based on

Foreign Key – Primary Key relationship

🧩 Maintains

Consistency between related tables

🚫 Prevents

Orphaned records (unmatched foreign keys)

🧪 Enforced Using

FOREIGN KEY in SQL