Referential Integrity Rules - Complete Analysis

Understanding ON DELETE and ON UPDATE actions in e-commerce context

Problem Statement

For your e-commerce database, define appropriate referential integrity rules:

What happens when a Customer is deleted? (CASCADE or SET NULL for Orders?)
What happens when a Product is deleted while it's in active Orders?
What happens when a Category parent is deleted?
Should you allow Order deletion if it has Order_Items?

1. Understanding Referential Integrity Actions

Action	ON DELETE	ON UPDATE
CASCADE	When parent deleted, automatically delete children	When parent key updated, automatically update children
RESTRICT	Prevent parent deletion if children exist	Prevent parent key update if children exist
SET NULL	When parent deleted, set child FK to NULL	When parent key updated, set child FK to NULL
SET DEFAULT	When parent deleted, set child FK to default value	When parent key updated, set child FK to default value
NO ACTION	Similar to RESTRICT (depends on DBMS)	Similar to RESTRICT (depends on DBMS)

2. Scenario Analysis

Scenario 1: Customer Deletion

Question: What happens when a Customer is deleted? (CASCADE or SET NULL for Orders?)

Option A: CASCADE

FOREIGN KEY (customer_id) REFERENCES CUSTOMERS(customer_id)
    ON DELETE CASCADE

Effect: Deleting a customer automatically deletes all their orders and order items.

Pros: Clean removal, no orphaned data

Cons:

Loss of historical data (sales records, revenue tracking)
Cannot analyze past customer behavior
Regulatory/legal issues (financial records must be kept)
Accidental deletion is catastrophic

Verdict: NOT RECOMMENDED for e-commerce

Option B: SET NULL

FOREIGN KEY (customer_id) REFERENCES CUSTOMERS(customer_id)
    ON DELETE SET NULL

Effect: Deleting a customer sets customer_id to NULL in Orders table.

Pros: Preserves order history

Cons:

Orders exist without knowing who placed them
Cannot contact customer about order issues
Breaks business logic (orders must have a customer)

Verdict: NOT RECOMMENDED - customer_id should be NOT NULL

Option C: RESTRICT (Recommended)

FOREIGN KEY (customer_id) REFERENCES CUSTOMERS(customer_id)
    ON DELETE RESTRICT

Effect: Cannot delete customer if they have orders.

Pros:

Preserves all historical data
Prevents accidental data loss
Meets regulatory requirements
Can still "soft delete" (add is_active flag to CUSTOMERS)

Best Practice: Use soft deletion instead

-- Add to CUSTOMERS table
ALTER TABLE CUSTOMERS ADD COLUMN is_active BOOLEAN DEFAULT TRUE;

-- "Delete" customer (soft delete)
UPDATE CUSTOMERS SET is_active = FALSE WHERE customer_id = 123;

-- Filter out inactive customers in queries
SELECT * FROM CUSTOMERS WHERE is_active = TRUE;

Verdict: RECOMMENDED

Scenario 2: Product Deletion with Active Orders

Question: What happens when a Product is deleted while it's in active Orders?

Option A: CASCADE

FOREIGN KEY (product_id) REFERENCES PRODUCTS(product_id)
    ON DELETE CASCADE

Effect: Deleting a product removes it from all order items.

Cons:

Historical orders become incomplete
Order totals won't match itemized amounts
Cannot process returns/exchanges
Legal/accounting issues

Verdict: NEVER use this for products in orders

Option B: RESTRICT (Recommended)

FOREIGN KEY (product_id) REFERENCES PRODUCTS(product_id)
    ON DELETE RESTRICT

Effect: Cannot delete product if it appears in any order items.

Pros:

Protects historical data completely
Order integrity maintained
Can still discontinue products (use is_active flag)

Implementation:

-- Add to PRODUCTS table
ALTER TABLE PRODUCTS ADD COLUMN is_active BOOLEAN DEFAULT TRUE;

-- Discontinue product (soft delete)
UPDATE PRODUCTS SET is_active = FALSE WHERE product_id = 456;

-- Show only active products to customers
SELECT * FROM PRODUCTS WHERE is_active = TRUE;

Verdict: RECOMMENDED

Scenario 3: Category Parent Deletion

Question: What happens when a Category parent is deleted?

Option A: CASCADE

FOREIGN KEY (parent_category_id) REFERENCES CATEGORIES(category_id)
    ON DELETE CASCADE

Effect: Deleting a parent category deletes all subcategories.

Example: Delete "Electronics" → also deletes "Computers", "Laptops", "Gaming"

Pros: Maintains logical hierarchy

Cons: Can delete many categories unintentionally

Use case: When subcategories have no meaning without parent

Option B: SET NULL (Recommended)

FOREIGN KEY (parent_category_id) REFERENCES CATEGORIES(category_id)
    ON DELETE SET NULL

Effect: Deleting a parent category makes children top-level categories.

Example: Delete "Electronics" → "Computers" becomes top-level

Pros:

Preserves subcategories
Allows manual reorganization
No data loss

Verdict: RECOMMENDED - more flexible

Option C: RESTRICT

FOREIGN KEY (parent_category_id) REFERENCES CATEGORIES(category_id)
    ON DELETE RESTRICT

Effect: Cannot delete parent if it has subcategories.

Pros: Forces manual cleanup before deletion

Use case: When you want explicit control over category management

Scenario 4: Order Deletion with Order Items

Question: Should you allow Order deletion if it has Order_Items?

Option A: CASCADE (Recommended)

FOREIGN KEY (order_id) REFERENCES ORDERS(order_id)
    ON DELETE CASCADE

Effect: Deleting an order automatically deletes all its order items.

Rationale: Order items have no meaning without their parent order.

Pros:

Maintains data consistency
Prevents orphaned order items
Simplifies deletion logic

Note: In practice, use soft deletion for orders too:

-- Add to ORDERS table
ALTER TABLE ORDERS ADD COLUMN status VARCHAR(50);

-- Statuses: Pending, Processing, Shipped, Delivered, Cancelled
UPDATE ORDERS SET status = 'Cancelled' WHERE order_id = 789;

Verdict: CASCADE is correct here

3. Complete E-commerce Schema with Recommended Rules

-- CUSTOMERS Table
CREATE TABLE CUSTOMERS (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,  -- For soft deletion
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- CATEGORIES Table (self-referencing)
CREATE TABLE CATEGORIES (
    category_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    parent_category_id INT,
    description TEXT,
    FOREIGN KEY (parent_category_id) REFERENCES CATEGORIES(category_id)
        ON DELETE SET NULL  -- Children become top-level
        ON UPDATE CASCADE
) ENGINE=InnoDB;

-- PRODUCTS Table
CREATE TABLE PRODUCTS (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock_quantity INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,  -- For soft deletion
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    CHECK (price >= 0),
    CHECK (stock_quantity >= 0)
) ENGINE=InnoDB;

-- ORDERS Table
CREATE TABLE ORDERS (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(10,2) NOT NULL,
    status VARCHAR(50) DEFAULT 'Pending',
    shipping_address TEXT NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES CUSTOMERS(customer_id)
        ON DELETE RESTRICT  -- Prevent deletion of customers with orders
        ON UPDATE CASCADE,
    CHECK (total_amount >= 0),
    CHECK (status IN ('Pending', 'Processing', 'Shipped', 'Delivered', 'Cancelled'))
) ENGINE=InnoDB;

-- ORDER_ITEMS Table
CREATE TABLE ORDER_ITEMS (
    order_item_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price_at_purchase DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES ORDERS(order_id)
        ON DELETE CASCADE  -- If order deleted, delete items
        ON UPDATE CASCADE,
    FOREIGN KEY (product_id) REFERENCES PRODUCTS(product_id)
        ON DELETE RESTRICT  -- Prevent deletion of products in orders
        ON UPDATE CASCADE,
    CHECK (quantity > 0),
    CHECK (price_at_purchase >= 0)
) ENGINE=InnoDB;

4. Decision Matrix

Relationship	ON DELETE	Reason
ORDERS → CUSTOMERS	RESTRICT	Preserve historical data, use soft deletion
ORDER_ITEMS → ORDERS	CASCADE	Items are meaningless without their order
ORDER_ITEMS → PRODUCTS	RESTRICT	Preserve order history, use soft deletion
PRODUCT_CATEGORIES → PRODUCTS	CASCADE	Category associations are dependent data
PRODUCT_CATEGORIES → CATEGORIES	CASCADE	Remove associations when category deleted
CATEGORIES → CATEGORIES (parent)	SET NULL	Preserve subcategories, make them top-level

5. Soft Deletion Pattern

For critical entities (Customers, Products, Orders), use soft deletion:

-- Add is_active or status column
ALTER TABLE CUSTOMERS ADD COLUMN is_active BOOLEAN DEFAULT TRUE;
ALTER TABLE PRODUCTS ADD COLUMN is_active BOOLEAN DEFAULT TRUE;

-- "Delete" by marking inactive
UPDATE CUSTOMERS SET is_active = FALSE WHERE customer_id = 123;
UPDATE PRODUCTS SET is_active = FALSE WHERE product_id = 456;

-- Queries filter inactive records
SELECT * FROM CUSTOMERS WHERE is_active = TRUE;
SELECT * FROM PRODUCTS WHERE is_active = TRUE AND stock_quantity > 0;

-- For orders, use status field
UPDATE ORDERS SET status = 'Cancelled' WHERE order_id = 789;

-- Advantages:
-- ✓ Preserve all historical data
-- ✓ Can reactivate if needed
-- ✓ Meets regulatory requirements
-- ✓ Enables data analysis
-- ✓ Prevents accidental data loss

6. Testing Referential Integrity

-- Test 1: Try to delete customer with orders (should fail with RESTRICT)
DELETE FROM CUSTOMERS WHERE customer_id = 1;
-- Error: Cannot delete or update a parent row

-- Test 2: Try to delete product in orders (should fail with RESTRICT)
DELETE FROM PRODUCTS WHERE product_id = 1;
-- Error: Cannot delete or update a parent row

-- Test 3: Delete order (should CASCADE to order_items)
DELETE FROM ORDERS WHERE order_id = 1;
-- Success: Order and all its items deleted

-- Test 4: Delete parent category (should SET NULL for children)
DELETE FROM CATEGORIES WHERE category_id = 1;
-- Success: Children's parent_category_id set to NULL

-- Test 5: Soft delete customer (recommended approach)
UPDATE CUSTOMERS SET is_active = FALSE WHERE customer_id = 1;
-- Success: Customer deactivated, orders preserved

Previous: E-commerce Exercise Back to Lecture 2