SQL Foreign Keys | One-to-Many, Many-to-Many Relationships

# CHAPTER 15

Foreign Keys and Relationships #

1. Introduction #

2. Learning Objectives #

• Define a FOREIGN KEY constraint.

• Understand the concept of Referential Integrity.

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

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

• Architect Many-to-Many (N:M) Relationships using Pivot Tables.

3. The FOREIGN KEY Concept #

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

To build this, we place the FOREIGN KEY on the "Many" side (The Orders table).

-- TABLE A: The "One" Side
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
);

-- TABLE B: The "Many" Side
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    order_total DECIMAL(10,2),
    
    -- 1. Create the integer column to hold the User's ID
    user_id INT,
    
    -- 2. Officially declare it as a Foreign Key linked to the users table!
    FOREIGN KEY (user_id) REFERENCES users(id)
);

5. What is Referential Integrity? #

6. Architecting a One-to-One Relationship (1:1) #

We build it exactly like a 1:N relationship, but we add a UNIQUE constraint to the Foreign Key, guaranteeing that no two Tax Records can point to the same User!

CREATE TABLE tax_records (
    record_id INT AUTO_INCREMENT PRIMARY KEY,
    ssn_number VARCHAR(11),
    
    user_id INT UNIQUE, -- The UNIQUE constraint makes it 1:1 !
    FOREIGN KEY (user_id) REFERENCES users(id)
);

7. Architecting a Many-to-Many Relationship (N:M) #

You cannot put a class_id on the Student (because they have many). You cannot put a student_id on the Class (because it has many). The Solution: You must create a 3rd table called a Pivot Table (or Junction Table). Its only job is to hold two Foreign Keys and connect them together.

-- The Pivot Table connecting Students and Classes
CREATE TABLE enrollments (
    enrollment_id INT AUTO_INCREMENT PRIMARY KEY,
    
    student_id INT,
    class_id INT,
    
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (class_id) REFERENCES classes(id)
);

*(Now, if Student 5 enrolls in Class 10, you simply insert a row into enrollments: (5, 10)!)*

8. Mini Project: The Library System #

CREATE TABLE publishers ( id INT PRIMARY KEY, name VARCHAR(100) );
CREATE TABLE authors ( id INT PRIMARY KEY, name VARCHAR(100) );

-- The Book has a 1:N relationship with Publisher
CREATE TABLE books (
    id INT PRIMARY KEY,
    title VARCHAR(100),
    publisher_id INT,
    FOREIGN KEY (publisher_id) REFERENCES publishers(id)
);

-- The N:M Bridge connecting Books and Authors
CREATE TABLE book_author_bridge (
    book_id INT,
    author_id INT,
    FOREIGN KEY (book_id) REFERENCES books(id),
    FOREIGN KEY (author_id) REFERENCES authors(id)
);

9. Common Mistakes #

• Mismatched Data Types: If users.id is an INT, but your orders.user_id column is a VARCHAR or a BIGINT, the Foreign Key creation will fail. The data types must match flawlessly.

• Forgetting to define the FK: Beginners often create the user_id column, but forget to write the FOREIGN KEY constraint command at the bottom of the table. Without the command, it is just a dumb integer column; the database has no idea it is supposed to enforce a relationship!

10. Best Practices #

• Cascading Deletes: What happens if you delete a User, but they have 50 Orders pointing to them? The database will block the deletion to protect Referential Integrity. If you want the database to automatically delete the 50 orders when the user is deleted, you append ON DELETE CASCADE to your Foreign Key definition!

11. Exercises #

1. 1. In a One-to-Many relationship (e.g., Department and Employees), which table gets the Foreign Key column?

1. 2. What specific database mechanism physically blocks you from inserting an order for a user_id that does not exist?

12. SQL Challenges #

CREATE TABLE comments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    text TEXT,
    post_id INT,
    FOREIGN KEY (post_id) REFERENCES posts(id)
);

13. MCQ Quiz with Answers #

14. Interview Questions #

• Q: Explain the structural difference between how you model a One-to-Many relationship versus how you model a Many-to-Many relationship in SQL. Why is the Pivot Table strictly necessary for the latter?

• Q: A developer attempts to execute a DELETE FROM users WHERE id = 5; command. The database engine rejects the command and throws a Foreign Key Constraint error. Explain why the database protected the record, and how ON DELETE CASCADE alters this behavior.

15. FAQs #

16. Summary #

17. Next Chapter Recommendation #