Advanced modeling with all constraints: PRIMARY KEY, FOREIGN KEY, NOT NULL, DEFAULT, CHECK, UNIQUE
Create a complete e-commerce database with:
Requirement: Include all appropriate constraints (PRIMARY KEY, FOREIGN KEY, NOT NULL, DEFAULT, CHECK)
Primary Key: customer_id
Unique Constraints: email
Attributes:
Primary Key: product_id
CHECK Constraints: price >= 0, stock_quantity >= 0
Attributes:
Primary Key: category_id
Foreign Key: parent_category_id references CATEGORIES(category_id)
Unique Constraints: name
Attributes:
Example hierarchy: Electronics → Computers → Laptops
Primary Key: order_id
Foreign Key: customer_id references CUSTOMERS(customer_id)
CHECK Constraints: total_amount >= 0, status IN (Pending, Processing, Shipped, Delivered, Cancelled)
Attributes:
Primary Key: order_item_id
Foreign Keys: order_id, product_id
CHECK Constraints: quantity > 0, price_at_purchase >= 0
Attributes:
Why price_at_purchase? Product prices may change over time, but historical orders must show the price at time of purchase.
Composite Primary Key: (product_id, category_id)
Foreign Keys: product_id, category_id
Attributes:
Purpose: Allows products to belong to multiple categories (e.g., a laptop in both "Electronics" and "Gaming")
-- E-COMMERCE DATABASE SCHEMA
-- All constraints: PK, FK, UNIQUE, NOT NULL, DEFAULT, CHECK
-- 1. CUSTOMERS
CREATE TABLE CUSTOMERS (
customer_id INT AUTO_INCREMENT,
email VARCHAR(255) NOT NULL,
password_hash VARCHAR(255) NOT NULL,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
phone VARCHAR(20),
shipping_address TEXT,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
-- Constraints
PRIMARY KEY (customer_id),
UNIQUE KEY (email),
INDEX idx_email (email),
INDEX idx_name (last_name, first_name)
) ENGINE=InnoDB;
-- 2. CATEGORIES (with self-referencing FK)
CREATE TABLE CATEGORIES (
category_id INT AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
parent_category_id INT,
description TEXT,
-- Constraints
PRIMARY KEY (category_id),
UNIQUE KEY (name),
FOREIGN KEY (parent_category_id) REFERENCES CATEGORIES(category_id)
ON DELETE SET NULL
ON UPDATE CASCADE,
INDEX idx_parent (parent_category_id)
) ENGINE=InnoDB;
-- 3. PRODUCTS
CREATE TABLE PRODUCTS (
product_id INT AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
description TEXT,
price DECIMAL(10,2) NOT NULL,
stock_quantity INT DEFAULT 0,
is_active BOOLEAN DEFAULT TRUE,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
-- Constraints
PRIMARY KEY (product_id),
CHECK (price >= 0),
CHECK (stock_quantity >= 0),
INDEX idx_name (name),
INDEX idx_price (price),
INDEX idx_stock (stock_quantity)
) ENGINE=InnoDB;
-- 4. PRODUCT_CATEGORIES (junction table)
CREATE TABLE PRODUCT_CATEGORIES (
product_id INT NOT NULL,
category_id INT NOT NULL,
-- Constraints
PRIMARY KEY (product_id, category_id),
FOREIGN KEY (product_id) REFERENCES PRODUCTS(product_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY (category_id) REFERENCES CATEGORIES(category_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
INDEX idx_product (product_id),
INDEX idx_category (category_id)
) ENGINE=InnoDB;
-- 5. ORDERS
CREATE TABLE ORDERS (
order_id INT AUTO_INCREMENT,
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,
-- Constraints
PRIMARY KEY (order_id),
FOREIGN KEY (customer_id) REFERENCES CUSTOMERS(customer_id)
ON DELETE RESTRICT
ON UPDATE CASCADE,
CHECK (total_amount >= 0),
CHECK (status IN ('Pending', 'Processing', 'Shipped', 'Delivered', 'Cancelled')),
INDEX idx_customer (customer_id),
INDEX idx_order_date (order_date),
INDEX idx_status (status)
) ENGINE=InnoDB;
-- 6. ORDER_ITEMS
CREATE TABLE ORDER_ITEMS (
order_item_id INT AUTO_INCREMENT,
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
price_at_purchase DECIMAL(10,2) NOT NULL,
-- Constraints
PRIMARY KEY (order_item_id),
FOREIGN KEY (order_id) REFERENCES ORDERS(order_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY (product_id) REFERENCES PRODUCTS(product_id)
ON DELETE RESTRICT
ON UPDATE CASCADE,
CHECK (quantity > 0),
CHECK (price_at_purchase >= 0),
INDEX idx_order (order_id),
INDEX idx_product (product_id)
) ENGINE=InnoDB;
-- SAMPLE DATA
INSERT INTO CUSTOMERS (email, password_hash, first_name, last_name, phone, shipping_address) VALUES
('john@example.com', 'hashed_password_1', 'John', 'Doe', '555-0001', '123 Main St, City, 12345'),
('jane@example.com', 'hashed_password_2', 'Jane', 'Smith', '555-0002', '456 Oak Ave, Town, 67890');
INSERT INTO CATEGORIES (name, parent_category_id, description) VALUES
('Electronics', NULL, 'Electronic devices'),
('Computers', 1, 'Computer hardware'),
('Laptops', 2, 'Portable computers'),
('Gaming', 1, 'Gaming equipment');
INSERT INTO PRODUCTS (name, description, price, stock_quantity) VALUES
('Gaming Laptop', '15-inch gaming laptop with RTX 4060', 1299.99, 25),
('Wireless Mouse', 'Ergonomic wireless mouse', 29.99, 150),
('Mechanical Keyboard', 'RGB mechanical keyboard', 89.99, 75);
INSERT INTO PRODUCT_CATEGORIES (product_id, category_id) VALUES
(1, 3), -- Laptop in Laptops category
(1, 4), -- Laptop also in Gaming category
(2, 2), -- Mouse in Computers
(3, 2); -- Keyboard in Computers
INSERT INTO ORDERS (customer_id, total_amount, status, shipping_address) VALUES
(1, 1419.97, 'Delivered', '123 Main St, City, 12345');
INSERT INTO ORDER_ITEMS (order_id, product_id, quantity, price_at_purchase) VALUES
(1, 1, 1, 1299.99),
(1, 2, 1, 29.99),
(1, 3, 1, 89.99);| Constraint Type | Tables Using It | Purpose |
|---|---|---|
| PRIMARY KEY | All tables | Unique identifier for each row |
| FOREIGN KEY | CATEGORIES (self), PRODUCT_CATEGORIES, ORDERS, ORDER_ITEMS | Maintains referential integrity between tables |
| UNIQUE | CUSTOMERS (email), CATEGORIES (name) | Ensures no duplicate emails or category names |
| NOT NULL | Critical fields in all tables | Ensures required data is always present |
| DEFAULT | created_at, status, is_active, stock_quantity | Provides sensible default values |
| CHECK | PRODUCTS (price, stock), ORDERS (total, status), ORDER_ITEMS (quantity, price) | Enforces business rules (positive values, valid statuses) |
| Relationship | ON DELETE | ON UPDATE | Rationale |
|---|---|---|---|
| ORDERS → CUSTOMERS | RESTRICT | CASCADE | Cannot delete customer with orders (historical data) |
| ORDER_ITEMS → ORDERS | CASCADE | CASCADE | If order deleted, delete its items |
| ORDER_ITEMS → PRODUCTS | RESTRICT | CASCADE | Cannot delete products in active orders |
| PRODUCT_CATEGORIES → PRODUCTS | CASCADE | CASCADE | If product deleted, remove category associations |
| CATEGORIES → CATEGORIES (self) | SET NULL | CASCADE | If parent deleted, children become top-level |
-- Get product with all its categories
SELECT
p.name AS product_name,
p.price,
GROUP_CONCAT(c.name SEPARATOR ', ') AS categories
FROM PRODUCTS p
LEFT JOIN PRODUCT_CATEGORIES pc ON p.product_id = pc.product_id
LEFT JOIN CATEGORIES c ON pc.category_id = c.category_id
WHERE p.product_id = 1
GROUP BY p.product_id;
-- Get category hierarchy
SELECT
c1.name AS category,
c2.name AS parent,
c3.name AS grandparent
FROM CATEGORIES c1
LEFT JOIN CATEGORIES c2 ON c1.parent_category_id = c2.category_id
LEFT JOIN CATEGORIES c3 ON c2.parent_category_id = c3.category_id;
-- Order details with customer and items
SELECT
o.order_id,
CONCAT(c.first_name, ' ', c.last_name) AS customer,
o.order_date,
p.name AS product,
oi.quantity,
oi.price_at_purchase,
o.status
FROM ORDERS o
JOIN CUSTOMERS c ON o.customer_id = c.customer_id
JOIN ORDER_ITEMS oi ON o.order_id = oi.order_id
JOIN PRODUCTS p ON oi.product_id = p.product_id
WHERE o.order_id = 1;