E-commerce Order Constraints

intermediate

Build order system with constraints ensuring: positive totals, valid discounts, existing customers, valid status values.

CREATE TABLE orders (

order_id SERIAL,

customer_id INT NOT NULL,

total DECIMAL(10,2) NOT NULL,

discount DECIMAL(10,2) DEFAULT 0,

status VARCHAR(20) DEFAULT 'pending',

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

CONSTRAINT pk_orders PRIMARY KEY (order_id),

CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id)

REFERENCES customers(customer_id) ON DELETE RESTRICT,

CONSTRAINT chk_orders_positive_total CHECK (total > 0),

CONSTRAINT chk_orders_valid_discount CHECK (discount >= 0 AND discount <= total),

CONSTRAINT chk_orders_valid_status CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled'))

);

-- Test constraints

INSERT INTO orders (customer_id, total, discount) VALUES (1, 100.00, 10.00); -- Success

INSERT INTO orders (customer_id, total, discount) VALUES (1, -50.00, 0); -- ERROR: chk_orders_positive_total

INSERT INTO orders (customer_id, total, discount) VALUES (1, 100.00, 150.00); -- ERROR: chk_orders_valid_discount

INSERT INTO orders (customer_id, total, status) VALUES (999, 100.00, 'pending'); -- ERROR: fk_orders_customers

Constraints prevent: negative totals, discounts > total, invalid status, non-existent customers

Multiple constraints work together to ensure data integrity. CHECK constraints validate business rules. FOREIGN KEY ensures referential integrity. Named constraints provide clear error messages.

All

graph TD

A[INSERT Order] --> B{customer_id exists?}

B -->|No| C[ERROR: fk_orders_customers]

B -->|Yes| D{total > 0?}

D -->|No| E[ERROR: chk_orders_positive_total]

D -->|Yes| F{discount valid?}

F -->|No| G[ERROR: chk_orders_valid_discount]

F -->|Yes| H{status valid?}

H -->|No| I[ERROR: chk_orders_valid_status]

H -->|Yes| J[INSERT Success]

style C fill:#FFB6C6

style E fill:#FFB6C6

style G fill:#FFB6C6

style I fill:#FFB6C6

style J fill:#90EE90