The CHECK Constraint: DB-Level Domain Rules | Database & SQL

Definition

A CHECK is a boolean rule the database enforces on every INSERT and UPDATE, rejecting bad values for every writer, so it cannot be bypassed like app-only validation.

CHECK constraint – a table- or column-level boolean condition that each row must satisfy.
Column-level CHECK – references one column, e.g., CHECK (balance >= 0).
Table-level CHECK – references multiple columns, e.g., CHECK (close_date >= open_date).
Three-valued logic – if the expression evaluates to NULL (unknown), the check is satisfied, not violated.
Rejection – the database rejects the entire INSERT or UPDATE statement if the condition is false.
NOT VALID – add a CHECK without scanning existing rows; VALIDATE later to avoid a long lock on large tables.

A CHECK constraint is like a hard rule stamped on National Bank of India's ledger itself: "a balance can never go below zero" or "an account's status must be one of ACTIVE, INACTIVE, or FROZEN." No matter who walks up to write, the mobile app, the branch system, a nightly batch, or an engineer typing SQL directly, the ledger refuses to record anything that breaks the rule. If one app forgets to check, the ledger still says no.

Understanding the Concepts

What is a CHECK constraint?

A CHECK constraint is a boolean expression attached to a column or table that the data in each row must satisfy. The database evaluates it on every INSERT and UPDATE.

SQL

-- Column-level CHECK: balance must be non-negative
CREATE TABLE accounts (
    account_id   VARCHAR(20) PRIMARY KEY,
    holder_name  VARCHAR(100) NOT NULL,
    balance      NUMERIC(15,2) NOT NULL
        CONSTRAINT chk_balance CHECK (balance >= 0),
    status       VARCHAR(20) NOT NULL
        CONSTRAINT chk_status CHECK (status IN ('ACTIVE', 'INACTIVE', 'FROZEN'))
);

-- Table-level CHECK: close date must be after open date
CREATE TABLE loans (
    loan_id    VARCHAR(20) PRIMARY KEY,
    open_date  DATE NOT NULL,
    close_date DATE,
    CONSTRAINT chk_dates CHECK (close_date IS NULL OR close_date >= open_date)
);

When does the database reject a row?

The database evaluates the CHECK on every INSERT and every UPDATE that touches the constrained columns. If the condition evaluates to false, the operation is rejected.

SQL

-- INSERT that violates CHECK: rejected
INSERT INTO accounts (account_id, holder_name, balance, status)
VALUES ('SAV2001', 'Rajesh Kumar', -500.00, 'ACTIVE');
-- ERROR: new row for relation "accounts" violates
-- check constraint "chk_balance"

-- UPDATE that violates CHECK: rejected
UPDATE accounts SET balance = -1000.00 WHERE account_id = 'SAV2001';
-- ERROR: new row violates check constraint "chk_balance"

-- UPDATE with invalid status: rejected
UPDATE accounts SET status = 'DELETED' WHERE account_id = 'SAV2001';
-- ERROR: new row violates check constraint "chk_status"

-- Valid operations: succeed
INSERT INTO accounts VALUES ('SAV2001', 'Rajesh Kumar', 50000.00, 'ACTIVE');
UPDATE accounts SET balance = 0.00 WHERE account_id = 'SAV2001';  -- 0 is >= 0

How does three-valued logic affect CHECK?

If the CHECK expression evaluates to unknown because a value is null, the check is treated as satisfied, not violated. This is a subtle but important rule.

SQL

-- CHECK (balance >= 0) with a nullable balance column
CREATE TABLE test_nullable (
    id      INT PRIMARY KEY,
    balance NUMERIC(15,2) CHECK (balance >= 0)
    -- Note: balance is NOT marked NOT NULL
);

INSERT INTO test_nullable VALUES (1, 50000.00);  -- OK: 50000 >= 0
INSERT INTO test_nullable VALUES (2, NULL);        -- OK: NULL >= 0 = UNKNOWN
                                                   -- UNKNOWN is NOT false
                                                   -- so CHECK is satisfied
INSERT INTO test_nullable VALUES (3, -100.00);     -- REJECTED: -100 >= 0 = FALSE

-- To also forbid NULL, add a separate NOT NULL constraint
-- CHECK only catches FALSE, not UNKNOWN

Why is a DB-level CHECK safer than app-only validation?

Three reasons: multiplicity, bypass paths, and race conditions.

JAVA

package com.bank.service;

import java.math.BigDecimal;
import java.sql.*;
import java.util.logging.Logger;

/**
 * App-only validation can be bypassed.
 * The CHECK constraint cannot.
 */
public class ValidationComparison {

    private static final Logger LOG = Logger.getLogger(
        ValidationComparison.class.getName());

    // Problem 1: Multiple writers, inconsistent validation
    // App A checks balance >= 0 before update
    // App B (batch loader) does NOT check balance
    // Direct SQL session does NOT run app validation
    // CHECK constraint catches ALL of them

    // Problem 2: Race condition
    // Thread 1: reads balance = 50000, checks 50000 >= 40000, proceeds
    // Thread 2: reads balance = 50000, checks 50000 >= 40000, proceeds
    // Both UPDATE: balance = 50000 - 40000 - 40000 = -30000
    // App check passed for both, but CHECK rejects the second commit

    public void safeDebit(Connection conn, String accountId, BigDecimal amount)
            throws SQLException {
        // The CHECK constraint is the safety net
        String sql = "UPDATE accounts SET balance = balance - ? "
                   + "WHERE account_id = ? AND balance >= ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setBigDecimal(1, amount);
            ps.setString(2, accountId);
            ps.setBigDecimal(3, amount);
            int rows = ps.executeUpdate();
            if (rows == 0) {
                LOG.warning("Debit rejected: insufficient balance");
            }
        }
        // Even if the WHERE clause is somehow bypassed,
        // the CHECK (balance >= 0) catches it at the DB level
    }
}

DIAGRAM

Why DB CHECK wins over app-only validation:

  Scenario                        App-Only    DB CHECK
  ──────────────────────────────  ──────────  ────────
  Main app validates correctly     ✓           ✓
  Batch job forgets to validate    ✗ (bypass)  ✓
  Engineer runs direct SQL         ✗ (bypass)  ✓
  Migration script updates rows    ✗ (bypass)  ✓
  Race condition between threads   ✗ (race)    ✓ (atomic)
  Another microservice writes      ✗ (no code) ✓

What are the rules for CHECK expressions?

Keep CHECK expressions deterministic and based on the row's own columns. Standard CHECK cannot reference other tables or subqueries. For cross-row rules, use triggers or exclusion constraints.

SQL

-- Valid: references columns in the same row
CHECK (balance >= 0)
CHECK (status IN ('ACTIVE', 'INACTIVE', 'FROZEN'))
CHECK (close_date >= open_date)
CHECK (max_withdrawal > 0 AND max_withdrawal <= 500000)

-- Invalid (standard SQL): cannot reference other tables
-- CHECK (account_id IN (SELECT account_id FROM valid_accounts))
-- For cross-table rules, use a FOREIGN KEY or trigger

How do you add a CHECK to a large existing table safely?

Adding a CHECK to a large table validates every existing row by default, which locks the table. Use NOT VALID first, then VALIDATE separately.

SQL

-- Step 1: Add CHECK without scanning (instant)
ALTER TABLE accounts
ADD CONSTRAINT chk_balance_positive
CHECK (balance >= 0) NOT VALID;
-- Does NOT scan existing rows
-- New inserts/updates are checked immediately

-- Step 2: Validate existing rows (scans but no exclusive lock)
ALTER TABLE accounts
VALIDATE CONSTRAINT chk_balance_positive;
-- Scans existing rows to verify compliance
-- Does not hold an exclusive lock (ShareUpdateExclusiveLock)

When should you use CHECK vs application validation?

Use both. Application validation gives fast, friendly user feedback. Database CHECK is the safety net that catches everything the application misses.

DIAGRAM

Layered Validation:

  Layer 1: Client-side (JavaScript)  ──► Fast feedback, easily bypassed
  Layer 2: Server-side (Java)        ──► Proper validation + error messages
  Layer 3: Database (CHECK)          ──► Last resort, catches EVERYTHING

  Application validation COMPLEMENTS, never REPLACES, DB constraints.

What should a senior engineer know?

A senior should know that CHECK with a function call must be deterministic (no random(), no now()). They should also know that PostgreSQL's exclusion constraints (EXCLUDE USING) can enforce non-overlapping ranges, which CHECK alone cannot do.

SQL

-- Exclusion constraint: no two loans for the same customer
-- can have overlapping date ranges
CREATE TABLE loans (
    loan_id      VARCHAR(20) PRIMARY KEY,
    customer_id  VARCHAR(20) NOT NULL,
    start_date   DATE NOT NULL,
    end_date     DATE NOT NULL,
    EXCLUDE USING gist (
        customer_id WITH =,
        daterange(start_date, end_date) WITH &&
    )
);
-- This prevents overlapping loan periods for the same customer
-- Something CHECK alone cannot enforce

Visual Overview

DIAGRAM

┌──────────────────────────────────────────────────────┐
│         CHECK Constraint Enforcement                 │
├──────────────────────────────────────────────────────┤
│                                                      │
│  INSERT / UPDATE                                     │
│       │                                              │
│       ▼                                              │
│  ┌────────────────────────────────┐                   │
│  │ Evaluate CHECK (balance >= 0) │                   │
│  └──────────┬─────────────────────┘                   │
│             │                                        │
│     ┌───────┴───────┐                                │
│     │               │                                │
│   FALSE           TRUE / UNKNOWN                     │
│     │               │                                │
│     ▼               ▼                                │
│   REJECT          PROCEED                            │
│   (error)         (write the row)                    │
│                                                      │
│  Three-Valued Logic:                                 │
│    balance = 50000  → 50000 >= 0 → TRUE → proceed    │
│    balance = -100   → -100 >= 0  → FALSE → reject    │
│    balance = NULL   → NULL >= 0  → UNKNOWN → proceed │
│                                                      │
│  Who is checked?                                     │
│  ┌──────────────┐                                    │
│  │ App A        │──► CHECK ✓                         │
│  │ Batch Job    │──► CHECK ✓                         │
│  │ Direct SQL   │──► CHECK ✓                         │
│  │ Migration    │──► CHECK ✓                         │
│  └──────────────┘                                    │
│  Every writer passes through the constraint.         │
└──────────────────────────────────────────────────────┘

Key Takeaway

A CHECK constraint is a boolean rule attached to a table or column that the data in each row must satisfy. The database evaluates it on every INSERT and UPDATE, and if the condition comes out false it rejects the statement. One important subtlety is three-valued logic: if a value is null the condition evaluates to unknown, and unknown is treated as satisfied rather than violated, so CHECK (balance >= 0) still permits a null balance unless you also add NOT NULL.

The reason a database-level CHECK is safer than validating only in the application comes down to three things. First, multiplicity: many services write to the same table and each would otherwise have to implement the exact same rule. Second, bypass paths: data arrives through admin tools, migration scripts, and direct SQL that never run the application code. Third, race conditions: an application check that reads then writes can be defeated by concurrency, whereas the constraint is evaluated atomically.

At the National Bank of India, Pankaj put CHECK (balance >= 0) and CHECK (status IN ('ACTIVE', 'INACTIVE', 'FROZEN')) directly on the accounts table. When a withdrawal path once forgot its own balance check, the over-withdrawal was still rejected by the database. When a batch loader tried to write a stray status value, the CHECK caught it.

Application validation still earns its place for fast, friendly feedback, but it complements rather than replaces the database constraint. For large existing tables, add the CHECK with NOT VALID first and VALIDATE later to avoid a long lock.

Remember one sentence: a CHECK is a boolean rule the database enforces on every INSERT and UPDATE, rejecting bad values for every writer, which is exactly why it cannot be bypassed the way app-only validation can.

---