Primary Keys and Foreign Keys

Duration: 75 minutes

Module 3: Session 2

Learning Objectives

Understand the purpose and importance of primary keys
Learn different types of primary keys (natural vs surrogate)
Master foreign key concepts and referential integrity
Implement one-to-one, one-to-many, and many-to-many relationships
Apply CASCADE operations and constraints
Design composite keys when appropriate

What are Database Keys?

Keys are the foundation of relational databases. They uniquely identify records and establish relationships between tables. Think of them as the ID cards and connections in your database! 🔑

💡

Key Concept

Every table should have a primary key, and foreign keys create the "relational" part of relational databases!

Primary Keys in Detail

Primary Key Rules

🔑Unique:No two rows can have the same primary key value
🚫Not NULL:Every row must have a primary key value
1️⃣Single:Only one primary key per table (but can be composite)
🔒Immutable:Should never change once assigned
⚡Indexed:Automatically creates a clustered index

Types of Primary Keys

Creating Primary Keys in MySQL

-- Method 1: Define during table creation
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100) UNIQUE NOT NULL,
    username VARCHAR(50) NOT NULL
);

-- Method 2: Natural key example
CREATE TABLE books (
    isbn VARCHAR(13) PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author VARCHAR(100)
);

-- Method 3: Composite primary key
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT NOT NULL,
    price DECIMAL(10,2),
    PRIMARY KEY (order_id, product_id)
);

-- Method 4: Add primary key to existing table
ALTER TABLE customers 
ADD PRIMARY KEY (customer_id);

-- Method 5: UUID as primary key
CREATE TABLE sessions (
    session_id CHAR(36) PRIMARY KEY DEFAULT (UUID()),
    user_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Foreign Keys and Relationships

graph TB subgraph "One-to-One" U1[User] ---|1:1| P1[Profile] end subgraph "One-to-Many" D1[Department] ---|1:N| E1[Employees] end subgraph "Many-to-Many" S1[Students] ---|M:N| C1[Courses] S1 ---|through| SC[student_courses] SC ---|through| C1 end style U1 fill:#dbeafe style D1 fill:#dcfce7 style S1 fill:#fef3c7 style SC fill:#e9d5ff

Foreign Key Rules

Must reference a PRIMARY KEY or UNIQUE column in another table
Values must exist in the referenced table (referential integrity)
Can be NULL (unless specified NOT NULL)
Multiple foreign keys allowed in one table
Can reference the same table (self-referencing)

Implementing Relationships

-- One-to-One Relationship
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE user_profiles (
    profile_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNIQUE NOT NULL,  -- UNIQUE ensures 1:1
    bio TEXT,
    avatar_url VARCHAR(255),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- One-to-Many Relationship
CREATE TABLE departments (
    dept_id INT AUTO_INCREMENT PRIMARY KEY,
    dept_name VARCHAR(50) NOT NULL
);

CREATE TABLE employees (
    emp_id INT AUTO_INCREMENT PRIMARY KEY,
    emp_name VARCHAR(100) NOT NULL,
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

-- Many-to-Many Relationship (requires junction table)
CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL
);

CREATE TABLE courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL,
    credits INT DEFAULT 3
);

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

Referential Integrity and CASCADE Operations

Implementing CASCADE Operations

-- CASCADE DELETE: Delete related records
CREATE TABLE posts (
    post_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(200),
    content TEXT,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
        ON DELETE CASCADE  -- Delete posts when user is deleted
);

-- SET NULL: Set foreign key to NULL
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    order_date DATETIME,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON DELETE SET NULL  -- Keep order but remove customer reference
);

-- RESTRICT: Prevent deletion (default)
CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT NOT NULL,
    product_name VARCHAR(100),
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
        ON DELETE RESTRICT  -- Can't delete category if products exist
);

-- CASCADE UPDATE: Update foreign keys
CREATE TABLE order_items (
    item_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT,
    product_sku VARCHAR(20),
    FOREIGN KEY (product_sku) REFERENCES products(sku)
        ON UPDATE CASCADE  -- Update SKU in order_items if product SKU changes
        ON DELETE RESTRICT
);

-- Multiple actions
CREATE TABLE comments (
    comment_id INT AUTO_INCREMENT PRIMARY KEY,
    post_id INT NOT NULL,
    user_id INT,
    comment_text TEXT,
    FOREIGN KEY (post_id) REFERENCES posts(post_id)
        ON DELETE CASCADE      -- Delete comment if post deleted
        ON UPDATE CASCADE,      -- Update if post_id changes
    FOREIGN KEY (user_id) REFERENCES users(user_id)
        ON DELETE SET NULL     -- Keep comment but anonymize if user deleted
        ON UPDATE CASCADE
);

⚠️

Caution with CASCADE DELETE

CASCADE DELETE can be dangerous! It can delete large amounts of data unintentionally. Always consider:

Use CASCADE DELETE only for truly dependent data
Consider SET NULL for historical records
Use RESTRICT when unsure
Test thoroughly in development

Self-Referencing Foreign Keys

graph TD E1[CEO
emp_id: 1
manager_id: NULL] E2[VP Sales
emp_id: 2
manager_id: 1] E3[VP Tech
emp_id: 3
manager_id: 1] E4[Sales Rep
emp_id: 4
manager_id: 2] E5[Developer
emp_id: 5
manager_id: 3] E1 --> E2 E1 --> E3 E2 --> E4 E3 --> E5 style E1 fill:#fee2e2 style E2 fill:#dcfce7 style E3 fill:#dbeafe style E4 fill:#fef3c7 style E5 fill:#e9d5ff

-- Self-referencing for hierarchical data
CREATE TABLE employees (
    emp_id INT AUTO_INCREMENT PRIMARY KEY,
    emp_name VARCHAR(100) NOT NULL,
    manager_id INT,
    hire_date DATE,
    FOREIGN KEY (manager_id) REFERENCES employees(emp_id)
        ON DELETE SET NULL
);

-- Insert hierarchical data
INSERT INTO employees (emp_name, manager_id) VALUES
('John CEO', NULL),           -- CEO has no manager
('Sarah VP Sales', 1),         -- Reports to CEO
('Mike VP Tech', 1),           -- Reports to CEO
('Lisa Sales Rep', 2),         -- Reports to VP Sales
('Tom Developer', 3);          -- Reports to VP Tech

-- Query to show hierarchy
SELECT 
    e1.emp_name AS employee,
    e2.emp_name AS manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.emp_id;

Best Practices for Keys

Primary Key Best Practices

✅ Use surrogate keys (AUTO_INCREMENT) for most tables
✅ Keep primary keys small (INT is usually sufficient)
✅ Never use sensitive data as primary keys
✅ Avoid composite keys unless necessary
✅ Consider UUID for distributed systems

Foreign Key Best Practices

✅ Always define foreign keys explicitly
✅ Name foreign keys descriptively (table_id format)
✅ Index foreign key columns for performance
✅ Choose appropriate CASCADE actions carefully
✅ Document relationships in your schema

🎯

Pro Tip

Consistent naming conventions make relationships clear:

Primary keys:idortablename_id
Foreign keys:referenced_table_id
Junction tables:table1_table2

Practice Exercise

💻

Build a Blog Database

Create a database schema with proper keys for a blog system:

Users can write multiple posts
Posts can have multiple comments
Posts can have multiple tags (many-to-many)
Comments belong to users and posts

💡 Click for Solution

-- Create database
CREATE DATABASE blog_system;
USE blog_system;

-- Users table
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Posts table
CREATE TABLE posts (
    post_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    published_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
        ON DELETE CASCADE
);

-- Tags table
CREATE TABLE tags (
    tag_id INT AUTO_INCREMENT PRIMARY KEY,
    tag_name VARCHAR(50) UNIQUE NOT NULL
);

-- Post-Tags junction table (many-to-many)
CREATE TABLE post_tags (
    post_id INT,
    tag_id INT,
    PRIMARY KEY (post_id, tag_id),
    FOREIGN KEY (post_id) REFERENCES posts(post_id)
        ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags(tag_id)
        ON DELETE CASCADE
);

-- Comments table
CREATE TABLE comments (
    comment_id INT AUTO_INCREMENT PRIMARY KEY,
    post_id INT NOT NULL,
    user_id INT,
    comment_text TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES posts(post_id)
        ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
        ON DELETE SET NULL
);

-- Create indexes for foreign keys
CREATE INDEX idx_posts_user ON posts(user_id);
CREATE INDEX idx_comments_post ON comments(post_id);
CREATE INDEX idx_comments_user ON comments(user_id);

PHP WordPress Development

Course Progress