PostgreSQL Deep Dive Part 5 - Mastering Constraints
Master PostgreSQL constraint implementation.
Published At
12/27/2024
Reading Time
~ 2 min read
Access complete, unfiltered study notes maintained on GitHub for easily maintaing Second Brain Dir
As we wrap up our journey through PostgreSQL's type system, let's tackle the critical aspects that transform a good database into a great one. Performance optimization isn't just about speed—it's about building a robust, maintainable system that gracefully handles growth while maintaining data integrity.
The Art of Constraints: Beyond Basic Rules 🎯
Let's dive into how constraints can make your database not just faster, but smarter.
Check Constraints: Your First Line of Defence
-- A simple example that evolves into something powerful
CREATE TABLE products (
price NUMERIC CHECK (price > 0),
discount_price NUMERIC,
inventory_count INTEGER DEFAULT 0,
-- Complex business rules as constraints
CONSTRAINT valid_discount
CHECK (discount_price IS NULL OR
(discount_price > 0 AND discount_price < price)),
CONSTRAINT valid_inventory
CHECK (inventory_count >= 0),
CONSTRAINT price_range
CHECK (price < 1000000)
);-- A simple example that evolves into something powerful
CREATE TABLE products (
price NUMERIC CHECK (price > 0),
discount_price NUMERIC,
inventory_count INTEGER DEFAULT 0,
-- Complex business rules as constraints
CONSTRAINT valid_discount
CHECK (discount_price IS NULL OR
(discount_price > 0 AND discount_price < price)),
CONSTRAINT valid_inventory
CHECK (inventory_count >= 0),
CONSTRAINT price_range
CHECK (price < 1000000)
);Domain-Level Constraints vs. Table Constraints
When should you use which? Let's explore through a real-world example:
-- Domain approach
CREATE DOMAIN price_amount AS NUMERIC
CONSTRAINT positive_price CHECK (VALUE > 0 AND VALUE < 1000000);
-- Table using domain
CREATE TABLE products_v2 (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
price price_amount,
discount_price price_amount,
-- Business logic remains at table level
CONSTRAINT valid_discount
CHECK (discount_price IS NULL OR discount_price < price)
);
-- The power of domains: reusability
CREATE TABLE order_items (
order_id INTEGER,
product_id INTEGER,
unit_price price_amount,
quantity INTEGER CHECK (quantity > 0)
);-- Domain approach
CREATE DOMAIN price_amount AS NUMERIC
CONSTRAINT positive_price CHECK (VALUE > 0 AND VALUE < 1000000);
-- Table using domain
CREATE TABLE products_v2 (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
price price_amount,
discount_price price_amount,
-- Business logic remains at table level
CONSTRAINT valid_discount
CHECK (discount_price IS NULL OR discount_price < price)
);
-- The power of domains: reusability
CREATE TABLE order_items (
order_id INTEGER,
product_id INTEGER,
unit_price price_amount,
quantity INTEGER CHECK (quantity > 0)
);Remember: A well-designed database is not just about storing data—it's about embedding business logic, ensuring data integrity, and providing a foundation for scalable applications.
🐝
Do you have any questions, or simply wish to contact me privately? Don't hesitate to shoot me a DM on Twitter.
Have a wonderful day.
Abhishek 🙏