Check Constraints for Complex Business Rules

1. What a check constraint can do beyond simple ranges

Most developers only know a check constraint as a simple range check, for example that a price must not be negative. This view considerably underestimates what a CHECK constraint can actually do. The SQL standard allows any boolean expression in the CHECK clause that refers to columns of the same row, including arbitrarily nested logical operators, CASE expressions, and, in PostgreSQL, even calls to user-defined functions.

This power allows complex business rules to be anchored directly in the schema instead of being checked exclusively in application code. A state machine for an order status field, a rule that puts several date fields into a logical order, or a condition that requires different mandatory fields depending on a discriminator field, can all be formulated as a single, declarative check constraint. The decisive advantage over a trigger: a CHECK constraint is declarative, visibly documented in the schema itself, and can be used by the query planner for optimizations.

The following sections show concrete patterns for complex business rules that go far beyond the usual simple range check, together with the limits at which a check constraint must hand off to a trigger or application code.

2. Multi-column check constraints for field relationships

A multi-column check constraint combines several fields of the same row into a shared condition. A classic example is a time range where the end date must not be before the start date, or a pricing rule where a discounted price must never be higher than the regular price. Such rules concern the relationship between columns, not the value of a single column on its own, and are therefore impossible to express with a single CHECK per column.

Consistent handling of NULL values is important for multi-column constraints, since a condition that hits a NULL value evaluates to neither true nor false but unknown under SQL's three-valued logic, meaning the constraint effectively does not fire. An end date that is optional must therefore be explicitly guarded with an OR condition for the NULL case, otherwise every row with NULL in the end date is unintentionally accepted, regardless of the start date's value.

CREATE TABLE promotion (
    promotion_id   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name           VARCHAR(255) NOT NULL,
    regular_price  DECIMAL(10,2) NOT NULL,
    sale_price     DECIMAL(10,2) NOT NULL,
    start_date     DATE NOT NULL,
    end_date       DATE,
    CONSTRAINT chk_sale_price_lower
        CHECK (sale_price <= regular_price),
    -- explicit NULL handling: an open end date is always valid
    CONSTRAINT chk_promotion_date_order
        CHECK (end_date IS NULL OR end_date >= start_date),
    -- three-column relationship in a single declarative rule
    CONSTRAINT chk_discount_percentage_realistic
        CHECK (
            (regular_price - sale_price) / NULLIF(regular_price, 0) <= 0.90
        )
);

3. Modeling state machines with check constraints

A status field such as an order status runs through a fixed set of meaningful values in practice, and certain combinations with other fields are often only permitted in specific statuses. A check constraint that goes beyond a simple IN list can capture this relationship directly: an order with status "cancelled" must carry a cancelled_at date, an order with status "shipped" must have a tracking number, an order with status "draft" must have neither.

This kind of state machine can be formulated as a CASE condition inside the check constraint that checks, for every possible status, which companion fields must be set or NULL. The advantage over a check in application code: the rule is guaranteed to apply to every write access, including admin scripts or direct SQL updates that bypass the application logic. A transition that accidentally creates an inconsistent state, such as a "shipped" status without a tracking number, is rejected by the database itself.

CREATE TABLE customer_order (
    order_id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    status          VARCHAR(20) NOT NULL,
    cancelled_at    TIMESTAMP,
    tracking_number VARCHAR(64),
    CONSTRAINT chk_order_status_values
        CHECK (status IN ('draft', 'confirmed', 'shipped', 'cancelled')),
    -- state machine: each status enforces its own required companion fields
    CONSTRAINT chk_order_status_consistency
        CHECK (
            (status = 'cancelled' AND cancelled_at IS NOT NULL AND tracking_number IS NULL)
            OR (status = 'shipped' AND tracking_number IS NOT NULL AND cancelled_at IS NULL)
            OR (status IN ('draft', 'confirmed') AND cancelled_at IS NULL AND tracking_number IS NULL)
        )
);

4. Combining check constraints with custom functions

PostgreSQL allows a check constraint to call a user-defined function, as long as it is marked IMMUTABLE. This means the function must be guaranteed to always return the same result for the same input values, without depending on external state, the current time, or other tables. This restriction exists because the query planner and index maintenance must rely on deterministic behavior.

An IMMUTABLE function is excellent for encapsulating recurring, complex validation logic, such as a regular expression pattern for an IBAN check digit or a custom checksum, into a single function and then reusing that function across multiple CHECK constraints on different tables. This avoids code duplication between tables that must check the same business rule, such as a valid ISO country code check across several address tables.

-- PostgreSQL: reusable IMMUTABLE function for a business rule check
CREATE OR REPLACE FUNCTION is_valid_iso_country_code(code TEXT)
RETURNS BOOLEAN AS $$
    SELECT code ~ '^[A-Z]{2}$' AND code IN (
        'DE', 'AT', 'CH', 'FR', 'IT', 'ES', 'NL', 'BE', 'PL', 'US'
        -- full list truncated for brevity
    );
$$ LANGUAGE sql IMMUTABLE;

CREATE TABLE shipping_address (
    address_id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    country_code  CHAR(2) NOT NULL,
    CONSTRAINT chk_address_country_valid
        CHECK (is_valid_iso_country_code(country_code))
);

CREATE TABLE billing_address (
    address_id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    country_code  CHAR(2) NOT NULL,
    -- same business rule reused across a different table
    CONSTRAINT chk_billing_country_valid
        CHECK (is_valid_iso_country_code(country_code))
);

5. Preventing range overlaps with exclude constraints

Some business rules cannot be expressed as a simple CHECK constraint because they must compare multiple rows of the same table against each other, for example that two bookings for the same room must not overlap in time. PostgreSQL solves this problem with the EXCLUDE constraint, closely related to a check constraint but operating on row comparisons instead of a single row. With the btree_gist extension module and a range type such as tstzrange, an overlap rule can be enforced declaratively and index-backed.

The decisive advantage of an EXCLUDE constraint over application logic that manually checks for overlaps before every INSERT: the check is race-condition-free because it happens within the same transaction and locking mechanism as the write operation itself. Two concurrent booking attempts for the same time range therefore cannot both succeed, even under high concurrency.

-- PostgreSQL: prevent overlapping bookings for the same room
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE room_booking (
    booking_id  BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    room_id     INT NOT NULL,
    during      TSTZRANGE NOT NULL,
    -- no two bookings for the same room may overlap in time
    CONSTRAINT excl_room_booking_overlap
        EXCLUDE USING gist (room_id WITH =, during WITH &&)
);

-- This insert succeeds
INSERT INTO room_booking (room_id, during)
VALUES (101, tstzrange('2026-08-01 09:00', '2026-08-01 10:00'));

-- This insert fails: overlaps with the row above for the same room
INSERT INTO room_booking (room_id, during)
VALUES (101, tstzrange('2026-08-01 09:30', '2026-08-01 10:30'));

6. Limits of check constraints: what does not work

In standard SQL, a check constraint may only refer to columns of the same row, never to other rows of the same table or to other tables. A rule such as "this order may only exist if the referenced customer is active" therefore cannot be formulated as a CHECK constraint, and instead requires either a foreign key combined with a trigger, or a check in application code. PostgreSQL technically disallows subqueries in certain contexts, while MySQL and most other systems explicitly forbid subqueries in CHECK constraints.

A second important limit concerns non-deterministic expressions: a CHECK constraint with CURRENT_TIMESTAMP or RANDOM() is either forbidden or evaluated only once at write time, not continuously reevaluated, in many database systems. A rule such as "the start date must not be in the past" therefore only applies at the moment of writing, not permanently, which is correct for most use cases but a common misunderstanding.

7. Named constraints, error messages, and user experience

For complex business rules, a descriptive name for the check constraint is even more important than for simple range checks, because the constraint encodes several business conditions at once. A name such as chk_order_status_consistency immediately reveals which rule was violated, while a generic, auto-generated name in the error message gives no clue to the actual business cause.

Since the error message of a violated CHECK constraint usually contains the constraint name, the application can parse this name and translate it into an understandable, user-friendly message. A mapping table in application code that maps constraint names to localized error messages prevents end users from seeing a cryptic database error message, while the actual enforcement of the rule still reliably lives in the database.

8. A testing strategy for complex check constraints

A complex check constraint with several conditions and CASE branches deserves the same testing discipline as a function in application code. A pragmatic approach is a series of test cases that deliberately insert every valid and every invalid combination and expect the insert to either succeed or fail with the expected constraint name. These tests ideally run inside a transaction that is rolled back at the end so test data does not permanently pollute the database.

For state machines with many combinations, a table-driven test structure is recommended that systematically checks every status transition combination against its expected validity. This systematic test uncovers edge cases that are easily missed with purely manual testing, such as a forgotten combination in the CASE expression that accidentally passes as valid.

-- Table-driven test for the order state machine constraint,
-- rolled back at the end so no test data persists
BEGIN;

-- Valid: cancelled with cancelled_at, no tracking number
INSERT INTO customer_order (status, cancelled_at, tracking_number)
VALUES ('cancelled', now(), NULL);

-- Invalid: shipped without a tracking number, expected to fail
DO $$
BEGIN
    INSERT INTO customer_order (status, cancelled_at, tracking_number)
    VALUES ('shipped', NULL, NULL);
    RAISE EXCEPTION 'Expected constraint violation did not occur';
EXCEPTION
    WHEN check_violation THEN
        RAISE NOTICE 'Constraint correctly rejected invalid shipped row';
END $$;

ROLLBACK;

9. Check constraint vs trigger vs application code

The choice between a check constraint, a trigger, and a check in application code depends on whether the rule is limited to a single row, involves several rows or tables, and whether it can be evaluated deterministically.

10. Summary

A check constraint is far more than a check for positive prices. Multi-column conditions, state machines for status fields, IMMUTABLE functions for reusable validation logic, and EXCLUDE constraints against range overlaps allow complex business rules to be anchored directly and declaratively in the schema. The advantage over application code: the rule is guaranteed to hold regardless of access path, is race-condition-free, and is documented in the schema itself.

The limits lie where a check constraint would need to access other tables or involve external state, and here triggers or application code take over. Named constraints with descriptive names, a systematic testing strategy, and deliberately weighing constraint against trigger against application code make complex business rules traceable instead of scattered across multiple layers.

11. FAQ: Check Constraints for Complex Business Rules