OOmnistu

Blog / Databases

Databases

PostgreSQL constraints: CHECK, UNIQUE and foreign keys explained

Choose PostgreSQL constraints deliberately, understand NULL behavior and avoid accidental deletion cascades and redundant indexes.

OOmnistu
Reviewed 2026-11-16 · 5 min read

PostgreSQL constraints enforce rules about stored data. NOT NULL rejects missing values, CHECK tests a row-level condition, UNIQUE prevents duplicate keys, a primary key identifies rows, and a foreign key requires a matching referenced row. Application validation improves the user experience, but database constraints provide a separate boundary when data arrives through another code path.

This guide covers the behaviour documented in the PostgreSQL 18 constraints manual. Other database systems and versions can differ, especially around nulls. The examples below are original illustrations, not migrations to run unchanged on a production database.

Choose the rule before choosing the syntax

Requirement Constraint to consider Important question
Every product must have a name NOT NULL Is an empty string also invalid?
A price cannot be negative CHECK Should zero and missing prices be allowed?
A SKU cannot repeat UNIQUE Is uniqueness global or within a merchant?
Every row needs a stable identifier PRIMARY KEY Is the key one column or a combination?
An order item must reference a product FOREIGN KEY What should happen if the product is deleted?

A constraint only enforces the rule expressed in its definition. NOT NULL does not reject an empty string. A unique key on sku is different from a unique key on (merchant_id, sku).

CHECK and NOT NULL solve different problems

This illustrative table combines several rules:

CREATE TABLE catalogue_products (
    product_id bigint PRIMARY KEY,
    sku text NOT NULL UNIQUE,
    name text NOT NULL,
    price numeric NOT NULL,
    discounted_price numeric,
    CONSTRAINT nonnegative_price CHECK (price >= 0),
    CONSTRAINT valid_discount CHECK (
        discounted_price >= 0 AND discounted_price <= price
    )
);

A PostgreSQL check constraint passes when its expression is true or null. In this example, a missing discounted_price is allowed. A missing price is not, because that column also has NOT NULL.

The rule allows a zero price and a discount equal to the regular price. If that is not the business policy, change the comparisons. Writing down these edge cases before a migration is more useful than copying a generic "positive price" example.

A CHECK should not be used to enforce relationships with other rows or tables. PostgreSQL does not support that as a reliable consistency guarantee. Use a suitable unique, foreign-key or exclusion constraint where applicable, or get a database-specific design review for more complex rules.

Also be cautious about changing a function used by a check. PostgreSQL assumes check conditions keep the same meaning for the same row; changing a function does not automatically revalidate all existing data.

UNIQUE does not automatically mean required

A unique constraint enforces uniqueness across a column or a combination of columns. PostgreSQL automatically creates a unique B-tree index to support it.

By default, nulls are treated as distinct for this purpose, so a nullable unique column can contain more than one null. PostgreSQL 18 supports NULLS NOT DISTINCT when the rule should treat nulls as equal instead. Decide what a missing identifier means before selecting either behaviour.

For a required SKU, NOT NULL and UNIQUE express two separate requirements. For a merchant catalogue, a combined unique constraint may be the right scope. Neither choice validates the SKU's format unless you add a separate rule.

A primary key is unique and not null

A primary key can use one column or several. PostgreSQL allows at most one primary key per table, and declaring one creates a unique B-tree index and makes its columns not null.

A primary key does not have to be an incrementing number. The right choice depends on identity, stability and how other tables refer to the row. Do not add another identical index just because the key is used in lookups; its supporting index already exists.

Foreign keys need an explicit deletion policy

A foreign key maintains a relationship between rows. For example:

CREATE TABLE catalogue_order_items (
    order_item_id bigint PRIMARY KEY,
    product_id bigint NOT NULL
        REFERENCES catalogue_products(product_id) ON DELETE RESTRICT,
    quantity integer NOT NULL CHECK (quantity > 0)
);

Here, an item must point to an existing product. The NOT NULL matters: without it, a null reference would normally escape the matching requirement.

Deletion choices are not interchangeable:

Use CASCADE only when the dependent row really should disappear with its parent. An order's line items and a product's historical references may need different policies. Treat that as a product and data-retention decision, not a convenient way to silence an error.

Does a foreign key create an index?

The referenced columns must have suitable uniqueness support, such as a primary key or unique constraint. PostgreSQL does not automatically add an index on the referencing columns.

Indexing the child-side columns can help when parent updates or deletes need to find related rows, but it is not an instruction to create every possible index. Evaluate the workload. Our database indexes guide explains that tradeoff.

Before a schema change

Write down expected behaviour for valid rows, missing values, duplicates and parent deletion. Test those cases on representative non-production data. Review the existing data, migration impact and rollback plan separately; this article does not provide a production migration procedure.

Constraints protect the rules you specify. They do not replace application authorization, input handling or a careful definition of the business rule.

By Omnistu. Reviewed against PostgreSQL 18 documentation on 16 November 2026. SQL examples have not been executed in a database during this review.

Sources

Previous
PostgreSQL backups: logical dumps vs point-in-time recovery