Exclusion constraints prevent overlapping data — useful for booking systems. Deferrable constraints delay validation until the end of a transaction. Domain types add CHECK constraints to an existing type for validation.
Advanced Constraints
-- Exclusion constraint (no overlapping bookings)
CREATE TABLE bookings (
room_id INTEGER,
period TSRANGE,
EXCLUDE USING GIST (room_id WITH =, period WITH &&)
);
-- Deferred constraint
ALTER TABLE orders
ADD CONSTRAINT fk_user
FOREIGN KEY (user_id) REFERENCES users(id)
DEFERRABLE INITIALLY DEFERRED;