3.2 Integrity Constraints & Data Validation

3.2 Integrity Constraints & Data Validation

[!NOTE] Exam Blueprint Focus: Relational integrity constraints form the frontline defense of data consistency in PostgreSQL. The EDB PostgreSQL Associate exam requires mastery of primary and foreign keys, referential action semantics (RESTRICT vs NO ACTION), CHECK constraints, UNIQUE rules (including NULL semantics), and EXCLUSION constraints using GiST. Furthermore, you must understand zero-downtime DDL operational patterns using NOT VALID and VALIDATE CONSTRAINT.

Integrity constraints define declarative rules enforced by the PostgreSQL database engine to ensure that data inserted, updated, or deleted conforms to strict business rules and relational consistency models. Enforcing constraints inside the database prevents data corruption regardless of which application, microservice, or ad-hoc SQL session modifies the underlying tables.

---

Primary Key Constraints

A PRIMARY KEY constraint designates a column or combination of columns as the unique identifier for tuples within a relation:

• Uniqueness and Nullability: Declaring a primary key automatically imposes a NOT NULL constraint on every constituent column and builds a unique B-Tree index across those columns.
• Singularity: A table can possess at most one primary key constraint.
• Composite Keys: When multiple columns combine to form the primary key, it must be declared as a table-level constraint.

-- Column-level declaration
CREATE TABLE departments (
    dept_id   integer PRIMARY KEY,
    dept_name text NOT NULL
);

-- Table-level composite declaration
CREATE TABLE project_assignments (
    project_id  integer NOT NULL,
    employee_id integer NOT NULL,
    assigned_at timestamptz DEFAULT clock_timestamp(),
    CONSTRAINT pk_project_assignments PRIMARY KEY (project_id, employee_id)
);

---

Foreign Key Constraints & Referential Actions

A FOREIGN KEY (referential integrity) constraint establishes a relationship between a referencing (child) column and a referenced (parent) column. The referenced column in the parent table must be backed by an existing PRIMARY KEY or UNIQUE constraint.

Referential Actions on DELETE and UPDATE

When a row in the parent table is deleted or its referenced key is updated, PostgreSQL executes the referential action specified on the foreign key definition:

1. CASCADE: Automatically deletes or updates the referencing rows in the child table to match the parent table modification.
2. SET NULL: Sets the referencing column values in the child table to NULL (the child column must not have a NOT NULL constraint).
3. SET DEFAULT: Sets the referencing column values in the child table to their defined column default values.
4. RESTRICT: Prevents the deletion or update of the referenced parent row immediately. The check is performed as soon as the statement executes and cannot be postponed.
5. NO ACTION (Default): Prevents the deletion or update of the parent row if any child rows reference it. If the constraint is declared as DEFERRABLE, the check can be deferred until the end of the transaction (COMMIT), allowing other statements within the same transaction to rectify the reference before validation.

CREATE TABLE orders (
    order_id    bigint PRIMARY KEY,
    dept_id     integer REFERENCES departments(dept_id) ON DELETE RESTRICT,
    customer_id bigint NOT NULL
);

CREATE TABLE order_items (
    item_id     bigint PRIMARY KEY,
    order_id    bigint NOT NULL REFERENCES orders(order_id) ON DELETE CASCADE,
    product_sku text NOT NULL,
    price       numeric(10, 2) NOT NULL
);

[!IMPORTANT] Administrative Tip: Index Your Foreign Keys! PostgreSQL does not automatically create indexes on foreign key columns in child tables. While the parent table has a primary key index, the child table's foreign key column (order_items.order_id) remains unindexed unless explicitly created.

If an unindexed child foreign key exists, running a DELETE or primary key UPDATE on the parent table forces PostgreSQL to perform an expensive Sequential Scan across the entire child table to verify referential integrity. On high-volume tables, this causes severe lock contention and catastrophic query slowdowns.

---

Unique Constraints & The NULL Dilemma

A UNIQUE constraint guarantees that all non-null values stored in a column or group of columns are distinct across the table. Like primary keys, declaring a UNIQUE constraint automatically creates a unique B-Tree index.

Standard SQL NULL Behavior

Under standard SQL and PostgreSQL default behavior, a unique constraint allows multiple NULL values:

• In relational logic, NULL represents an unknown value. Because NULL = NULL evaluates to UNKNOWN (falsy) rather than TRUE, two NULL entries are not considered duplicates of each other.
• Therefore, multiple rows containing NULL in a UNIQUE column will not trigger a constraint violation.

PostgreSQL 15+ Feature: UNIQUE NULLS NOT DISTINCT

Starting with PostgreSQL 15, the SQL standard clause NULLS NOT DISTINCT can be added to unique constraints and unique indexes. Under this clause, NULL values are treated as equal values for uniqueness evaluation, permitting at most one NULL entry in the column:

-- Default behavior: Multiple NULLs permitted
CREATE TABLE user_profiles (
    user_id     bigint PRIMARY KEY,
    national_id text UNIQUE -- Allows unlimited NULL entries
);

-- PostgreSQL 15+: Exactly ONE NULL permitted across the entire table
CREATE TABLE employee_identities (
    emp_id       bigint PRIMARY KEY,
    tax_id       text UNIQUE NULLS NOT DISTINCT
);

---

NOT NULL & CHECK Constraints

NOT NULL Constraints

A NOT NULL constraint enforces that a column must always contain an explicit, non-null value. In PostgreSQL, NOT NULL is technically stored as a column attribute bit flag inside pg_attribute rather than an independent entry in the pg_constraint catalog, optimizing row validation during writes.

CHECK Constraints

A CHECK constraint evaluates an arbitrary boolean expression on row data whenever a tuple is inserted or updated:

• Evaluation Rule: A CHECK constraint is satisfied if the expression evaluates to TRUE or NULL. It fails only if the expression evaluates to FALSE. If a column value is NULL, the expression often evaluates to NULL, which passes the check unless a separate NOT NULL constraint is present.
• Expression Limitations: The check expression can reference any column within the same row, but cannot reference columns in other tables, execute subqueries, or invoke non-immutable (volatile) functions such as random(), clock_timestamp(), or now().

CREATE TABLE employee_salaries (
    emp_id       bigint PRIMARY KEY,
    hourly_rate  numeric(8, 2) NOT NULL CHECK (hourly_rate > 0),
    start_date   date NOT NULL,
    end_date     date,
    CONSTRAINT chk_date_sequence CHECK (end_date IS NULL OR end_date >= start_date)
);

---

EXCLUSION Constraints Using GiST

A common challenge in relational modeling is preventing overlapping ranges (such as overlapping conference room reservations, hotel stays, or employee shifts). Standard UNIQUE constraints only test equality (=), which cannot detect whether two time intervals overlap (&&).

PostgreSQL solves this with EXCLUSION Constraints backed by Generalized Search Tree (GiST) indexes:

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE room_reservations (
    reservation_id bigint PRIMARY KEY,
    room_number    integer NOT NULL,
    booking_slot   tsrange NOT NULL,
    -- Prevent two reservations for the SAME room from having OVERLAPPING time slots
    CONSTRAINT exclude_room_double_booking 
        EXCLUDE USING gist (
            room_number WITH =,
            booking_slot WITH &&
        )
);

If an application attempts to insert a reservation for room 101 that overlaps with an existing reservation for room 101, PostgreSQL raises a constraint violation: ERROR: conflicting key value violates exclusion constraint "exclude_room_double_booking".

---

Zero-Downtime Operations: Adding & Validating Constraints

In 24/7 enterprise production databases with tables containing millions or billions of rows, adding a constraint naively causes operational disasters:

-- DANGEROUS ON PRODUCTION TABLES!
ALTER TABLE orders ADD CONSTRAINT chk_orders_amount CHECK (amount > 0);

This naive statement acquires an ACCESS EXCLUSIVE lock on orders while it performs a sequential scan to validate every existing row. While this lock is held, all concurrent SELECT, INSERT, UPDATE, and DELETE operations on orders are completely blocked, causing application timeouts and outages.

The Safe Two-Step Workflow: NOT VALID and VALIDATE CONSTRAINT

To eliminate table blocking, PostgreSQL provides a zero-downtime, two-step pattern:

-- Step 1: Add constraint with NOT VALID (instantaneous)
ALTER TABLE orders 
    ADD CONSTRAINT chk_orders_amount 
    CHECK (amount > 0) NOT VALID;

-- Step 2: Validate existing rows concurrently (non-blocking)
ALTER TABLE orders 
    VALIDATE CONSTRAINT chk_orders_amount;

1. Step 1 (NOT VALID): Acquires a brief SHARE ROW EXCLUSIVE lock to register the constraint in system catalogs. It does not scan existing rows. From this moment onward, all new INSERT and UPDATE statements are strictly enforced against the constraint. The lock is released in milliseconds.
2. Step 2 (VALIDATE CONSTRAINT): Acquires a lightweight SHARE UPDATE EXCLUSIVE lock. This lock permits concurrent reads and writes (SELECTs, INSERTs, UPDATEs, DELETEs) while a background sequential scan validates all existing historical rows. Once the scan completes without violations, the constraint is marked fully valid.

-- Dropping constraints safely
ALTER TABLE orders DROP CONSTRAINT chk_orders_amount RESTRICT;