How to VALIDATE CONSTRAINT in PostgreSQL

How do I validate a constraint in PostgreSQL?

VALIDATE CONSTRAINT verifies that all existing rows satisfy a NOT VALID constraint without recreating it.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What does ALTER TABLE … VALIDATE CONSTRAINT do?

VALIDATE CONSTRAINT checks every existing row against a previously created constraint that was declared NOT VALID. If all rows pass, PostgreSQL flips the constraint status to VALID, making it fully enforceable for future inserts or updates.

When should I run VALIDATE CONSTRAINT?

Run it after you add CHECK, FOREIGN KEY, or UNIQUE constraints with NOT VALID during peak-traffic periods. This lets you defer the heavy scan to a maintenance window, avoiding long blocking locks.

How do I validate multiple constraints at once?

You can list several constraints in one ALTER TABLE statement separated by commas. PostgreSQL scans the table only once, saving I/O and lock time.

Does validation lock my table?

PostgreSQL takes a SHARE UPDATE EXCLUSIVE lock. This blocks concurrent ALTER TABLE commands but allows SELECT, INSERT, UPDATE, and DELETE, so your app keeps running.

Best practices for data validation

Create constraints NOT VALID during high load, schedule VALIDATE CONSTRAINT during low-traffic windows, and batch multiple validations together. Always monitor lock wait events.

Why How to VALIDATE CONSTRAINT in PostgreSQL is important

How to VALIDATE CONSTRAINT in PostgreSQL Example Usage


-- 1. Add a foreign key without validating it immediately
ALTER TABLE Orders
    ADD CONSTRAINT orders_customer_id_fkey
    FOREIGN KEY (customer_id)
    REFERENCES Customers(id)
    NOT VALID;

-- 2. Later, during maintenance, validate it
ALTER TABLE Orders VALIDATE CONSTRAINT orders_customer_id_fkey;

How to VALIDATE CONSTRAINT in PostgreSQL Syntax


ALTER TABLE table_name
    VALIDATE CONSTRAINT constraint_name [, ...];

-- Ecommerce examples
-- Validate a NEW unique customer email constraint
ALTER TABLE Customers VALIDATE CONSTRAINT customers_email_key;

-- Validate both a foreign key and a check in one pass
ALTER TABLE Orders
    VALIDATE CONSTRAINT orders_customer_id_fkey,
    VALIDATE CONSTRAINT orders_total_amount_check;

Common Mistakes

Creating a heavy constraint without NOT VALID causes an immediate full-table scan and an ACCESS EXCLUSIVE lock. Fix: always append NOT VALID when adding constraints in production tables.
Running VALIDATE CONSTRAINT during peak usage can still stress I/O and extend lock time. Fix: schedule validation in a low-traffic window or batch several constraints together.