Constraints

Constraints are rules applied to columns (or tables) to ensure data validity and integrity. If an INSERT or UPDATE statement violates a constraint, the database will throw an error and abort the operation.

We have already discussed Primary Keys, Foreign Keys, and Unique Keys. Let's look at a few other essential constraints.

NOT NULL

By default, a column can hold NULL values. NULL means "unknown" or "missing"—it does not mean zero or an empty string. The NOT NULL constraint enforces that a column must always have a value.

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL, -- Name is strictly required
    description TEXT            -- Description is optional (can be NULL)
);

CHECK

The CHECK constraint allows you to specify a boolean expression that must evaluate to TRUE or UNKNOWN (if NULL) for the data to be accepted.

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    salary NUMERIC(10, 2),
    age INT,
    
    -- Ensure salary is positive
    CONSTRAINT chk_positive_salary CHECK (salary > 0),
    
    -- Ensure age is valid
    CONSTRAINT chk_valid_age CHECK (age >= 18 AND age <= 100)
);

TIP

Always explicitly name your constraints using the CONSTRAINT constraint_name syntax. If you don't, PostgreSQL will autogenerate a name (like employees_salary_check). If you ever need to ALTER TABLE to drop the constraint, knowing the exact name makes it much easier.

DEFAULT

The DEFAULT constraint sets a default value for a column if no value is specified during an INSERT.

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    status VARCHAR(20) DEFAULT 'PENDING',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

If you insert a row without specifying status or created_at, PostgreSQL will automatically insert 'PENDING' and the exact timestamp the transaction occurred.

Exclusion Constraints (PostgreSQL Specific)

An Exclusion Constraint ensures that if any two rows are compared on the specified columns or expressions using the specified operators, at least one of these operator comparisons will return false or null.

They are incredibly powerful for scheduling applications (e.g., preventing double-booking a meeting room).

CREATE EXTENSION btree_gist; -- Required extension

CREATE TABLE reservations (
    room_id INT,
    reserved_period TSTZRANGE,
    -- Prevent overlapping reservations for the same room
    EXCLUDE USING GIST (
        room_id WITH =,
        reserved_period WITH &&
    )
);

(Here && is the overlapping operator for ranges).