Foreign Keys and Relationships

# CHAPTER 13

Foreign Keys and Relationships #

1. Introduction #

2. Learning Objectives #

• Define a Foreign Key constraint.

• Architect One-to-One (1:1) relationships.

• Architect One-to-Many (1:N) relationships.

• Architect Many-to-Many (N:M) relationships using Pivot tables.

• Enforce Referential Integrity using ON DELETE CASCADE.

3. What is a Foreign Key? #

*Crucially*, applying a Foreign Key constraint tells MySQL: *"Do not allow a number in this column unless that exact ID number actually exists in the other table."* This prevents "Orphaned Records" (e.g., an order belonging to user ID 999, when user 999 doesn't exist!).

4. The One-to-Many Relationship (1:N) #

• One User can have Many Orders.

• One Department can have Many Employees.

The Rule: The Foreign Key always goes on the "Many" side.

-- 1. Create the "One" side (The Parent)
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50)
);

-- 2. Create the "Many" side (The Child)
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_total DECIMAL(10, 2),
    user_id INT,  -- This column will hold the bridge data
    
    -- This constraint enforces the mathematical link!
    FOREIGN KEY (user_id) REFERENCES users(id)
);

5. The Many-to-Many Relationship (N:M) #

• Many Students can enroll in Many Classes.

• Many Classes can hold Many Students.

You cannot put a Foreign Key in the students table, nor the classes table, because you would need multiple IDs in a single cell (which violates Normalization). The Solution: You must create a 3rd table in the middle, called a Pivot Table (or Junction Table). It holds nothing but Foreign Keys!

-- Parent Table 1
CREATE TABLE students ( id INT PRIMARY KEY, name VARCHAR(50) );

-- Parent Table 2
CREATE TABLE classes ( id INT PRIMARY KEY, title VARCHAR(50) );

-- The Pivot Table (The Bridge)
CREATE TABLE student_classes (
    student_id INT,
    class_id INT,
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (class_id) REFERENCES classes(id),
    
    -- A composite Primary Key! A student can't enroll in the exact same class twice.
    PRIMARY KEY (student_id, class_id) 
);

6. The One-to-One Relationship (1:1) #

• One User has One Social Security Profile.

7. Referential Integrity: ON DELETE CASCADE #

If you *want* MySQL to automatically delete all the user's orders when the user is deleted, you append ON DELETE CASCADE to the Foreign Key definition.

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    -- If the parent user is deleted, instantly delete this order too!
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

8. Common Mistakes #

• Data Type Mismatches: If the Primary Key id in the users table is an INT, the Foreign Key user_id in the orders table MUST also be an INT. If one is a BIGINT and the other is an INT, the constraint will fail to create.

• Putting the FK on the wrong side: In a 1:N relationship (One Department has Many Employees), beginners often try to put an employees_list column inside the departments table. This violates 1NF. The department_id FK must go inside the employees table!

9. Best Practices #

• Foreign Key Naming Convention: Always name your Foreign Key columns as the singular name of the parent table, followed by _id. (e.g., Parent table users -> Foreign Key user_id). This makes writing JOIN queries infinitely easier to read.

10. Exercises #

1. 1. In a blog application where one User can write many Comments, in which table should the Foreign Key be placed, and what should it be named?

1. 2. Why is a "Pivot Table" strictly required to map a Many-to-Many relationship between products and categories?

11. MCQ Quiz with Answers #

12. Interview Questions #

• Q: Describe the architectural structure required to implement a Many-to-Many relationship in a relational database. Why can't it be done with just two tables?

• Q: Explain what "Referential Integrity" means in the context of database architecture and how Foreign Keys enforce it.

13. FAQs #

14. Summary #

15. Next Chapter Recommendation #