Mastering Table Relationships in SQL Server - Quick Office Pointe
Quick Office Pointe Logo
Quick Office Pointe Logo
Quick Office Pointe Logo
Database Decoded

Mastering Table Relationships in SQL Server

by isaac Muteru Feb 12, 2025
Mastering Table Relationships in SQL Server

Welcome to week 2 Day 3 of the Database Decoded series! Now that you’ve learned to insert, update, and delete data safely, let’s dive into one of the most powerful concepts in databases: table relationships. Today, we’ll explore how to link tables using primary and foreign keys and combine data with INNER JOIN.

Why Relationships Matter

In real-world applications, data is rarely stored in a single table. For example:

  • Employees belong to Departments.

  • Orders are linked to Customers.

  • Products are categorized into Categories.

Relationships prevent data duplication, improve organization, and ensure consistency. Let’s see how they work!

Topics Covered

1. Primary Keys: The Unique Identifier

A primary key is a column (or set of columns) that uniquely identifies each row in a table.

Example: Departments Table


CREATE TABLE Departments (  
    DepartmentID INT PRIMARY KEY,  
    DepartmentName VARCHAR(50)  
);

  • DepartmentID is the primary key. No two departments can have the same ID.

  • Why It Matters: Ensures each record is unique and speeds up searches.

2. Foreign Keys: Linking Tables

A foreign key is a column that references the primary key of another table, creating a relationship.

Example: Linking Employees to Departments
Modify the Employees table to include a foreign key:



ALTER TABLE Employees  
ADD DepartmentID INT,  
CONSTRAINT FK_Employee_Department  
FOREIGN KEY (DepartmentID)  
REFERENCES Departments(DepartmentID);

  • Explanation:

    • The DepartmentID in Employees references the DepartmentID in Departments.

    • This ensures an employee can’t be assigned to a nonexistent department.

3. INNER JOIN: Combining Data from Multiple Tables

The INNER JOIN clause retrieves data from two or more tables based on a related column.

Example: Fetch Employees with Department Names



SELECT  
    E.EmployeeID,  
    E.FirstName,  
    E.LastName,  
    D.DepartmentName  
FROM Employees E  
INNER JOIN Departments D  
    ON E.DepartmentID = D.DepartmentID;

  • Result:

EmployeeIDFirstNameLastNameDepartmentName
1JohnDoeHR
2JaneSmithIT

  • How It Works:

    • INNER JOIN returns only rows where there’s a match in both tables.

    • Use aliases (E for Employees, D for Departments) to simplify queries.

Visual Analogy:
Imagine two overlapping circles (Venn diagram). INNER JOIN returns only the overlapping section.

Practice Tasks

Let’s reinforce these concepts with hands-on exercises. Use the Employees and Departments tables you created earlier.

Task 1: Create Related Tables

  1. Create a Departments table with DepartmentID (primary key) and DepartmentName.

  2. Modify the Employees table to include a DepartmentID column (foreign key).

Task 2: Insert Sample Data

  1. Add departments:

    INSERT INTO Departments (DepartmentID, DepartmentName)  
VALUES (1, 'HR'), (2, 'IT'), (3, 'Finance');

  2. Update employees to assign DepartmentID values (e.g., John Doe to HR = 1).

Task 3: Write JOIN Queries

  1. List all employees and their department names.

  2. Find employees in the "IT" department using INNER JOIN.

  3. Bonus: Calculate the average salary per department.

Task 4: Experiment with Relationships

  • Try inserting an employee with an invalid DepartmentID (e.g., 99). What happens?

  • Delete a department and observe how it affects linked employees (hint: use ON DELETE CASCADE if needed).

Key Takeaways

  • Primary Key: Uniquely identifies rows in a table (e.g., DepartmentID).

  • Foreign Key: Links data between tables, enforcing referential integrity.

  • INNER JOIN: Combines data from related tables based on matching values.

  • Data Integrity: Relationships prevent orphaned records (e.g., employees without valid departments).

192 views