CHECK Constraints, Defaults, and Business Rules

Learning outcomes

CHECK constraints and defaults allow the database to enforce many row-level rules close to the data. Used well, they prevent impossible states no matter which application writes to the database. Used poorly, defaults manufacture false facts and checks duplicate inconsistent logic across layers.

Simple CHECK constraints

quantity INTEGER NOT NULLCHECK (quantity > 0)charged_unit_price NUMERIC NOT NULLCHECK (charged_unit_price >= 0)

These rules reject invalid rows regardless of whether the write comes from a web app, CLI, import job, or SQL console.

Cross-column row rules

A check can compare columns in the same row:

CHECK (  ended_at IS NULL  OR ended_at >= started_at)

This ensures an assignment cannot end before it starts.

State-dependent attributes

Suppose a closed work order must have closed_at, while non-closed orders must not:

CHECK (  (status_code = 'closed' AND closed_at IS NOT NULL)  OR  (status_code <> 'closed' AND closed_at IS NULL))

Whether this exact rule is appropriate depends on workflow semantics—for example, cancelled orders may also need a closure timestamp.

Defaults represent business policy

A valid default:

status_code TEXT NOT NULL DEFAULT 'open'

is appropriate only if every newly created WorkOrder truly begins in the open state.

Fake defaults create bad data

These are suspicious:

country_code DEFAULT 'US'quantity DEFAULT 0birth_date DEFAULT '1900-01-01'customer_id DEFAULT 1

If the actual value is unknown, a fake default silently creates false information.

Database defaults versus application defaults

If an application assumes status open but the database defaults to new, behavior becomes inconsistent. Decide which layer owns the rule and keep definitions aligned.

Database defaults are valuable because they apply to every writer, but application code may still set values explicitly for clarity.

CHECK versus reference table

For a small fixed status set:

CHECK (priority IN ('low','normal','high','urgent'))

is simple. If priorities have SLA minutes, color, escalation policy, localization, or effective dates, model Priority as reference data instead.

CHECK constraints should express stable invariants

A rule such as quantity > 0 is stable and local. A rule such as “orders over $10,000 require manager approval” depends on workflow, authorization, perhaps changing policy, and other rows. It may not belong in a simple check.

Do not encode volatile policy as hard-coded schema logic blindly

If the business changes thresholds monthly, a reference/policy table plus transaction logic may be better than repeated migrations that modify check expressions.

Constraint naming

Named constraints improve diagnostics and migrations:

CONSTRAINT ck_assignment_time_orderCHECK (ended_at IS NULL OR ended_at >= started_at)

When the DBMS reports the violated constraint name, developers can quickly identify the business rule.

Domain checks

Useful local checks include:

CHECK (length(currency_code) = 3)CHECK (discount_percent BETWEEN 0 AND 100)CHECK (line_number > 0)CHECK (estimated_minutes >= 0)

Be careful not to mistake syntactic validity for complete semantic validity. A three-character string is not necessarily a real currency code.

Null and CHECK semantics

SQL three-valued logic can surprise beginners. A check expression that evaluates to unknown may be treated differently than false. If an attribute must be present, use NOT NULL explicitly rather than assuming a check will reject nulls.

Validation at multiple layers

It is not wasteful to validate important rules in both application and database layers when the responsibilities differ:

• application validation gives friendly early feedback;
• database constraints protect the invariant against every writer;
• tests confirm both layers agree.

WorkshopHub constraints

CREATE TABLE work_order_assignment (    assignment_id INTEGER PRIMARY KEY,    work_order_id INTEGER NOT NULL,    technician_id INTEGER NOT NULL,    started_at    TEXT NOT NULL,    ended_at      TEXT,    role_code     TEXT NOT NULL,    CONSTRAINT ck_assignment_time_order      CHECK (ended_at IS NULL OR ended_at >= started_at));

CREATE TABLE part_usage (    part_usage_id       INTEGER PRIMARY KEY,    work_order_id       INTEGER NOT NULL,    part_id             INTEGER NOT NULL,    quantity             INTEGER NOT NULL,    charged_unit_price  NUMERIC NOT NULL,    CONSTRAINT ck_part_usage_quantity      CHECK (quantity > 0),    CONSTRAINT ck_part_usage_price      CHECK (charged_unit_price >= 0));

Practice: design row-level constraints

Subscription(  starts_at,  ends_at,  status,  trial_ends_at,  monthly_price)

Summary and next lesson

CHECK constraints protect row-level invariants and defaults encode legitimate initial states. They are powerful precisely because every writer must respect them, but they should not be stretched to represent cross-row or highly dynamic policy. The final lesson of Chapter 6 addresses those broader rules.

References

• PostgreSQL documentation for CHECK, DEFAULT, and constraint behavior.
• SQLite documentation for CHECK constraints.
• C. J. Date, Database Design and Relational Theory.