👋
Welcome to my blog!

PostgreSQL Deep Dive Part 10 — Data Integrity and Constraints — Building Robust Database Design

Master PostgreSQL's data integrity mechanisms including NULL handling, unique constraints, exclusion constraints, and foreign keys. Learn advanced constraint implementations and optimization strategies.

PostgreSQL Deep Dive Part 10 — Data Integrity and Constraints — Building Robust Database Design
Database
Postgres
Development

Published At

1/2/2025

Reading Time

~ 8 min read

📚 Raw Postgres Study Notes

Access complete, unfiltered study notes maintained on GitHub for easily maintaing Second Brain Dir

Data integrity is fundamental to database design. PostgreSQL provides robust mechanisms to ensure data consistency through various types of constraints. This comprehensive guide explores these mechanisms in detail.

Understanding NULL Values

NULL values in PostgreSQL represent unknown data. Their handling requires special consideration in constraint design and query logic.

NULL Fundamentals

sql
-- Basic NOT NULL constraint
CREATE TABLE products (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL,
    price numeric NOT NULL CHECK(price > 0)
);
sql
-- Basic NOT NULL constraint
CREATE TABLE products (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL,
    price numeric NOT NULL CHECK(price > 0)
);

Key characteristics of NULL:

  • NULL = NULL evaluates to NULL, not TRUE
  • NULL is not equal to anything, including itself
  • NULL requires special handling in constraints and queries
  • NULL affects index behaviour and query optimization

NULL in Logical Operations

sql
-- Three-valued logic demonstration
SELECT
    TRUE AND NULL,  -- Returns: NULL
    FALSE AND NULL, -- Returns: FALSE
    TRUE OR NULL,   -- Returns: TRUE
    FALSE OR NULL,  -- Returns: NULL
    NOT NULL;       -- Returns: NULL
sql
-- Three-valued logic demonstration
SELECT
    TRUE AND NULL,  -- Returns: NULL
    FALSE AND NULL, -- Returns: FALSE
    TRUE OR NULL,   -- Returns: TRUE
    FALSE OR NULL,  -- Returns: NULL
    NOT NULL;       -- Returns: NULL

Unique Constraints

Unique constraints ensure data uniqueness across specified columns or column combinations.

Basic Unique Constraints

sql
CREATE TABLE products (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_number text UNIQUE,
    name text NOT NULL,
    price numeric NOT NULL CHECK(price > 0)
);
sql
CREATE TABLE products (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_number text UNIQUE,
    name text NOT NULL,
    price numeric NOT NULL CHECK(price > 0)
);

NULL Behaviour with Unique Constraints

sql
-- Default behavior: multiple NULLs allowed
CREATE TABLE products (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_number text UNIQUE, -- Multiple NULLs allowed
    name text NOT NULL,
    price numeric NOT NULL CHECK(price > 0)
);
 
-- Restricting NULLs
CREATE TABLE products (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_number text UNIQUE NOT NULL, -- No NULLs allowed
    name text NOT NULL,
    price numeric NOT NULL CHECK(price > 0)
);
 
-- NULLS NOT DISTINCT option (PostgreSQL 15+)
CREATE TABLE products (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_number text UNIQUE NULLS NOT DISTINCT, -- Only one NULL allowed
    name text NOT NULL,
    price numeric NOT NULL CHECK(price > 0)
);
sql
-- Default behavior: multiple NULLs allowed
CREATE TABLE products (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_number text UNIQUE, -- Multiple NULLs allowed
    name text NOT NULL,
    price numeric NOT NULL CHECK(price > 0)
);
 
-- Restricting NULLs
CREATE TABLE products (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_number text UNIQUE NOT NULL, -- No NULLs allowed
    name text NOT NULL,
    price numeric NOT NULL CHECK(price > 0)
);
 
-- NULLS NOT DISTINCT option (PostgreSQL 15+)
CREATE TABLE products (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_number text UNIQUE NULLS NOT DISTINCT, -- Only one NULL allowed
    name text NOT NULL,
    price numeric NOT NULL CHECK(price > 0)
);

Composite Unique Constraints

sql
CREATE TABLE products (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    brand text NOT NULL,
    product_number text NOT NULL,
    name text NOT NULL,
    price numeric NOT NULL CHECK(price > 0),
 
-- Composite unique constraint
    UNIQUE(brand, product_number)
);
sql
CREATE TABLE products (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    brand text NOT NULL,
    product_number text NOT NULL,
    name text NOT NULL,
    price numeric NOT NULL CHECK(price > 0),
 
-- Composite unique constraint
    UNIQUE(brand, product_number)
);

Exclusion Constraints

Exclusion constraints offer more flexibility than unique constraints by allowing custom operators and conditions.

Basic Exclusion Constraint

sql
CREATE TABLE reservations (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    room_id integer,
    reservation_period tsrange,
    EXCLUDE USING gist (reservation_period WITH &&)
);
sql
CREATE TABLE reservations (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    room_id integer,
    reservation_period tsrange,
    EXCLUDE USING gist (reservation_period WITH &&)
);

Advanced Exclusion Constraints

Advanced exclusion constraints provide powerful functionality for enforcing complex business rules. They combine multiple conditions and operators to create sophisticated constraints that go beyond simple uniqueness checks.

The btree_gist extension enables the use of equality operators (=) alongside range operators in exclusion constraints. This is particularly useful when combining regular columns with range types:

sql
-- Enable btree_gist extension for equality operator support
CREATE EXTENSION IF NOT EXISTS btree_gist;
 
-- Multiple-column exclusion constraint
CREATE TABLE reservations (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    room_id integer,
    reservation_period tsrange,
    EXCLUDE USING gist (
        room_id WITH =,
        reservation_period WITH &&
    )
);
sql
-- Enable btree_gist extension for equality operator support
CREATE EXTENSION IF NOT EXISTS btree_gist;
 
-- Multiple-column exclusion constraint
CREATE TABLE reservations (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    room_id integer,
    reservation_period tsrange,
    EXCLUDE USING gist (
        room_id WITH =,
        reservation_period WITH &&
    )
);

In this example, the exclusion constraint ensures that no two reservations for the same room can have overlapping time periods. The && operator checks for range overlap, while = checks for equality of room_id. This effectively prevents double-booking of rooms.

We can further refine this constraint by adding conditional logic:

sql
-- Adding WHERE clause to exclusion constraint
CREATE TABLE reservations (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    room_id integer,
    reservation_period tsrange,
    is_cancelled boolean DEFAULT false,
    EXCLUDE USING gist (
        room_id WITH =,
        reservation_period WITH &&
    ) WHERE (NOT is_cancelled)
);
sql
-- Adding WHERE clause to exclusion constraint
CREATE TABLE reservations (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    room_id integer,
    reservation_period tsrange,
    is_cancelled boolean DEFAULT false,
    EXCLUDE USING gist (
        room_id WITH =,
        reservation_period WITH &&
    ) WHERE (NOT is_cancelled)
);

The WHERE clause makes the constraint more flexible by only applying it to active (non-cancelled) reservations. This means:

  • Multiple cancelled reservations can overlap for the same room
  • A cancelled reservation can overlap with an active reservation
  • Active reservations still cannot overlap for the same room

This pattern is particularly useful in real-world applications where you need to maintain historical records while enforcing current business rules.

Foreign Key Constraints

Foreign keys ensure referential integrity between tables.

Basic Foreign Key Implementation

sql
CREATE TABLE states (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text
);
 
CREATE TABLE cities (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    state_id bigint REFERENCES states(id),
    name text
);
 
-- Sample data insertion
INSERT INTO states (name) VALUES ('Texas');
 
-- Valid insertion
INSERT INTO cities (state_id, name)
VALUES (1, 'Dallas'); -- OK
 
-- Invalid insertion (non-existent state_id)
INSERT INTO cities (state_id, name)
VALUES (2, 'Dallas');
-- Error, as state_id 2 does not exist current on parent table
sql
CREATE TABLE states (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text
);
 
CREATE TABLE cities (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    state_id bigint REFERENCES states(id),
    name text
);
 
-- Sample data insertion
INSERT INTO states (name) VALUES ('Texas');
 
-- Valid insertion
INSERT INTO cities (state_id, name)
VALUES (1, 'Dallas'); -- OK
 
-- Invalid insertion (non-existent state_id)
INSERT INTO cities (state_id, name)
VALUES (2, 'Dallas');
-- Error, as state_id 2 does not exist current on parent table

Table-Level Foreign Key Constraints

sql
CREATE TABLE cities (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    state_id bigint,
    name text,
    FOREIGN KEY (state_id) REFERENCES states(id)
);
 
-- Composite foreign key
CREATE TABLE cities (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    state_id_1 bigint,
    state_id_2 bigint,
    name text,
    FOREIGN KEY (state_id_1, state_id_2) REFERENCES states(id_1, id_2)
);
sql
CREATE TABLE cities (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    state_id bigint,
    name text,
    FOREIGN KEY (state_id) REFERENCES states(id)
);
 
-- Composite foreign key
CREATE TABLE cities (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    state_id_1 bigint,
    state_id_2 bigint,
    name text,
    FOREIGN KEY (state_id_1, state_id_2) REFERENCES states(id_1, id_2)
);

Cascading Actions

sql
CREATE TABLE cities (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    state_id bigint,
    name text,
    FOREIGN KEY (state_id)
        REFERENCES states(id)
        ON DELETE CASCADE-- Automatically delete dependent rows
);
 
-- Alternative actions
CREATE TABLE cities (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    state_id bigint,
    name text,
    FOREIGN KEY (state_id)
        REFERENCES states(id)
        ON DELETE SET NULL-- Set to NULL when parent is deleted
);
 
CREATE TABLE cities (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    state_id bigint,
    name text,
    FOREIGN KEY (state_id)
        REFERENCES states(id)
        ON DELETE NO ACTION-- Default: prevent deletion if dependencies exist
);
sql
CREATE TABLE cities (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    state_id bigint,
    name text,
    FOREIGN KEY (state_id)
        REFERENCES states(id)
        ON DELETE CASCADE-- Automatically delete dependent rows
);
 
-- Alternative actions
CREATE TABLE cities (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    state_id bigint,
    name text,
    FOREIGN KEY (state_id)
        REFERENCES states(id)
        ON DELETE SET NULL-- Set to NULL when parent is deleted
);
 
CREATE TABLE cities (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    state_id bigint,
    name text,
    FOREIGN KEY (state_id)
        REFERENCES states(id)
        ON DELETE NO ACTION-- Default: prevent deletion if dependencies exist
);

Best Practices

  1. Constraint Naming:
  • Use consistent naming conventions
  • Include table and column names
  • Indicate the constraint type
  1. NULL Handling:
  • Be explicit about NULL acceptance
  • Use NOT NULL when appropriate
  • Consider NULLS NOT DISTINCT for unique constraints
  1. Foreign Key Design:
  • Consider cascade actions carefully
  • Index foreign key columns
  • Use appropriate action for business logic
  1. Constraint Implementation:
  • Start with table-level constraints
  • Use column constraints for simple cases

Conclusion

PostgreSQL's constraint system provides robust tools for ensuring data integrity. Key takeaways:

  • Use appropriate constraint types for your use case
  • Consider performance implications
  • Implement proper monitoring
  • Follow naming conventions
  • Plan for maintenance and scaling

Understanding these mechanisms enables building reliable and maintainable database systems while ensuring data consistency and integrity.

🥞

Share this article

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 🙏

Join My Exclusive Newsletter Community

Step into a world where creativity intersects with technology. By subscribing, you'll get a front-row seat to my latest musings, full-stack development resources, and exclusive previews of future posts. Each email is a crafted experience that includes:

  • In-depth looks at my covert projects and musings to ignite your imagination.
  • Handpicked frontend development resources and current explorations, aimed at expanding your developer toolkit.
  • A monthly infusion of inspiration with my personal selection of quotes, books, and music.

Embrace the confluence of words and wonder, curated thoughtfully and sent straight to your inbox.

No fluff. Just the highest caliber of ideas.