Primary Keys and Foreign Keys

Certainly! Let’s delve into a comprehensive exploration of Primary Keys and Foreign Keys in relational databases, covering their definitions, significance, differences, and practical applications.

---

Table of Contents

1. Introduction
   • What are Primary Keys and Foreign Keys?
   • Importance in Relational Database Design
2. Primary Key
   • Definition and Characteristics
   • Syntax and Examples
   • Composite Primary Keys
   • Best Practices
3. Foreign Key
   • Definition and Purpose
   • Syntax and Examples
   • Referential Integrity Constraints
   • Cascading Actions
   • Best Practices
4. Differences Between Primary Key and Foreign Key
   • Comparative Analysis
5. Real-World Applications
   • Use Cases in Database Design
   • Examples in E-Commerce, Banking, and Healthcare Systems
6. Advanced Concepts
   • Self-Referencing Foreign Keys
   • Surrogate Keys vs. Natural Keys
7. Conclusion
   • Summary of Key Points
   • Final Thoughts on Database Integrity

---

1. Introduction

What are Primary Keys and Foreign Keys?

In relational databases, keys are essential for identifying records and establishing relationships between tables:

• Primary Key: A column or a set of columns that uniquely identifies each row in a table. It ensures that no two rows have the same value in the primary key column(s) and that no value is NULL.
• Foreign Key: A column or a set of columns in one table that uniquely identifies a row in another table. It establishes a link between the data in two tables, enforcing referential integrity.

Importance in Relational Database Design

• Data Integrity: Keys ensure that the data is accurate and consistent across the database.
• Normalization: They help in organizing data to reduce redundancy and dependency.
• Relationships: Keys define relationships between tables, facilitating complex queries and data retrieval.

---

2. Primary Key

Definition and Characteristics

A primary key has the following characteristics:

• Uniqueness: Each value in the primary key column(s) must be unique.
• Non-Null: No value in the primary key column(s) can be NULL.
• Immutability: The values in the primary key column(s) should not change over time.

Syntax and Examples

CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

In this example, student_id is the primary key of the Students table.

Composite Primary Keys

A composite primary key is a primary key composed of multiple columns:

CREATE TABLE Enrollments (
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    PRIMARY KEY (student_id, course_id)
);

Here, the combination of student_id and course_id uniquely identifies each record.

Best Practices

• Choose Stable Columns: Select columns that are unlikely to change as primary keys.
• Avoid Using Personal Information: Refrain from using personal data like email addresses as primary keys.
• Use Surrogate Keys When Necessary: Surrogate keys (artificial keys) can be used when natural keys are not suitable.

---

3. Foreign Key

Definition and Purpose

A foreign key is a column or set of columns that establishes a link between the data in two tables. It refers to the primary key in another table, ensuring referential integrity.

Syntax and Examples

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

In this example, customer_id in the Orders table is a foreign key that references the customer_id in the Customers table.

Referential Integrity Constraints

Foreign keys enforce referential integrity by ensuring that:

• Valid References: A foreign key value must match an existing primary key value in the referenced table.
• No Orphan Records: Prevents records in the child table from referencing non-existent records in the parent table.

Cascading Actions

Foreign keys can define cascading actions to maintain data integrity:

• ON DELETE CASCADE: Deletes rows in the child table when the corresponding row in the parent table is deleted.
• ON UPDATE CASCADE: Updates rows in the child table when the corresponding row in the parent table is updated.
• ON DELETE SET NULL: Sets the foreign key column values to NULL in the child table when the corresponding row in the parent table is deleted.
• ON UPDATE SET NULL: Sets the foreign key column values to NULL in the child table when the corresponding row in the parent table is updated.

Example:

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id)
        REFERENCES Customers(customer_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

Best Practices

• Index Foreign Key Columns: Indexing foreign key columns can improve query performance.
• Use Appropriate Data Types: Ensure that foreign key columns have the same data type as the referenced primary key columns.
• Avoid Circular References: Do not create circular foreign key relationships, as they can lead to integrity issues.

---

4. Differences Between Primary Key and Foreign Key

Feature	Primary Key	Foreign Key
Purpose	Uniquely identifies each record in a table	Establishes a link between two tables
Uniqueness	Must be unique	Can have duplicate values
Nullability	Cannot be NULL	Can be NULL
Number per Table	One per table	Multiple per table
Referential Integrity	Ensures each record is unique	Ensures valid references between tables
Indexing	Automatically indexed	May require manual indexing

---

5. Real-World Applications

E-Commerce Systems

In an e-commerce database:

• Customers Table: Contains customer information with customer_id as the primary key.
• Orders Table: Contains order details with order_id as the primary key and customer_id as a foreign key referencing the Customers table.

This structure ensures that each order is associated with a valid customer.

Banking Systems

In a banking database:

• Accounts Table: Contains account details with account_id as the primary key.
• Transactions Table: Contains transaction records with transaction_id as the primary key and account_id as a foreign key referencing the Accounts table.

This setup maintains the relationship between accounts and their transactions.

Healthcare Systems

In a healthcare database:

• Patients Table: Contains patient information with patient_id as the primary key.
• Appointments Table: Contains appointment records with appointment_id as the primary key and patient_id as a foreign key referencing the Patients table.

This design links patients to their appointments.

---

6. Advanced Concepts

Self-Referencing Foreign Keys

A self-referencing foreign key is a foreign key that references the primary key of the same table. This is useful for hierarchical data structures.

Example:

CREATE TABLE Employees (