Quote from Admin on 02/12/2024, 20:18

Primary and Foreign Keys play a crucial role in relational databases by defining relationships between tables. Understanding these keys is essential for writing efficient joins.

---

What Are Primary and Foreign Keys?

1. Primary Key: A unique identifier for each row in a table.

   • Example: CustomerID in a Customers table.
   • Each value in the primary key column is unique and cannot be NULL.

2. Foreign Key: A column in one table that references the primary key in another table.

   • Example: CustomerID in an Orders table referencing the CustomerID in the Customers table.
   • It establishes a relationship between the two tables.

---

Real-Life Example of Primary and Foreign Keys

Let’s create two tables:

1. Customers Table

CustomerID (PK)	CustomerName	Country
1	Alice	USA
2	Bob	UK
3	Charlie	Canada

2. Orders Table

OrderID (PK)	CustomerID (FK)	OrderAmount
101	1	200
102	2	150
103	4	300

Here:

• CustomerID in the Customers table is the Primary Key.
• CustomerID in the Orders table is the Foreign Key referencing Customers.CustomerID.

---

Using Joins with Primary and Foreign Keys

1. INNER JOIN

• Retrieves rows where the foreign key in one table matches the primary key in another.

Query:

SELECT Customers.CustomerName, Orders.OrderAmount
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

Result:

CustomerName	OrderAmount
Alice	200
Bob	150

Explanation:

• The INNER JOIN combines rows where CustomerID in Orders matches CustomerID in Customers.
• The order with CustomerID = 4 is excluded because it doesn’t have a matching customer.

---

2. LEFT JOIN

• Returns all rows from the table with the primary key (left table) and matching rows from the table with the foreign key.

Query:

SELECT Customers.CustomerName, Orders.OrderAmount
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

Result:

CustomerName	OrderAmount
Alice	200
Bob	150
Charlie	NULL

Explanation:

• All customers are included.
• “Charlie” has no orders, so OrderAmount is NULL.

---

3. RIGHT JOIN

• Returns all rows from the table with the foreign key (right table) and matching rows from the table with the primary key.

Query:

SELECT Customers.CustomerName, Orders.OrderAmount
FROM Customers
RIGHT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

Result:

CustomerName	OrderAmount
Alice	200
Bob	150
NULL	300

Explanation:

• All orders are included.
• The order with CustomerID = 4 has no matching customer, so CustomerName is NULL.

---

How Primary and Foreign Keys Ensure Data Integrity

1. Foreign Key Constraint: Ensures that the value in the foreign key column matches a value in the primary key column of the referenced table.

   • Example: CustomerID in the Orders table must exist in the Customers table.

2. Prevent Orphan Records: You cannot insert a row in the Orders table with a CustomerID that doesn’t exist in the Customers table.
3. Cascade Updates/Deletes: Foreign key constraints can be configured to automatically update or delete dependent rows:

   • ON DELETE CASCADE: Deletes orders when a customer is deleted.
   • ON UPDATE CASCADE: Updates CustomerID in Orders if it changes in Customers.

Creating a Foreign Key Constraint:

ALTER TABLE Orders
ADD CONSTRAINT FK_CustomerID
FOREIGN KEY (CustomerID)
REFERENCES Customers(CustomerID)
ON DELETE CASCADE
ON UPDATE CASCADE;

---

4. FULL OUTER JOIN

• Combines rows from both tables, including unmatched rows from each.

Query:

SELECT Customers.CustomerName, Orders.OrderAmount
FROM Customers
FULL OUTER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

Result:

CustomerName	OrderAmount
Alice	200
Bob	150
Charlie	NULL
NULL	300

---

5. SELF JOIN

• A table joins with itself using a foreign key relationship.

Example: Employees Table

EmployeeID (PK)	EmployeeName	ManagerID (FK)
1	John	NULL
2	Alice	1
3	Bob	1
4	Charlie	2

Query:

SELECT E1.EmployeeName AS Employee, E2.EmployeeName AS Manager
FROM Employees E1
LEFT JOIN Employees E2
ON E1.ManagerID = E2.EmployeeID;

Result:

Employee	Manager
John	NULL
Alice	John
Bob	John
Charlie	Alice

Explanation:

• ManagerID is a foreign key referencing EmployeeID.
• The table is joined with itself to pair employees with their managers.

---

Best Practices for Using Primary and Foreign Keys in Joins

1. Index Primary Keys: Always ensure primary key columns are indexed for faster joins.
2. Use Aliases: Use table aliases (Customers C, Orders O) for clarity when joining.
3. Enforce Constraints: Define foreign key constraints to maintain data integrity.
4. Filter Early: Use WHERE clauses to limit rows before joining large tables.

---

Summary

Understanding how primary and foreign keys define relationships helps you write efficient joins and maintain data integrity. Practice with real-world examples to solidify these concepts.