KAHIBARO
Discord Login Register ☰
☰

Constraints

Table of Contents

Why Constraints Matter in PostgreSQL

In PostgreSQL, constraints are rules that the database enforces on your data. They help keep your data correct, consistent, and meaningful, even when many applications and users access the database at the same time.

You have already seen the ideas of primary keys and foreign keys in earlier chapters. In this chapter we focus on the wider family of constraints in PostgreSQL and how to use them effectively.

Key idea: A constraint is a rule enforced by the database. If a statement violates a constraint, PostgreSQL rejects that statement and returns an error.

Think of constraints as safety rails. They prevent invalid data from entering your tables, which avoids many bugs at the application level.

We will cover: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT, and some PostgreSQL specific details such as naming, deferrable constraints, and constraint validation.

Basic Types of Constraints

PostgreSQL supports several standard constraint types:

Constraint typePurposeTypical example
NOT NULLColumn cannot store NULL valuesEvery user must have an email
UNIQUEValues must be unique within a set of rowsNo two users can share the same email
PRIMARY KEYUniquely identifies each rowid column in users table
FOREIGN KEYLinks rows between tablesorders.user_id references users.id
CHECKArbitrary boolean condition on dataage >= 18 for an adult_users table
DEFAULTSets default value when none is providedcreated_at defaults to current timestamp
EXCLUDEAdvanced uniqueness using operatorsNo overlapping date ranges for a resource

You already have chapters for primary keys, foreign keys, and indexes, so we will not re-explain their general concepts. Here we focus on how they behave as constraints in PostgreSQL and what is unique about constraints as a system.

Constraints can be declared:

Both approaches are equivalent in effect, but table-level constraints are useful when a rule involves multiple columns.

NOT NULL and CHECK Constraints

NOT NULL

A NOT NULL constraint prevents a column from storing NULL values.

sql 
CREATE TABLE users (
    id          bigserial PRIMARY KEY,
    email       text NOT NULL,
    full_name   text NOT NULL,
    bio         text      -- bio is optional
);

In this example:

If you try to insert a row with a missing email:

sql 
INSERT INTO users (full_name) VALUES ('Alice');

PostgreSQL will respond with an error similar to:

text 
ERROR:  null value in column "email" violates not-null constraint
DETAIL: Failing row contains (1, null, Alice, null).

Adding NOT NULL to an existing table

If your table already exists:

sql 
ALTER TABLE users
ADD COLUMN phone text;
-- later, you decide phone is required
ALTER TABLE users
ALTER COLUMN phone SET NOT NULL;

If any existing row has phone = NULL, the SET NOT NULL command will fail. You must first update existing rows:

sql 
UPDATE users
SET phone = 'unknown'
WHERE phone IS NULL;
ALTER TABLE users
ALTER COLUMN phone SET NOT NULL;

CHECK Constraints

A CHECK constraint lets you define a custom rule that must be true for every row.

General form:

sql 
CREATE TABLE table_name (
    ...,
    CHECK ( boolean_expression )
);

The boolean_expression can reference columns of the row.

Column-level CHECK

sql 
CREATE TABLE products (
    id         bigserial PRIMARY KEY,
    name       text NOT NULL,
    price      numeric(10,2) NOT NULL CHECK (price >= 0),
    stock      integer NOT NULL CHECK (stock >= 0)
);

Here:

Invalid insert:

sql 
INSERT INTO products (name, price, stock)
VALUES ('Laptop', -100, 10);

Error:

text 
ERROR:  new row for relation "products"
        violates check constraint "products_price_check"
DETAIL:  Failing row contains (1, Laptop, -100.00, 10).

Table-level CHECK with multiple columns

Sometimes the rule includes more than one column, so you use a table-level CHECK.

sql 
CREATE TABLE discounts (
    id            bigserial PRIMARY KEY,
    percentage    numeric(5,2) NOT NULL,
    valid_from    date NOT NULL,
    valid_until   date NOT NULL,
    CHECK (percentage >= 0 AND percentage <= 100),
    CHECK (valid_from <= valid_until)
);

Rules here:

Another example, preventing conflicting columns:

sql 
CREATE TABLE contacts (
    id          bigserial PRIMARY KEY,
    email       text,
    phone       text,
    CHECK (email IS NOT NULL OR phone IS NOT NULL)
);

The CHECK ensures that each contact has at least one way to be reached.

Rule: A CHECK constraint must always evaluate to TRUE or UNKNOWN.
If it ever evaluates to FALSE, PostgreSQL rejects the row.

Note: UNKNOWN typically comes from NULL values. If the CHECK expression is NULL, PostgreSQL treats it as satisfied. For example, in CHECK (age >= 18), if age is NULL, the expression is UNKNOWN, not FALSE, so the constraint passes. If you need to forbid NULL, combine NOT NULL and CHECK.

sql 
age integer NOT NULL CHECK (age >= 18)

UNIQUE and PRIMARY KEY

You already know the concept of primary keys from earlier chapters. Here we focus on how UNIQUE and PRIMARY KEY work as constraints in PostgreSQL.

UNIQUE Constraints

A UNIQUE constraint enforces that all values in a column or a group of columns are unique.

sql 
CREATE TABLE users (
    id       bigserial PRIMARY KEY,
    email    text NOT NULL UNIQUE,
    username text NOT NULL UNIQUE
);

Here, both email and username must be unique across all users.

Trying to insert two users with the same email:

sql 
INSERT INTO users (email, username) VALUES
('alice@example.com', 'alice'),
('alice@example.com', 'alice2');

This will fail with:

text 
ERROR:  duplicate key value violates unique constraint "users_email_key"
DETAIL:  Key (email)=(alice@example.com) already exists.

Multi-column UNIQUE constraints

A UNIQUE constraint can cover multiple columns. In that case, the combination of values must be unique.

sql 
CREATE TABLE user_logins (
    user_id       bigint NOT NULL,
    device_id     text   NOT NULL,
    last_login_at timestamptz NOT NULL,
    PRIMARY KEY (user_id, device_id)
);

The primary key here is also a composite unique constraint. You can do the same with explicit UNIQUE:

sql 
CREATE TABLE product_translations (
    product_id bigint NOT NULL,
    language   text   NOT NULL,
    name       text   NOT NULL,
    description text,
    UNIQUE (product_id, language)
);

This allows the same product_id to appear multiple times with different language values, but each (product_id, language) pair appears only once.

UNIQUE and NULLs in PostgreSQL

PostgreSQL treats NULL values in a UNIQUE column as distinct. This means a UNIQUE constraint allows multiple NULL values.

sql 
CREATE TABLE employees (
    id         bigserial PRIMARY KEY,
    email      text UNIQUE
);
INSERT INTO employees (email) VALUES
(NULL),
(NULL);  -- allowed

If you want to ensure that at most one row has NULL, or no row has NULL, use NOT NULL combined with UNIQUE, or use a conditional unique index (covered in the indexes chapter).

PRIMARY KEY as a Constraint

A PRIMARY KEY is a special combination of NOT NULL and UNIQUE constraint that marks the main identifier of a table.

sql 
CREATE TABLE users (
    id        bigserial PRIMARY KEY,
    email     text NOT NULL UNIQUE
);

This is equivalent to:

sql 
CREATE TABLE users (
    id        bigserial NOT NULL,
    email     text NOT NULL UNIQUE,
    CONSTRAINT users_pkey PRIMARY KEY (id)
);

Some rules about primary key constraints in PostgreSQL:

You can define composite primary keys:

sql 
CREATE TABLE order_items (
    order_id  bigint NOT NULL,
    item_id   bigint NOT NULL,
    quantity  integer NOT NULL,
    PRIMARY KEY (order_id, item_id)
);

Again, this will be covered more deeply in the dedicated primary keys chapter, so here you just need to know it is a particular built-in constraint type.

FOREIGN KEYS and Referential Integrity

Foreign keys enforce relationships between tables and keep them consistent.

In PostgreSQL, a foreign key is declared with the REFERENCES clause and is implemented as a constraint.

sql 
CREATE TABLE users (
    id    bigserial PRIMARY KEY,
    email text NOT NULL UNIQUE
);
CREATE TABLE posts (
    id       bigserial PRIMARY KEY,
    user_id  bigint NOT NULL REFERENCES users(id),
    title    text   NOT NULL,
    body     text   NOT NULL
);

The user_id in posts is a foreign key that must reference an existing users.id.

Invalid insert:

sql 
INSERT INTO posts (user_id, title, body)
VALUES (9999, 'Hello', 'World');

If there is no user with id = 9999, PostgreSQL will reject the insert:

text 
ERROR:  insert or update on table "posts"
        violates foreign key constraint "posts_user_id_fkey"
DETAIL: Key (user_id)=(9999) is not present in table "users".

ON DELETE and ON UPDATE actions

Foreign key constraints can define what happens when the referenced row changes or is deleted.

Common options:

ActionEffect on child rows
ON DELETE RESTRICTReject delete if dependent rows exist. This is default in many cases.
ON DELETE CASCADEDelete child rows automatically when parent row is deleted.
ON DELETE SET NULLSet child foreign key column to NULL when parent row is deleted.
ON DELETE SET DEFAULTSet child foreign key to default value when parent is deleted.

Example:

sql 
CREATE TABLE posts (
    id       bigserial PRIMARY KEY,
    user_id  bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title    text   NOT NULL
);

If you delete a user:

sql 
DELETE FROM users WHERE id = 1;

All posts with user_id = 1 are automatically removed.

Be careful with ON DELETE CASCADE, it can delete large parts of your data if used without attention.

Again, the dedicated foreign keys chapter will explore this more, so here the goal is to show that foreign keys are implemented as constraints in PostgreSQL.

DEFAULT and GENERATED Columns

DEFAULT is not a constraint type in the strict SQL sense, but it is often mentioned together with constraints and is part of the table definition that influences data integrity.

DEFAULT values

A column DEFAULT provides a value when no value is specified in an insert.

sql 
CREATE TABLE accounts (
    id          bigserial PRIMARY KEY,
    created_at  timestamptz NOT NULL DEFAULT now(),
    status      text NOT NULL DEFAULT 'active'
);

Inserting without created_at and status:

sql 
INSERT INTO accounts (id) VALUES (1);

The row will have created_at = current timestamp and status = 'active'.

Combining DEFAULT and constraints:

sql 
CREATE TABLE tasks (
    id          bigserial PRIMARY KEY,
    title       text NOT NULL,
    priority    integer NOT NULL DEFAULT 3 CHECK (priority BETWEEN 1 AND 5),
    is_done     boolean NOT NULL DEFAULT false
);

Here:

GENERATED ALWAYS / BY DEFAULT (computed columns)

PostgreSQL supports generated columns that are calculated from other columns. They are more advanced, but related to data integrity.

Example:

sql 
CREATE TABLE line_items (
    quantity    integer NOT NULL CHECK (quantity > 0),
    unit_price  numeric(10,2) NOT NULL CHECK (unit_price >= 0),
    total       numeric(10,2) GENERATED ALWAYS AS (quantity * unit_price) STORED
);

Here, total is always computed by PostgreSQL. You cannot insert or update it directly. This avoids inconsistencies where someone might store the wrong total.

Naming, Enabling, and Disabling Constraints

Naming constraints

If you do not provide a name, PostgreSQL creates one for you, such as table_column_check or table_pkey. It is often helpful to provide meaningful names.

sql 
CREATE TABLE users (
    id          bigserial PRIMARY KEY,
    email       text NOT NULL,
    age         integer,
    CONSTRAINT users_email_unique UNIQUE (email),
    CONSTRAINT users_age_adult CHECK (age IS NULL OR age >= 18)
);

Named constraints make errors easier to understand and can help when you modify or drop constraints later.

Error example:

text 
ERROR:  new row for relation "users" violates check constraint "users_age_adult"

Much clearer than a generic auto-generated name.

Adding a named constraint with ALTER TABLE

sql 
ALTER TABLE users
ADD CONSTRAINT users_email_unique UNIQUE (email);
ALTER TABLE users
ADD CONSTRAINT users_age_adult CHECK (age IS NULL OR age >= 18);

Dropping constraints

You can remove a constraint with ALTER TABLE ... DROP CONSTRAINT.

sql 
ALTER TABLE users
DROP CONSTRAINT users_age_adult;

Be careful, once you drop a constraint, PostgreSQL no longer enforces that rule.

Deferrable and Deferred Constraints

By default, PostgreSQL checks constraints immediately when you execute an INSERT, UPDATE, or DELETE. Sometimes you need more flexibility, especially with complex transactions.

PostgreSQL supports deferrable constraints. A deferrable constraint can be checked:

This is an advanced feature, but it is unique and powerful in PostgreSQL.

DEFERRABLE INITIALLY DEFERRED

Example with a foreign key:

sql 
CREATE TABLE accounts (
    id      integer PRIMARY KEY
);
CREATE TABLE account_transfers (
    id          bigserial PRIMARY KEY,
    from_id     integer NOT NULL,
    to_id       integer NOT NULL,
    amount      numeric(10,2) NOT NULL CHECK (amount > 0),
    CONSTRAINT account_transfers_fk
        FOREIGN KEY (from_id, to_id)
        REFERENCES accounts (id, id) DEFERRABLE INITIALLY DEFERRED
);

This syntax is only illustrative. More realistic example:

Consider a constraint in which two rows in the same table reference each other, or you temporarily violate a constraint inside a transaction, but fix it before commit.

To declare a deferrable constraint:

sql 
CREATE TABLE inventory (
    id          bigserial PRIMARY KEY,
    product_id  bigint NOT NULL,
    quantity    integer NOT NULL,
    CHECK (quantity >= 0) DEFERRABLE INITIALLY DEFERRED
);

Meaning:

Inside a transaction:

sql 
BEGIN;
UPDATE inventory
SET quantity = quantity - 10
WHERE id = 1;
-- quantity may be temporarily negative at this point,
-- but we will fix it before commit
UPDATE inventory
SET quantity = quantity + 10
WHERE id = 1;
COMMIT;

If the final value at commit time violates the constraint, the transaction fails. If it is valid, the transaction succeeds.

SET CONSTRAINTS

You can control when deferrable constraints are checked inside a transaction.

sql 
BEGIN;
SET CONSTRAINTS ALL DEFERRED;  -- or specific constraint names
-- Do operations that temporarily violate constraints
-- Fix them later in the transaction
COMMIT;

Or switch to immediate mode:

sql 
SET CONSTRAINTS ALL IMMEDIATE;

This is used rarely in simple CRUD applications, but in complex bulk operations or certain migrations it becomes very useful.

VALIDATING Existing Data with Constraints

When adding a new constraint to a table that already has data, PostgreSQL must check that existing rows satisfy the new rule.

By default, ALTER TABLE ... ADD CONSTRAINT validates all existing rows and fails if any row violates the constraint.

Example:

sql 
ALTER TABLE users
ADD CONSTRAINT users_email_unique UNIQUE (email);

If two existing rows share the same email, the command will fail and no constraint is added.

Using NOT VALID for large tables

For very large tables, validating every row can be slow and lock the table. PostgreSQL offers NOT VALID to add a constraint without checking existing data immediately.

sql 
ALTER TABLE users
ADD CONSTRAINT users_email_unique UNIQUE (email) NOT VALID;

Behavior:

Later, you can validate the constraint in a separate step:

sql 
ALTER TABLE users
VALIDATE CONSTRAINT users_email_unique;

This pattern is useful during online migrations in production, where you want to minimize blocking.

Practical Examples of Constraint Usage

Example 1: A robust `users` table

sql 
CREATE TABLE users (
    id             bigserial PRIMARY KEY,
    email          text NOT NULL,
    password_hash  text NOT NULL,
    full_name      text NOT NULL,
    status         text NOT NULL DEFAULT 'active',
    created_at     timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT users_email_unique UNIQUE (email),
    CONSTRAINT users_status_valid CHECK (status IN ('active', 'inactive', 'banned'))
);

Effects:

Example 2: Orders and order items with integrity

sql 
CREATE TABLE customers (
    id        bigserial PRIMARY KEY,
    email     text NOT NULL UNIQUE,
    full_name text NOT NULL
);
CREATE TABLE orders (
    id           bigserial PRIMARY KEY,
    customer_id  bigint NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
    created_at   timestamptz NOT NULL DEFAULT now(),
    status       text NOT NULL DEFAULT 'pending',
    CONSTRAINT orders_status_valid CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled'))
);
CREATE TABLE order_items (
    id          bigserial PRIMARY KEY,
    order_id    bigint NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id  bigint NOT NULL,
    quantity    integer NOT NULL CHECK (quantity > 0),
    unit_price  numeric(10,2) NOT NULL CHECK (unit_price >= 0)
);

Here:

If your application has a bug that tries to insert an order item with quantity = 0, PostgreSQL will prevent it from entering the database.

Summary

In real backend applications, you should consistently use constraints to let the database guard the correctness of your data, instead of relying only on application code.

Views: 8

Comments

Please login to add a comment.

Don't have an account? Register now!

© 2025 Kahibaro
Visitors: 3103 / 24h
Impressum / Imprint
Datenschutz / Data Policy