10. Foreign Keys & Table Relationships

Level: Beginner

ℹ️ What You'll Learn

• Foreign key constraint -- linking tables
• One-to-many relationships (1 teacher, many students)
• Referential integrity -- prevent orphaned records
• Parent and child tables
• Cascade delete -- automatic related record deletion
• Cascade update -- automatic related record updating
• Creating foreign keys
• Common foreign key mistakes

Tables don't exist in isolation. Students belong to Teachers. Exams belong to Subjects. This article explains relationships via Foreign Keys.

Why Foreign Keys Matter for Backend Developers

Foreign keys prevent bad relationships:

-- WITHOUT foreign keys (inconsistent data)
INSERT INTO Exam (SubjectId) VALUES (999);  -- Subject 999 doesn't exist!
-- Orphaned record. Backend has to clean up.

-- WITH foreign keys (enforced consistency)
INSERT INTO Exam (SubjectId) VALUES (999);  -- Rejected!
-- Error: FK_Exam_Subject violated
-- Only valid SubjectIds allowed.

Foreign keys enforce relationships at database level, not in code.

Understanding Relationships

One-to-Many Relationship

One teacher teaches many subjects. One subject is taught by one teacher.

Teacher Table         Subject Table
TeacherId            SubjectId
Name      -> (1)      Name       (many) <-
                     TeacherId

Example:

• Dr. Mehta (TeacherId = 1) teaches:
  • Maths (SubjectId = 10, TeacherId = 1)
  • Science (SubjectId = 11, TeacherId = 1)
• Mrs. Rao (TeacherId = 2) teaches:
  • English (SubjectId = 12, TeacherId = 2)

In database:

-- Teacher table
TeacherId | Name
1         | Dr. Mehta
2         | Mrs. Rao

-- Subject table
SubjectId | Name     | TeacherId
10        | Maths    | 1           <- References Dr. Mehta
11        | Science  | 1           <- References Dr. Mehta
12        | English  | 2           <- References Mrs. Rao

Subject.TeacherId is Foreign Key (references Teacher.TeacherId).

Foreign Key Constraint

Link child table to parent table.

CREATE TABLE Teacher
(
    TeacherId INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(100) NOT NULL
);

CREATE TABLE Subject
(
    SubjectId INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(100) NOT NULL,
    TeacherId INT NOT NULL,
    
    -- Foreign Key: Subject.TeacherId references Teacher.TeacherId
    FOREIGN KEY (TeacherId) REFERENCES Teacher(TeacherId)
);

Parts:

• FOREIGN KEY (TeacherId) -- This table's column
• REFERENCES Teacher(TeacherId) -- Points to Teacher's primary key

Effect: Subject can only have valid TeacherIds (must exist in Teacher table).

Example 1: Valid Insert (FK Constraint Satisfied)

-- First, create teacher
INSERT INTO Teacher (Name) VALUES ('Dr. Mehta');
-- TeacherId = 1

-- Then, create subject with valid TeacherId
INSERT INTO Subject (Name, TeacherId) VALUES ('Maths', 1);
-- Success: TeacherId 1 exists in Teacher table

Example 2: Invalid Insert (FK Constraint Violated)

-- Try to create subject with non-existent TeacherId
INSERT INTO Subject (Name, TeacherId) VALUES ('Physics', 999);
-- Error: The INSERT, UPDATE, or DELETE statement conflicted with a FOREIGN KEY constraint
-- TeacherId 999 doesn't exist in Teacher table

Full School Management System (Relationships)

-- Parent table (no dependencies)
CREATE TABLE Teacher
(
    TeacherId INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(100) NOT NULL
);

-- Child table (depends on Teacher)
CREATE TABLE Subject
(
    SubjectId INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(100) NOT NULL,
    TeacherId INT NOT NULL,
    FOREIGN KEY (TeacherId) REFERENCES Teacher(TeacherId)
);

-- Another child table (depends on Subject)
CREATE TABLE Exam
(
    ExamId INT PRIMARY KEY IDENTITY(1,1),
    SubjectId INT NOT NULL,
    ExamDate DATE,
    FOREIGN KEY (SubjectId) REFERENCES Subject(SubjectId)
);

-- Another child table (depends on Student and Exam)
CREATE TABLE Student
(
    StudentId INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(100) NOT NULL
);

CREATE TABLE ExamResult
(
    ResultId INT PRIMARY KEY IDENTITY(1,1),
    StudentId INT NOT NULL,
    ExamId INT NOT NULL,
    MarksObtained DECIMAL(5,2),
    
    FOREIGN KEY (StudentId) REFERENCES Student(StudentId),
    FOREIGN KEY (ExamId) REFERENCES Exam(ExamId)
);

Hierarchy:

Teacher (parent)
    v (1-to-many)
Subject (child of Teacher, parent of Exam)
    v (1-to-many)
Exam (child of Subject, parent of ExamResult)
    v (1-to-many, many-to-many)
ExamResult (child of both Student and Exam)
    ^ (1-to-many)
Student (parent)

Cascade Delete

Automatically delete related records when parent deleted.

CREATE TABLE Subject
(
    SubjectId INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(100) NOT NULL,
    TeacherId INT NOT NULL,
    
    FOREIGN KEY (TeacherId) REFERENCES Teacher(TeacherId)
    ON DELETE CASCADE      -- Delete all subjects if teacher deleted
);

Example:

-- Delete teacher Dr. Mehta
DELETE FROM Teacher WHERE TeacherId = 1;

-- What happens:
-- 1. Delete Teacher (TeacherId = 1)
-- 2. ON DELETE CASCADE triggers
-- 3. Automatically delete all Subject rows where TeacherId = 1

Without CASCADE DELETE: Would get FK constraint violation error.

With CASCADE DELETE: Teacher and all subjects deleted automatically.

Cascade Update

Automatically update related records when parent updated.

CREATE TABLE Subject
(
    SubjectId INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(100) NOT NULL,
    TeacherId INT NOT NULL,
    
    FOREIGN KEY (TeacherId) REFERENCES Teacher(TeacherId)
    ON UPDATE CASCADE      -- Update all subjects if teacher ID changes
);

When to use: Rare (IDs shouldn't change). Mainly for non-ID columns.

Adding Foreign Key to Existing Table

-- Existing tables
CREATE TABLE Subject
(
    SubjectId INT PRIMARY KEY,
    Name NVARCHAR(100),
    TeacherId INT
);

-- Add FK afterward
ALTER TABLE Subject
ADD CONSTRAINT FK_Subject_Teacher
FOREIGN KEY (TeacherId) REFERENCES Teacher(TeacherId);

---

Common Foreign Key Mistakes

Mistake 1: Creating orphaned records (without FK)

Wrong -- No FK constraint:

CREATE TABLE Subject
(
    SubjectId INT PRIMARY KEY,
    Name NVARCHAR(100),
    TeacherId INT  -- No FOREIGN KEY constraint!
);

INSERT INTO Subject (SubjectId, Name, TeacherId) 
VALUES (1, 'Maths', 999);
-- Success! But TeacherId 999 doesn't exist (orphaned)

Data inconsistent. "Maths" subject has non-existent teacher.

Result: Backend must handle orphaned data.

Fix: Add FOREIGN KEY constraint:

CREATE TABLE Subject
(
    SubjectId INT PRIMARY KEY,
    Name NVARCHAR(100),
    TeacherId INT,
    FOREIGN KEY (TeacherId) REFERENCES Teacher(TeacherId)
);

INSERT INTO Subject (SubjectId, Name, TeacherId) 
VALUES (1, 'Maths', 999);
-- Error: FK constraint violated. Only valid TeacherIds accepted.

Mistake 2: Deleting parent record (breaks FK)

Wrong -- FK without CASCADE:

CREATE TABLE Subject
(
    SubjectId INT PRIMARY KEY,
    TeacherId INT,
    FOREIGN KEY (TeacherId) REFERENCES Teacher(TeacherId)
    -- No CASCADE
);

DELETE FROM Teacher WHERE TeacherId = 1;
-- Error: FK constraint prevents deletion (subjects still reference teacher)

Can't delete teacher if subjects exist.

Fix -- Option 1: Use CASCADE:

FOREIGN KEY (TeacherId) REFERENCES Teacher(TeacherId)
ON DELETE CASCADE  -- Auto-delete subjects too

Fix -- Option 2: Delete children first:

DELETE FROM Subject WHERE TeacherId = 1;  -- Delete children
DELETE FROM Teacher WHERE TeacherId = 1;  -- Then parent

Mistake 3: Wrong FK column type

Wrong -- Type mismatch:

CREATE TABLE Teacher
(
    TeacherId INT PRIMARY KEY,
    ...
);

CREATE TABLE Subject
(
    SubjectId INT PRIMARY KEY,
    TeacherId BIGINT,  -- Wrong! Should be INT
    FOREIGN KEY (TeacherId) REFERENCES Teacher(TeacherId)
    -- Error: FK types must match (INT != BIGINT)
);

Result: FK constraint fails.

Fix: Match types:

TeacherId INT,  -- Same as Teacher.TeacherId type
FOREIGN KEY (TeacherId) REFERENCES Teacher(TeacherId)

Mistake 4: Circular FK (Table A -> B -> A)

Wrong:

CREATE TABLE Student (StudentId INT PRIMARY KEY, ClassId INT, ...);
CREATE TABLE Class (ClassId INT PRIMARY KEY, StudentId INT, ...);
-- Student references Class, Class references Student
-- Creates circular dependency

Result: Data integrity hard to maintain. Deletion complex.

Fix: Avoid circular FKs. Design hierarchical relationships:

School (parent)
    v
Class (child)
    v
Student (child)
    v
Exam (child)

One direction only.

---

Best Practices for Foreign Keys

1. Always define foreign keys -- Enforce relationships at database
2. Use CASCADE DELETE carefully -- Only when child shouldn't exist without parent
3. Match data types -- FK column type must match PK type
4. Use meaningful names -- FK_Subject_Teacher not FK1
5. Document relationships -- Diagram or comments explain structure
6. Test constraints -- Verify they work as intended
7. Design hierarchically -- Parent -> Child, not circular

---

🎯 Q1: What's a foreign key and why use it?

Foreign Key links child table to parent table.

Example:

CREATE TABLE Subject
(
    SubjectId INT PRIMARY KEY,
    TeacherId INT,
    FOREIGN KEY (TeacherId) REFERENCES Teacher(TeacherId)
);

Subject.TeacherId must exist in Teacher table. Prevents orphaned subjects.

Why:

• Prevents invalid relationships
• Enforces consistency at database level
• Database rejects bad data before it enters

Without FK: Bad data enters table, backend has to handle. With FK: Bad data rejected at source.

🎯 Q2: What's the difference between CASCADE DELETE and checking foreign keys?

Foreign Key (checking only):

FOREIGN KEY (TeacherId) REFERENCES Teacher(TeacherId)

DELETE FROM Teacher WHERE TeacherId = 1;
-- Error: Cannot delete, subjects reference this teacher

Prevents deletion if children exist. Must delete children first.

CASCADE DELETE:

FOREIGN KEY (TeacherId) REFERENCES Teacher(TeacherId)
ON DELETE CASCADE

DELETE FROM Teacher WHERE TeacherId = 1;
-- Success: Teacher deleted, all subjects auto-deleted

Automatically deletes children when parent deleted.

Use CASCADE when: Child shouldn't exist without parent. Use FK only when: Child can exist alone or you delete manually.

🎯 Q3: Can I delete a record if it has related records?

Without CASCADE:

DELETE FROM Teacher WHERE TeacherId = 1;
-- Error: The DELETE statement conflicted with a FOREIGN KEY constraint
-- Solution: Delete subjects first
DELETE FROM Subject WHERE TeacherId = 1;
DELETE FROM Teacher WHERE TeacherId = 1;

With CASCADE:

-- Same command works
DELETE FROM Teacher WHERE TeacherId = 1;
-- Automatically deletes all subjects too

Which to use:

• CASCADE: When child meaningless without parent (Subject without Teacher)
• FK only: When child can exist alone (Student can exist without Class temporarily)

🎯 Q4: What happens if I insert with wrong FK?

CREATE TABLE Subject
(
    SubjectId INT PRIMARY KEY,
    TeacherId INT,
    FOREIGN KEY (TeacherId) REFERENCES Teacher(TeacherId)
);

-- TeacherId 999 doesn't exist in Teacher table
INSERT INTO Subject VALUES (1, 'Maths', 999);
-- Error: The INSERT conflicted with a FOREIGN KEY constraint 'FK_Subject_Teacher'
-- Statement failed

Insert rejected. Must provide valid TeacherId.

Benefit: Data stays consistent. No orphaned records.

🎯 Q5: How do I diagram relationships in School Management System?

Hierarchy:

Teacher (1)
    v 1-to-many
Subject (many)
    v 1-to-many
Exam (many)
    v 1-to-many, many-to-many
ExamResult (many)
    ^ 1-to-many
Student (1)

Rule: Parent (1) has PRIMARY KEY. Child (many) has FOREIGN KEY.

• 1 Teacher -> many Subjects (1-to-many)
• 1 Subject -> many Exams (1-to-many)
• 1 Student, 1 Exam -> many ExamResults (many-to-many through ExamResult)

🎯 Q6: Do I need foreign keys if using an ORM like Entity Framework?

Short answer: Yes, still define them in database.

Reason:

• Database enforces, not code
• Prevents data corruption
• ORM depends on FKs for navigation

Best practice: Define FKs in database schema:

FOREIGN KEY (TeacherId) REFERENCES Teacher(TeacherId)

Then ORM (EF Core, Dapper) uses them for relationships.

Code example (EF Core):

public class Subject
{
    public int SubjectId { get; set; }
    public int TeacherId { get; set; }
    public Teacher Teacher { get; set; }  // Navigation based on FK
}

EF Core uses FK to navigate relationships.

---

🤖Use AI to Learn Faster

⚠️ Important for beginners: Do NOT use AI to write your code yet. Type every example yourself. Your brain learns by doing, not by reading AI output. Use AI only to explain and quiz you — not to code for you. Once you have strong fundamentals, AI becomes a powerful productivity tool for repetitive tasks.

Use ChatGPT, Claude, or Copilot to go deeper on Foreign keys and table relationships in SQL Server. Try these prompts:

• "Explain foreign keys and one-to-many relationships with Teacher/Subject example"
• "What's the difference between CASCADE DELETE and FK constraint only?"
• "How do I create relationships between Student, Exam, and ExamResult tables?"
• "What happens if I try to insert with a non-existent foreign key?"
• "Quiz me: ask 5 questions about foreign keys, relationships, referential integrity"

💡 Tip: After reading this article, paste your own code into AI and ask "What could go wrong here and why?" — fastest way to find edge cases and deepen understanding.

Next Article

11. NULL Handling -- ISNULL, COALESCE, NULLIF ->