Foreign Keys and Relationships

A foreign key may reference a primary key or another eligible unique key. Foreign keys enforce references between rows; normalisation, rather than the constraint alone, is what removes redundancy.

FOREIGN KEY Constraint

1. What is a Foreign Key?

A FOREIGN KEY is a constraint used to link two tables together by enforcing a relationship.

Purpose:

• Ensures referential integrity
• Prevents inserting invalid references
• Ensures child records always point to existing parent records

Parent Table:

The table containing the primary key (example: customers)

Child Table:

The table containing the foreign key (example: orders)

2. Basic Foreign Key Example

Parent Table (customers)

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50)
);

Child Table (orders)

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  order_date DATE,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);

Valid Insert

INSERT INTO customers VALUES (1, 'John', 'Doe');
INSERT INTO orders VALUES (1, '2023-07-01', 1);

Invalid Insert (customer_id does not exist)

INSERT INTO orders VALUES (2, '2023-07-02', 2);

Explanation:

You cannot insert customer_id = 2 in orders unless it exists in customers.

3. Foreign Key with ON DELETE CASCADE

Purpose:

Automatically deletes child records if the parent record is deleted.

Useful for:

• Orders related to a deleted customer
• Posts related to a deleted user
• Cart items related to a deleted cart

Example

Recreate tables:

DROP TABLE orders;
DROP TABLE customers;

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50)
);

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  order_date DATE,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
  ON DELETE CASCADE
);

Insert data:

INSERT INTO customers VALUES (1, 'John', 'Doe');
INSERT INTO orders VALUES (1, '2023-07-01', 1);

Now delete the customer:

DELETE FROM customers WHERE customer_id = 1;

Result:

The order referencing customer_id = 1 is automatically deleted.

4. Foreign Key with ON UPDATE CASCADE

Purpose:

If the parent key changes, update the child table automatically.

Use case:

• Changing customer_id values due to data migration
• Updating product_id, order_id, etc.

Example

DROP TABLE orders;
DROP TABLE customers;

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50)
);

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  order_date DATE,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
  ON UPDATE CASCADE
);

Insert sample data:

INSERT INTO customers VALUES (1, 'John', 'Doe');
INSERT INTO orders VALUES (1, '2023-07-01', 1);

Update parent:

UPDATE customers SET customer_id = 10 WHERE customer_id = 1;

Result:

orders.customer_id becomes 10 automatically.

5. Many-to-many Relationships with a Composite Primary Key

A junction table commonly uses two foreign keys together as its composite primary key.

Example: Student → Course → Enrollment

Parent tables:

CREATE TABLE students (
  student_id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50)
);

CREATE TABLE courses (
  course_id INT PRIMARY KEY,
  course_name VARCHAR(100)
);

Child table (composite primary key):

CREATE TABLE enrollments (
  student_id INT,
  course_id INT,
  enrollment_date DATE,
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES students (student_id),
  FOREIGN KEY (course_id) REFERENCES courses (course_id)
);

Insert valid data:

INSERT INTO students VALUES (1, 'Alice', 'Johnson');
INSERT INTO courses VALUES (101, 'Math 101');
INSERT INTO enrollments VALUES (1, 101, '2023-09-01');

Insert invalid data (course 102 not present):

INSERT INTO enrollments VALUES (1, 102, '2023-09-01');

Fails because 102 is not a valid course_id.

6. Self-referencing Foreign Key (Self FK)

Used for hierarchical relationships:

Examples:

• Employee → Manager
• Comment → Parent Comment
• Category → Parent Category

Example

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  manager_id INT,
  FOREIGN KEY (manager_id) REFERENCES employees (employee_id)
);

Insert valid:

INSERT INTO employees VALUES (1, 'John', 'Doe', NULL);   -- CEO
INSERT INTO employees VALUES (2, 'Jane', 'Smith', 1);    -- reports to John

Insert invalid (manager does not exist):

INSERT INTO employees VALUES (3, 'Alice', 'Johnson', 99);

Fails because employee_id = 99 is not present.

7. Summary of Today’s Learning

What a Foreign Key Ensures

• Child data must reference valid parent data
• Prevents orphan records
• Maintains referential integrity

ON DELETE CASCADE

• Deletes child rows automatically when parent is deleted

ON UPDATE CASCADE

• Updates child rows automatically when parent key changes

Junction-table Keys

• A composite primary key can identify each many-to-many pairing

Self Foreign Keys

• Used for hierarchical or recursive relationships

8. High-Level Comparison Table

Why Do We Need Foreign Keys?

Foreign keys are used to connect related tables and keep data clean, consistent, and reliable.

Without foreign keys, the same data gets repeated again and again, which leads to:

• duplicate data
• wasted storage
• difficult updates
• inconsistent records

This problem is called Data Redundancy.

Example: Without a Foreign Key

Imagine a store where every time a customer buys something, you store:

• customer name
• customer email
• customer phone
• product purchased

If Raj Patel buys 50 products, his details are repeated 50 times.

Problems:

• Wasted space
• Hard to maintain
• If Raj changes phone number, all 50 rows must be updated
• High chance of inconsistent data

This leads to redundancy and update problems.

Example: With a Foreign Key

Instead of storing Raj’s full details in every order:

• Store Raj once in the users table
• Store only user_id in the orders table

The foreign key tells SQL:

“To know who placed this order, go look up this user_id in the users table.”

This removes duplication and keeps data normalized.

Parent–Child Relationship

Foreign keys create a Parent–Child relationship between tables.

• Parent Table → owns the original record
• Child Table → references the parent record

Example: Users and Orders

Parent Table: users

Child Table: orders

Explanation

• Raj (user_id = 1) placed 2 orders
• Neha (user_id = 2) placed 1 order

The user_id in orders points to users.user_id.

This is called a One-to-Many relationship.

Referential Integrity

One of the biggest benefits of foreign keys is Referential Integrity.

Referential integrity means:

Every foreign key value in the child table must exist in the parent table.

This prevents invalid or “ghost” records.

Example: Invalid Foreign Key

INSERT INTO orders (order_id, amount, user_id)
VALUES (5004, 100.00, 99);

This fails because user_id = 99 does not exist in the users table.

SQL rejects the insert and protects the data.

This prevents:

• invalid references
• broken relationships
• orphan records

Another Example: Products and Categories

Foreign keys are also used to organize related data.

Example: categories and products

CREATE TABLE categories (
  cat_id INT PRIMARY KEY,
  cat_name VARCHAR(50)
);

CREATE TABLE products (
  prod_id INT PRIMARY KEY,
  prod_name VARCHAR(100),
  cat_id INT,
  FOREIGN KEY (cat_id) REFERENCES categories(cat_id)
);

Here, products are linked to categories using cat_id.

Rules of Deletion

Once tables are connected using foreign keys, SQL enforces safety rules.

Rule 1: Cannot Delete Parent if Child Exists

If Raj still has orders, SQL will not allow deleting Raj from the users table.

Why?

Because that would leave orders pointing to a non-existing user.

This is called an orphan record.

Rule 2: Drop Child First, Then Parent

When deleting full tables:

• drop child table first
• drop parent table second

Correct order:

DROP TABLE orders;
DROP TABLE users;

Wrong order:

DROP TABLE users;

This fails because orders still depends on users.

Common Mistakes to Avoid

1. Mismatched Data Types

The parent key and foreign key must have the same datatype.

Correct:

• users.user_id → INT
• orders.user_id → INT

Wrong:

• users.user_id → INT
• orders.user_id → VARCHAR

This breaks the relationship.

2. Forgetting the Foreign Key Constraint

If you only store user_id as a number without defining FOREIGN KEY, SQL will not protect your data.

That means:

• invalid user IDs can be inserted
• ghost records are possible
• data integrity is lost

3. Not Indexing Foreign Keys

In large databases, foreign key columns should usually be indexed for better performance.

Without indexing:

• joins become slow
• lookups become expensive

Relationships in SQL

A relationship in SQL is a connection between two tables using:

• a Primary Key in one table
• a Foreign Key in another table

This is why SQL databases are called Relational Databases.

Types of Relationships

1. One-to-Many (1:N)

One parent row can have many child rows.

Example:

• one student
• many attendance records

students

attendance

Raj has many attendance records.

2. One-to-One (1:1)

One parent row maps to exactly one child row.

Example:

• one student
• one private profile

Used for:

• sensitive data
• optional profile info

3. Many-to-Many (M:N)

Many rows from one table relate to many rows in another.

Example:

• one student can join many batches
• one batch has many students

Handled using a Junction Table.

Example:

• students
• batches
• enrollments

4. Self-Referencing Relationship

A table references itself.

Example:

• employee → manager
• student → mentor

Used for hierarchical data.

Normalization and Why Foreign Keys Matter

Foreign keys are one of the core building blocks of Normalization.

Normalization means:

• split repeated data into separate tables
• connect them using keys
• avoid duplication
• improve consistency

Instead of repeating Raj’s data in 50 rows:

• store Raj once
• reference him everywhere using user_id

Benefits:

• less duplication
• easier updates
• cleaner schema
• fewer bugs

Referential Integrity Rules

Foreign keys help enforce data safety rules:

• no orphan records
• no invalid references
• no broken relationships
• parent-child consistency is maintained

This is called Referential Integrity.

It is one of the most important reasons foreign keys exist.