PostgreSQL enforces data integrity through constraints — rules on table data. Beyond the standard ones (, , , , , ), Postgres adds powerful options like constraints and flexible expressions.
PostgreSQL enforces data integrity through constraints — rules on table data. Beyond the standard ones (, , , , , ), Postgres adds powerful options like constraints and flexible expressions.
PRIMARY KEYFOREIGN KEYUNIQUENOT NULLCHECKDEFAULTCHECKCREATE TABLE bookings (
id SERIAL PRIMARY KEY, -- unique identifier
room_id INT NOT NULL REFERENCES rooms(id), -- foreign key (referential integrity)
email VARCHAR(255) UNIQUE NOT NULL, -- unique + required
guests INT CHECK (guests > 0 AND guests <= 10), -- value constraint
status TEXT DEFAULT 'pending' -- default value
);
PRIMARY KEY → unique row identifier
FOREIGN KEY → references another table (referential integrity, with ON DELETE options)
UNIQUE → no duplicate values
NOT NULL → must have a value
CHECK → value must satisfy a condition
DEFAULT → default value when not provided
CHECK (price > 0)
CHECK (end_date > start_date) -- logical consistency between columns
CHECK (status IN ('active', 'inactive', 'pending'))
CHECK (email ~ '^[^@]+@[^@]+\.[^@]+$') -- regex validation
Postgres CHECK constraints can enforce rich business rules, including cross-column conditions and regex patterns.
-- prevent OVERLAPPING bookings for the same room (impossible with UNIQUE alone!)
CREATE TABLE bookings (
room_id INT,
during TSRANGE, -- a time range
EXCLUDE USING GIST (room_id WITH =, during WITH &&) -- no two rows where room_id is
); -- equal AND time ranges OVERLAP
EXCLUDE constraints (a Postgres-specific feature) prevent rows that conflict by a custom operator — famously, preventing overlapping time ranges (double-bookings), which a UNIQUE constraint can't do.
-- check constraints at COMMIT instead of immediately (useful for circular references)
FOREIGN KEY (...) REFERENCES ... DEFERRABLE INITIALLY DEFERRED
Constraints are essential for data integrity — they enforce data rules at the database level (the last line of defense, always enforced regardless of application code), so understanding PostgreSQL's constraint support is important for designing robust databases.
Knowing the standard constraints (PRIMARY KEY, FOREIGN KEY with referential integrity, UNIQUE, NOT NULL, CHECK, DEFAULT) and what each enforces is fundamental to schema design, providing database-level guarantees (uniqueness, valid references, required values, valid data) that protect against bugs, race conditions, and inconsistent data in ways application code alone cannot.
PostgreSQL's CHECK constraints are particularly capable, enforcing rich business rules including cross-column conditions (logical consistency like end_date > start_date) and even regex patterns — letting you encode domain rules directly in the schema.
Especially valuable is the Postgres-specific EXCLUDE constraint, which prevents conflicting rows by custom operators — most notably preventing overlapping time ranges (double-bookings for a room/resource), a powerful capability that a standard UNIQUE constraint cannot provide and that elegantly solves a common real-world problem (scheduling, reservations) at the database level.
Understanding the constraints — the standard ones for integrity, Postgres's flexible CHECK expressions, and the powerful EXCLUDE for conflict prevention — is important for designing databases that enforce correctness automatically.
Since data integrity is critical and constraints provide guarantees application code can't, and since Postgres offers powerful options (rich CHECK, EXCLUDE for overlaps) beyond the basics, understanding PostgreSQL's constraint support is valuable, practically-relevant knowledge for building robust databases, with the EXCLUDE constraint in particular being a distinctive, useful Postgres capability worth knowing for scheduling/reservation systems.