Creating Relationships with Foreign Keys in SQL

Table of Contents

1. Introduction
2. What is a Foreign Key?
3. Importance of Foreign Keys
4. Primary Key vs Foreign Key
5. Syntax for Declaring a Foreign Key
6. Creating Foreign Keys During Table Creation
7. Adding Foreign Keys to Existing Tables
8. One-to-Many Relationship
9. One-to-One Relationship
10. Many-to-Many Relationship (Using Junction Tables)
11. ON DELETE and ON UPDATE Options
12. CASCADE, SET NULL, RESTRICT Explained
13. Foreign Keys Across Multiple Columns
14. Naming Foreign Keys for Clarity
15. Foreign Key Constraints and Data Integrity
16. Viewing Foreign Key Relationships
17. Dropping Foreign Keys
18. Real-World Example: Orders and Customers
19. Common Errors and Troubleshooting
20. Summary and What’s Next

1. Introduction

In relational databases, foreign keys establish connections between tables. They enforce rules about how data is shared and related across tables, enabling consistency, normalization, and real-world modeling.

2. What is a Foreign Key?

A foreign key is a field (or group of fields) in one table that refers to the primary key in another table. It creates a link between the two tables.

3. Importance of Foreign Keys

• Maintain referential integrity
• Prevent orphaned records
• Represent real-world relationships (e.g., orders belong to customers)
• Help with joins and query performance when indexed

4. Primary Key vs Foreign Key

5. Syntax for Declaring a Foreign Key

sqlCopyEditFOREIGN KEY (column_name) REFERENCES parent_table(primary_key)

6. Creating Foreign Keys During Table Creation

sqlCopyEditCREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

7. Adding Foreign Keys to Existing Tables

sqlCopyEditALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(id);

Name the constraint for easier debugging and management.

8. One-to-Many Relationship

One customer can place many orders

sqlCopyEdit-- customers table
CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(100)
);

-- orders table
CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

9. One-to-One Relationship

When one row in table A corresponds to exactly one row in table B:

sqlCopyEditCREATE TABLE user_profiles (
  user_id INT PRIMARY KEY,
  bio TEXT,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

Here, user_id is both primary and foreign key.

10. Many-to-Many Relationship (Using Junction Tables)

Use a third table with two foreign keys:

sqlCopyEditCREATE TABLE student_courses (
  student_id INT,
  course_id INT,
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES students(id),
  FOREIGN KEY (course_id) REFERENCES courses(id)
);

11. ON DELETE and ON UPDATE Options

Foreign keys can define what happens when the referenced data is deleted or updated:

sqlCopyEditFOREIGN KEY (customer_id)
REFERENCES customers(id)
ON DELETE CASCADE
ON UPDATE CASCADE;

12. CASCADE, SET NULL, RESTRICT Explained

13. Foreign Keys Across Multiple Columns

Composite keys:

sqlCopyEditFOREIGN KEY (product_id, supplier_id)
REFERENCES inventory(product_id, supplier_id);

Ensure both columns exist and are indexed together in the referenced table.

14. Naming Foreign Keys for Clarity

sqlCopyEditCONSTRAINT fk_order_customer
FOREIGN KEY (customer_id)
REFERENCES customers(id)

A clear constraint name simplifies debugging and schema reviews.

15. Foreign Key Constraints and Data Integrity

• Prevents inserting invalid values
• Prevents deleting referenced values
• Helps ensure relationships are always valid

16. Viewing Foreign Key Relationships

In MySQL:

sqlCopyEditSHOW CREATE TABLE orders;

In PostgreSQL:

sqlCopyEditSELECT
  conname, conrelid::regclass AS table_name
FROM pg_constraint
WHERE contype = 'f';

17. Dropping Foreign Keys

sqlCopyEditALTER TABLE orders
DROP FOREIGN KEY fk_customer; -- MySQL

ALTER TABLE orders
DROP CONSTRAINT fk_customer; -- PostgreSQL

18. Real-World Example: Orders and Customers

sqlCopyEditCREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INT NOT NULL,
  order_total DECIMAL(10,2),
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

This ensures every order is linked to a valid customer.

19. Common Errors and Troubleshooting

20. Summary and What’s Next

Foreign keys are essential for establishing relational integrity in SQL databases. They enforce consistency, clarify relationships, and power robust query design. Whether you’re creating one-to-one or many-to-many links, understanding foreign keys is key to designing proper relational schemas.