Foreign Key Relationships

Objective

Create related tables with FOREIGN KEY constraints to establish referential integrity between tables. Foreign keys ensure that relationships between tables remain consistent.

Step 1: Create Parent Table (Departments)

Create the parent table that will be referenced by other tables.

CREATE TABLE departments (
    department_id INTEGER PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL UNIQUE,
    location VARCHAR(255),
    budget DECIMAL(12, 2)
);

INSERT INTO departments (department_id, department_name, location, budget) VALUES
    (1, 'Engineering', 'Building A, Floor 3', 500000.00),
    (2, 'Marketing', 'Building B, Floor 1', 250000.00),
    (3, 'Sales', 'Building B, Floor 2', 350000.00),
    (4, 'Human Resources', 'Building A, Floor 1', 150000.00);

Step 2: Create Child Table (Employees)

Create a table with a foreign key reference to departments.

CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE,
    department_id INTEGER,
    hire_date DATE,
    salary DECIMAL(10, 2),
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

INSERT INTO employees (employee_id, first_name, last_name, email, department_id, hire_date, salary) VALUES
    (1, 'Alice', 'Johnson', 'alice.j@company.com', 1, '2022-03-15', 85000.00),
    (2, 'Bob', 'Smith', 'bob.s@company.com', 1, '2021-07-20', 92000.00),
    (3, 'Carol', 'Williams', 'carol.w@company.com', 2, '2023-01-10', 75000.00),
    (4, 'David', 'Brown', 'david.b@company.com', 3, '2022-09-05', 68000.00),
    (5, 'Eve', 'Davis', 'eve.d@company.com', 3, '2023-06-01', 72000.00);

Step 3: Create Projects Table with Multiple Foreign Keys

Create a table that references multiple parent tables.

CREATE TABLE projects (
    project_id INTEGER PRIMARY KEY,
    project_name VARCHAR(255) NOT NULL,
    department_id INTEGER NOT NULL,
    lead_employee_id INTEGER,
    start_date DATE,
    end_date DATE,
    budget DECIMAL(12, 2),
    status VARCHAR(20) DEFAULT 'planning',
    FOREIGN KEY (department_id) REFERENCES departments(department_id),
    FOREIGN KEY (lead_employee_id) REFERENCES employees(employee_id)
);

INSERT INTO projects (project_id, project_name, department_id, lead_employee_id, start_date, budget, status) VALUES
    (1, 'Website Redesign', 1, 1, '2024-01-01', 50000.00, 'active'),
    (2, 'Q1 Campaign', 2, 3, '2024-01-15', 25000.00, 'active'),
    (3, 'Sales Portal', 3, 4, '2024-02-01', 35000.00, 'planning');

Step 4: Create Order System with Foreign Keys

Create a complete order system with multiple related tables.

-- Customers table
CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    customer_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE,
    phone VARCHAR(20)
);

-- Orders table referencing customers
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(10, 2),
    status VARCHAR(20) DEFAULT 'pending',
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Order items table referencing orders
CREATE TABLE order_items (
    item_id INTEGER PRIMARY KEY,
    order_id INTEGER NOT NULL,
    product_name VARCHAR(255) NOT NULL,
    quantity INTEGER NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

-- Insert sample data
INSERT INTO customers (customer_id, customer_name, email, phone) VALUES
    (1, 'Acme Corp', 'orders@acme.com', '555-0100'),
    (2, 'Tech Solutions', 'purchasing@techsol.com', '555-0200');

INSERT INTO orders (order_id, customer_id, total_amount, status) VALUES
    (1, 1, 1500.00, 'completed'),
    (2, 1, 750.00, 'shipped'),
    (3, 2, 2200.00, 'pending');

INSERT INTO order_items (item_id, order_id, product_name, quantity, unit_price) VALUES
    (1, 1, 'Widget Pro', 10, 100.00),
    (2, 1, 'Widget Basic', 20, 25.00),
    (3, 2, 'Widget Pro', 5, 100.00),
    (4, 2, 'Accessory Pack', 10, 25.00);

Step 5: Query with JOINs

Use JOIN to query related data across tables.

-- Get employees with their department names
SELECT
    e.first_name,
    e.last_name,
    d.department_name,
    e.salary
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
ORDER BY d.department_name, e.last_name;

-- Get projects with department and lead info
SELECT
    p.project_name,
    d.department_name,
    e.first_name || ' ' || e.last_name as project_lead,
    p.budget,
    p.status
FROM projects p
INNER JOIN departments d ON p.department_id = d.department_id
LEFT JOIN employees e ON p.lead_employee_id = e.employee_id;

-- Get orders with customer and item details
SELECT
    c.customer_name,
    o.order_id,
    o.order_date,
    oi.product_name,
    oi.quantity,
    oi.unit_price,
    (oi.quantity * oi.unit_price) as line_total
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
ORDER BY o.order_id, oi.item_id;

Step 6: Aggregate Across Related Tables

Perform aggregations across relationships.

-- Count employees per department
SELECT
    d.department_name,
    COUNT(e.employee_id) as employee_count,
    SUM(e.salary) as total_salaries
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name
ORDER BY employee_count DESC;

-- Order totals by customer
SELECT
    c.customer_name,
    COUNT(o.order_id) as order_count,
    SUM(o.total_amount) as total_spent
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;

Cleanup (Optional)

DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS projects;
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS departments;

Expected Outcomes

• Foreign keys establish table relationships
• JOINs retrieve data across related tables
• Referential integrity maintained
• Aggregations work across relationships

Key Concepts Learned

• FOREIGN KEY constraint syntax
• Parent and child table relationships
• Multiple foreign keys in one table
• INNER JOIN and LEFT JOIN operations
• Aggregating across related tables