Financial systems require exact precision (DECIMAL for money). Inventory systems need validation (CHECK for non-negative stock). User registration needs unique email enforcement. Multi-tenant SaaS normalizes user data from API JSON streams with TRY_CAST for analytics. Each relies on proper data types and constraints.

E-Commerce Product Catalog

Amazon-scale product database with proper data types and constraints:

PRODUCTS (

product_id INTEGER PRIMARY KEY,

sku VARCHAR(50) NOT NULL UNIQUE,

name VARCHAR(200) NOT NULL,

price DECIMAL(10,2) NOT NULL CHECK (price > 0),

inventory SMALLINT NOT NULL CHECK (inventory >= 0),

category VARCHAR(100) NOT NULL,

is_active BOOLEAN DEFAULT TRUE,

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

)

Constraints prevent invalid data at database level. Cannot insert negative prices, duplicate SKUs, or NULL required fields. Data integrity enforced automatically.

Prevent negative prices and inventory

INSERT INTO products VALUES (1, 'SKU-001', 'Laptop', 999.99, 50, 'Electronics', TRUE, NOW());

-- ❌ This fails: CHECK constraint violation

INSERT INTO products VALUES (2, 'SKU-002', 'Mouse', -10.00, 100, 'Electronics', TRUE, NOW());

Prevent duplicate SKUs

INSERT INTO products VALUES (3, 'SKU-001', 'Keyboard', 79.99, 30, 'Electronics', TRUE, NOW());

-- ❌ This fails: UNIQUE constraint violation on sku

All

Financial Transaction System

Banking system requiring exact precision for money:

TRANSACTIONS (

transaction_id BIGINT PRIMARY KEY,

account_id INTEGER NOT NULL,

amount DECIMAL(12,4) NOT NULL,

transaction_type VARCHAR(20) NOT NULL CHECK (transaction_type IN ('DEBIT', 'CREDIT', 'TRANSFER')),

timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(),

FOREIGN KEY (account_id) REFERENCES accounts(account_id)

)

DECIMAL ensures exact precision for financial calculations. CHECK constraints enforce valid transaction types. TIMESTAMPTZ handles timezone correctly for global operations.

Record transaction with exact precision

INSERT INTO transactions VALUES (9001, 1001, 123.4567, 'DEBIT', NOW());

-- DECIMAL(12,4) stores exactly 123.4567 (no rounding errors)

Prevent invalid transaction types

INSERT INTO transactions VALUES (9002, 1001, 50.00, 'INVALID', NOW());

-- ❌ This fails: CHECK constraint violation on transaction_type

All