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:

• 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).