2. Primary Keys, Foreign Keys, and Relationships

1. Primary Key (PK)

A Primary Key is a column (or a combination of columns) that uniquely identifies each row in a table.
- Ensures uniqueness: No two rows can have the same primary key value.
- Cannot be NULL.
- Often an integer (e.g., ID fields).

Example:

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,  -- Unique ID for each customer
    Name VARCHAR(100),
    Email VARCHAR(100) UNIQUE,
    Age INT
);

| CustomerID (PK) | Name | Email | Age |
|-----------------|-----------|--------------------|------|
| 1 | John Doe | john@example.com | 30 |
| 2 | Jane Smith | jane@example.com | 25 |

---

2. Foreign Key (FK)

A Foreign Key is a column (or set of columns) that references a Primary Key in another table.
- Establishes relationships between tables.
- Enforces referential integrity (ensures valid references).
- Can be NULL (if optional).

Example:

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,  -- Unique ID for each order
    CustomerID INT,  -- References Customers table
    TotalAmount DECIMAL(10,2),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)  
);

| OrderID (PK) | CustomerID (FK) | TotalAmount |
|---------------|----------------|--------------|
| 101 | 1 | 150.00 |
| 102 | 2 | 200.00 |

• Here, CustomerID in Orders is a Foreign Key, linking to CustomerID in Customers.

---

3. Types of Relationships

a) One-to-One (1:1)

Each record in Table A relates to one record in Table B.
- Example: Each employee has one office.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100)
);

CREATE TABLE Offices (
    OfficeID INT PRIMARY KEY,
    EmployeeID INT UNIQUE,  
    Location VARCHAR(100),
    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)
);

b) One-to-Many (1:M) (Most Common)

Each record in Table A relates to multiple records in Table B.
- Example: One customer can place many orders.

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(100)
);

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

c) Many-to-Many (M:M)

Requires a junction table to manage relationships.
- Example: Students and Courses (A student can enroll in many courses, and a course can have many students).

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(100)
);

CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(100)
);

CREATE TABLE Enrollments (  -- Junction Table
    StudentID INT,
    CourseID INT,
    PRIMARY KEY (StudentID, CourseID),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

| StudentID (FK) | CourseID (FK) |
|-----------------|----------------|
| 1 | 101 |
| 1 | 102 |
| 2 | 101 |

---

Key Takeaways

• Primary Key → Uniquely identifies each row.
• Foreign Key → References a primary key in another table to establish relationships.
• One-to-One (1:1) → Rare; one record links to one other record.
• One-to-Many (1:M) → Common; one record links to multiple records.
• Many-to-Many (M:M) → Requires a junction table.

---