Foreign Keys and Table Relationships

# CHAPTER 13

Foreign Keys and Table Relationships #

1. Introduction #

2. Learning Objectives #

• Define a Foreign Key constraint.

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

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

• Understand Referential Integrity.

• Use ON DELETE CASCADE to manage child records automatically.

3. The FOREIGN KEY Constraint #

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

• One User can have Many Orders.

• One Department can have Many Employees.

The Rule: The Foreign Key always goes in the "Many" table (the Child table).

-- 1. Create the Parent Table First
CREATE TABLE departments (
    dept_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    dept_name VARCHAR(50)
);

-- 2. Create the Child Table
CREATE TABLE employees (
    emp_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(50),
    department_id INT, -- This column will hold the Foreign Key
    
    -- The Mathematical Link:
    CONSTRAINT fk_department
        FOREIGN KEY (department_id) 
        REFERENCES departments(dept_id)
);

5. Referential Integrity and ON DELETE CASCADE #

By default, PostgreSQL will strictly block you from deleting the Sales department, throwing an error to protect the children. If you want PostgreSQL to automatically delete all the child employees when the parent department is deleted, you use ON DELETE CASCADE.

    CONSTRAINT fk_department
        FOREIGN KEY (department_id) 
        REFERENCES departments(dept_id)
        ON DELETE CASCADE -- If the parent dies, the children die with it.

6. Many-to-Many Relationships (N:M) #

The Solution: You must build a 3rd table in the middle, called a "Pivot" (or Junction) table.

CREATE TABLE students ( id INT PRIMARY KEY, name VARCHAR(50) );
CREATE TABLE classes ( id INT PRIMARY KEY, title VARCHAR(50) );

-- The Pivot Table connecting them together!
CREATE TABLE student_classes (
    student_id INT REFERENCES students(id) ON DELETE CASCADE,
    class_id INT REFERENCES classes(id) ON DELETE CASCADE,
    PRIMARY KEY (student_id, class_id) -- Composite Key!
);

7. One-to-One Relationships (1:1) #

8. Mini Project: E-Commerce Architecture #

CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(50) );
CREATE TABLE products ( id INT PRIMARY KEY, price NUMERIC );

-- 1:N Relationship (One User -> Many Orders)
CREATE TABLE orders ( 
    id INT PRIMARY KEY, 
    user_id INT REFERENCES users(id) 
);

-- N:M Relationship (Orders have Many Products, Products belong to Many Orders)
-- We build a Pivot table called "order_items"
CREATE TABLE order_items (
    order_id INT REFERENCES orders(id),
    product_id INT REFERENCES products(id),
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

9. Common Mistakes #

• Creating the Child Table First: You cannot create the employees table if the departments table does not exist yet. You must always construct the Parent tables before the Child tables.

• Data Type Mismatches: If the Parent Primary Key is a BIGINT, the Child Foreign Key column MUST also be a BIGINT. If you make it a standard INT, PostgreSQL will throw a foreign key constraint error.

10. Best Practices #

• Naming Conventions: Always name your Foreign Key columns as the singular parent table name followed by id (e.g., userid, productid).

CREATE TABLE reviews (
    review_id SERIAL PRIMARY KEY,
    product_id INTEGER,
    rating INTEGER,
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE
);

13. MCQ Quiz with Answers #

14. Interview Questions #

• Q: Explain the concept of Referential Integrity. How do Foreign Keys enforce it?

• Q: Describe a business scenario where using ON DELETE CASCADE would be extremely dangerous and ill-advised.

15. FAQs #

16. Summary #

17. Next Chapter Recommendation #