CHECK Constraint in SQL

Introduction

The SQL CHECK constraint enforces a condition on values stored in a table. Whenever relevant data is inserted or updated, the database evaluates the expression and rejects a row when the result is false. This makes the table a boundary for valid states rather than a passive container.

CREATE TABLE employees (
    employee_id BIGINT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    age INTEGER,
    CONSTRAINT chk_employee_age CHECK (age >= 18)
);

Values 18 and 25 satisfy this rule, while 17 does not. The constraint protects every writer: applications, scripts, imports, stored procedures, administrative tools, and future services.

What a CHECK Constraint Represents

A good CHECK expresses an invariant: a condition that must always hold for every valid stored row. Examples include positive quantity, a percentage between zero and one hundred, an end date after a start date, or a status from a small stable set.

The database evaluates the condition against the row's proposed state. A valid insert remains valid after later updates because any update affecting the rule is checked again.

Basic and Named Syntax

A CHECK can appear beside a column or at table level. Explicit names improve error messages, schema inspection, migration scripts, and API error mapping.

CREATE TABLE products (
    product_id BIGINT PRIMARY KEY,
    price DECIMAL(12,2),
    CONSTRAINT chk_products_price
      CHECK (price > 0)
);

A common convention is chk_<table>_<rule>. Name the business rule, not merely the column, especially when several columns participate.

Numeric Ranges and Boundaries

Range checks are simple but boundary operators must match the requirement exactly. quantity > 0 rejects zero, while quantity >= 0 permits it. A rating from one through five needs inclusive boundaries.

CONSTRAINT chk_reviews_rating
CHECK (rating BETWEEN 1 AND 5)

BETWEEN is inclusive at both ends. Write boundary tests before deployment so zero, minimum, maximum, and just-outside values are intentional rather than guessed.

Percentages, Money, and Units

A percentage can be stored as 0 through 100 or as a fraction from 0 through 1. The CHECK must match the chosen representation. A rule is misleading when the unit is undocumented.

discount_rate DECIMAL(5,4) NOT NULL,
CONSTRAINT chk_discount_rate
CHECK (discount_rate >= 0 AND discount_rate <= 1)

Use DECIMAL for exact monetary or percentage rules when binary floating-point rounding would create surprising edges. The data type defines representation; CHECK narrows the valid domain.

Controlled String Values

A CHECK can restrict a column to a small, stable list of values.

status VARCHAR(20) NOT NULL,
CONSTRAINT chk_orders_status
CHECK (status IN ('PENDING', 'PAID', 'CANCELLED'))

This protects spelling and unsupported states. Decide case and whitespace policy explicitly. Canonical uppercase values simplify comparison, but conversion should occur before storage rather than relying on every query to normalize.

CHECK or Lookup Table?

Use a CHECK list when the domain is tiny, stable, and has no additional attributes. Use a reference table with a foreign key when values change independently, need labels, sort order, localization, activation dates, permissions, or other metadata.

A frequently changing policy encoded in table DDL creates repeated schema deployments. A lookup table can model governed data, while a CHECK is strongest for durable row invariants.

CHECK Is Not a Workflow Engine

A status list proves that the current value is allowed; it does not prove that the transition from the previous status was legal. A row may satisfy status IN (...) even if an application changed it directly from PENDING to REFUNDED against policy.

Transition rules depend on old and new state, actor, time, or related records. Handle them through transactional application logic, procedures, triggers, or a dedicated workflow model while retaining a CHECK for the valid state domain.

Cross-Column CHECK Constraints

Table-level checks can compare columns in the same row. This protects relationships that individual column rules cannot express.

CREATE TABLE bookings (
    booking_id BIGINT PRIMARY KEY,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    CONSTRAINT chk_booking_dates
      CHECK (end_date > start_date)
);

The strict operator requires at least one later date. If same-day bookings are valid, use >=. The correct expression comes from the domain.

Conditional Business Rules

A CHECK can require one value only under a particular condition. An approved request may require an approver, while a draft may not.

CONSTRAINT chk_request_approval
CHECK (
    status <> 'APPROVED'
    OR approved_by IS NOT NULL
)

Read this as implication: if status is APPROVED, an approver must exist. For complex conditions, write a truth table first and keep parentheses explicit.

Mutually Consistent Columns

CHECK constraints can prevent contradictory combinations, such as a closed ticket without a closure timestamp or a non-closed ticket with one.

CONSTRAINT chk_ticket_closed_state
CHECK (
  (status = 'CLOSED' AND closed_at IS NOT NULL)
  OR
  (status <> 'CLOSED' AND closed_at IS NULL)
)

This two-way rule is stronger than requiring a timestamp only when closed. Determine whether both directions are truly required before enforcing them.

CHECK and NULL: The Critical Rule

SQL uses three-valued logic: expressions can be true, false, or unknown. A CHECK rejects false. A result of true or unknown is accepted. Therefore, CHECK (salary > 0) normally permits NULL because comparing NULL produces unknown.

CHECK does not automatically mean NOT NULL. Add NOT NULL when the value is mandatory. This distinction is one of the most common sources of incorrect constraint designs.

Optional Values With CHECK

For an optional value, allowing NULL while validating present values is often exactly right.

termination_date DATE NULL,
CONSTRAINT chk_termination_date
CHECK (termination_date IS NULL
       OR termination_date >= hire_date)

The explicit form communicates intent and remains readable when more columns are involved. Test every meaningful NULL combination rather than relying on intuition.

Three-Valued Logic in Cross-Column Rules

Suppose a row has CHECK (end_date >= start_date). If either date is NULL, the result is unknown and the row may pass. That may be appropriate for an optional end date but wrong when both dates are required.

Combine NOT NULL constraints with CHECK for mandatory dates, or express permitted NULL combinations directly. Constraint design should state both presence and relationship rules.

AND, OR, NOT, and Parentheses

Logical operators let CHECK expressions combine conditions, but precedence can make a correct-looking rule behave incorrectly. Parenthesize conceptual groups even when the database's precedence rules would produce the same result.

CHECK (
  (account_type = 'TRIAL' AND credit_limit = 0)
  OR
  (account_type = 'PAID' AND credit_limit >= 0)
)

Readable constraints are easier to review, test, migrate, and explain when a violation occurs.

Booleans and Flag Columns

Use a native Boolean type where supported. In systems that represent flags with integers or characters, a CHECK can restrict the domain to 0 and 1 or Y and N.

A flag often becomes unclear as requirements grow. If a value evolves beyond two states, replace it with an explicit status model rather than adding more loosely related flags that can contradict one another.

Empty Strings and Whitespace

NOT NULL prevents a missing value but may still permit an empty or whitespace-only string. A CHECK can require meaningful content.

name VARCHAR(100) NOT NULL,
CONSTRAINT chk_name_not_blank
CHECK (TRIM(name) <> '')

String functions, empty-string semantics, character sets, and collations differ by DBMS. Test portability and decide whether normalization belongs in the application, database, or both.

Avoid Complex Email and Phone Rules

Simple structural checks can reject obvious mistakes, but full email and international phone validation is too complex and changeable for a large CHECK expression. Overly strict patterns reject valid real-world values and are difficult to maintain.

Use application validation, verification workflows, and specialized libraries for rich syntax. Reserve database checks for stable, defensible invariants such as maximum normalized length or a required canonical format.

CHECK Versus NOT NULL

NOT NULL requires a value. CHECK restricts a value or combination of values. A mandatory positive quantity needs both.

quantity INTEGER NOT NULL,
CONSTRAINT chk_quantity_positive CHECK (quantity > 0)

Separating responsibilities makes the schema easier to understand and produces more specific violations.

CHECK Versus FOREIGN KEY

CHECK validates an expression based on permitted row data. FOREIGN KEY proves that a referenced candidate key exists in another table. Do not copy a changing list of department IDs into a CHECK expression.

Use a foreign key for relational membership. A CHECK list can be appropriate for a truly fixed, tiny domain that does not need metadata or independent lifecycle management.

CHECK Versus UNIQUE and PRIMARY KEY

UNIQUE compares rows to prevent duplicate candidate keys. PRIMARY KEY selects the table's unique, non-null identity. CHECK normally evaluates each proposed row and cannot replace either cross-row rule.

A well-designed table often uses all of them: primary identity, alternate business uniqueness, relationships, required values, and row-domain checks.

CHECK Versus DEFAULT

DEFAULT supplies a value when an insert omits the column. It does not restrict explicitly supplied values. CHECK restricts values but does not generate one.

status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
CONSTRAINT chk_status
CHECK (status IN ('PENDING', 'PAID', 'CANCELLED'))

The default itself must satisfy every relevant constraint. Design defaults and allowed domains together.

Column-Level and Table-Level Placement

A simple rule involving one column can be written beside that column. Cross-column rules require table-level placement. Because SQL standards and vendors differ in how placement affects expression scope, named table-level constraints are often the clearest portable style.

Placement does not change the need for a precise name, documented semantics, and boundary tests.

Several Focused Checks or One Large Check?

Separate independent rules so violations identify the problem clearly. Price positivity and inventory range are easier to understand as two named checks than as one long AND expression.

Keep one logical invariant together when splitting it would lose meaning, such as the relationship among status, approval user, and approval timestamp. Constraint granularity should follow business-rule granularity.

Stable Invariants Make the Best Checks

A strong CHECK remains true whenever stored data is valid. Quantity greater than zero or end date after start date are stable row invariants. A temporary promotion limit or policy that changes monthly is usually a poor candidate.

Frequently changing policy belongs in configurable data or business logic. External state, such as a remote service response, cannot be reliably represented by a normal row CHECK.

Current Time and Derived State

Rules involving the current time are problematic because a row can become invalid as time passes without any write to trigger reevaluation. A check such as expiration date greater than the current date may be true today and false tomorrow while remaining stored.

Store the objective timestamp and derive whether it is expired at query or workflow time. Use CHECK for stable relationships among stored values, not moving environmental conditions.

Functions and Subqueries

DBMS products differ in which functions are permitted in CHECK expressions. Functions should be deterministic and stable for the same row values. Session-dependent, locale-dependent, or volatile behavior makes stored validity unpredictable.

Subqueries and direct cross-table checks are commonly restricted because other-table changes could invalidate rows without touching them. Use FOREIGN KEY, UNIQUE, triggers, materialized models, or transactional logic for cross-table rules.

JSON Columns and Relational Invariants

Some databases provide JSON functions that can support basic checks such as valid shape, expected type, or required property. However, hiding core identifiers, money, status, and dates inside JSON weakens ordinary relational constraints and indexing.

Keep stable core attributes in typed columns and use JSON for flexible edge data. Validate both layers according to their purpose.

Choose the Data Type Before the CHECK

A CHECK does not repair a poor data type. Store dates as date types, exact amounts as DECIMAL, and counts as integer types. Then use CHECK to narrow the valid range.

A VARCHAR column checked with numeric-looking patterns is still weaker and harder to query than a numeric column. Type first, NULLability second, and domain constraints third is a useful design order.

Precision, Length, and Canonical Representation

Column precision and length already establish physical bounds. CHECK adds semantic bounds, such as an amount being nonnegative or a code matching an allowed representation. Avoid duplicating rules that the type already guarantees unless the additional expression documents a narrower business meaning.

Decide whether case and whitespace are significant. Canonical storage reduces duplicate representations and simplifies downstream logic.

Adding CHECK to Existing Data

Before adding a constraint, query for rows that would violate it. Profile boundary values, NULLs, unexpected categories, and cross-column contradictions.

SELECT product_id, rating
FROM products
WHERE rating IS NULL
   OR rating < 1
   OR rating > 5;

Investigate why violations exist before changing them. A negative amount may represent a refund rather than corrupt data, revealing that the proposed rule or model is incomplete.

Constraint Migration Is Data Migration

Adding or tightening a CHECK changes the set of representable states. Existing data, every writer, import format, ORM model, report correction tool, and rollback version must be considered.

For rolling deployments, update writers before enforcing a tighter rule. Relaxations are usually backward-compatible for writes but older readers may still assume the previous domain. Schema compatibility works in both directions.

Large-Table Validation

A short ALTER TABLE statement can scan a large table, consume CPU and I/O, acquire locks, generate logs, and delay replication. Prevalidation queries estimate violations and runtime but do not eliminate races with concurrent writes.

Use DBMS-specific online or staged validation features when appropriate. Confirm through metadata that the final constraint is enabled, validated, and trusted.

Bulk Loads, Staging, and ETL

Raw staging tables may temporarily accept data that core tables must reject. Validate, normalize, map, and report bad rows before moving data into constrained tables.

Disabling CHECK constraints for speed creates a window for invalid data. If a controlled process requires it, restrict permissions, record the state, revalidate afterward, and fail the pipeline if trust is not restored.

Applications, ORMs, and Stored Procedures

Application validation provides immediate, friendly feedback; the database CHECK protects the final shared state. ORM annotations and client-side forms do not cover scripts, imports, race conditions, or future writers.

Keep rule definitions aligned through tests and generated metadata where practical. Treat a constraint violation as useful evidence of an incompatible writer rather than an inconvenience to suppress.

Violations and Transaction Behavior

When a proposed insert or update violates a CHECK, the database rejects that statement according to its transaction and error-handling rules. The invalid row should not be partially stored. In a larger transaction, application code must understand whether the statement alone failed or whether the transaction must be rolled back before further work.

Do not catch a constraint error and silently substitute an arbitrary value. Return a domain-appropriate message, retain the constraint name and correlation context in internal logs, and decide whether the operation is safe to retry. Retrying unchanged invalid data only repeats the failure.

Batch operations need explicit policy for all-or-nothing behavior versus rejected-row isolation. Staging and validating rows before the final insert can produce useful error reports without weakening the destination table. The constrained core remains the authoritative valid-state boundary.

DBMS Portability

PostgreSQL, SQL Server, Oracle, MySQL, and SQLite differ in expression support, function rules, constraint states, naming syntax, NULL details, and historical enforcement behavior. Modern versions may behave differently from older deployments.

Test on the exact production engine and version. Avoid assuming that a migration validated by an in-memory test database has identical semantics or operational cost.

Performance Characteristics

CHECK evaluation occurs on relevant writes. Simple deterministic expressions are normally inexpensive, while complex functions can increase cost. CHECK is not an index and normally does not make queries faster by itself.

Some optimizers can use trusted constraints to reason about impossible values, but integrity should be the primary design goal. Measure write behavior at expected volume and keep expressions understandable.

Testing a CHECK Constraint

Test valid interior values, exact lower and upper boundaries, values immediately outside each boundary, NULL, empty strings where relevant, and updates from valid to invalid states. For multi-column checks, create a truth table covering every meaningful combination.

Test bulk and ORM paths as well as direct SQL. After deployment, inspect schema metadata and run a deliberately invalid statement inside a rolled-back transaction to confirm enforcement.

Real-World Combined Example

CREATE TABLE order_items (
    order_item_id BIGINT PRIMARY KEY,
    order_id BIGINT NOT NULL,
    quantity INTEGER NOT NULL,
    unit_price DECIMAL(12,2) NOT NULL,
    discount_rate DECIMAL(5,4) NOT NULL DEFAULT 0,
    CONSTRAINT fk_item_order
      FOREIGN KEY (order_id) REFERENCES orders(order_id),
    CONSTRAINT chk_item_quantity CHECK (quantity > 0),
    CONSTRAINT chk_item_price CHECK (unit_price >= 0),
    CONSTRAINT chk_item_discount
      CHECK (discount_rate BETWEEN 0 AND 1)
);

Each constraint has one responsibility: identity, relationship, required values, positive quantity, nonnegative price, and discount range. Historical selling price remains on the item even when the product's current price changes.

Store Birth Date Instead of Mutable Age

An age column appears easy to constrain, but age changes as time passes even when the row is untouched. Storing age >= 18 can therefore preserve a value that becomes stale. Store a birth date and calculate age when the business operation needs it.

Eligibility may depend on the date of application, purchase, or event rather than today's date. Record that decision context when it matters historically. CHECK cannot replace good modeling: it can validate a stored value without proving that the value is the right fact to store.

Negative Values Are Domain-Specific

A general rule such as balance >= 0 may look safe but reject legitimate overdrafts, credits, corrections, or accounting entries. Inventory below zero may be invalid in one warehouse and intentionally represent backorders in another.

Ask what a negative value means before prohibiting it. Sometimes the correct model uses transaction rows, separate debit and credit fields, explicit adjustment types, or a policy limit. Database validity must follow domain meaning rather than a universal preference for positive numbers.

Loan and Financial Examples

A loan can require positive principal, a nonnegative interest rate, and an end date after the start date. An account may permit a balance down to its approved overdraft limit rather than zero.

CONSTRAINT chk_loan_terms CHECK (
    principal_amount > 0
    AND annual_rate >= 0
    AND maturity_date > start_date
)

Keep currency, precision, and legal rounding rules explicit. A row-level CHECK cannot confirm a market rate, external approval, or total derived from another table.

Exam and Scoring Examples

An exam result may require score BETWEEN 0 AND maximum_score, which is a cross-column invariant. If absence is represented by NULL, decide whether score and maximum should both be present, both absent, or governed by an explicit attendance status.

Percentage checks should not assume every exam uses a one-hundred-point scale. Model raw score and maximum accurately, then calculate percentage. Avoid storing redundant derived values unless a clear historical or performance requirement justifies them.

Reservation and Subscription Examples

A reservation normally requires checkout after check-in, positive guest count, and perhaps a cancellation timestamp only when cancelled. A subscription may require an end date after its start date, while an open-ended plan allows end date to be NULL.

Current availability and room capacity across other reservations are cross-row or cross-table rules, not ordinary CHECK candidates. Use transactions and appropriate locking for those decisions while CHECK protects the row's internally consistent dates and state.

One-Way and Two-Way Consistency

A one-way rule states that if a condition is true, another value is required. It does not prohibit that value in other states. A two-way rule requires both directions, such as closed rows having closed_at and all other rows having it NULL.

Two-way rules are stronger and can block legitimate preparatory data. Write examples for each status and timestamp combination before deciding. Logical precision matters more than expression brevity.

Tightening a CHECK Constraint

Tightening a rule reduces the accepted domain, such as changing a maximum discount from 50 percent to 30 percent. Existing rows may violate it, and older application versions may continue sending values that were previously valid.

Deploy compatible writers first, analyze history, decide whether old values remain valid, and then enforce the new boundary. When policy applies only to future records, adding an effective-date model may be more accurate than retroactively declaring history invalid.

Relaxing or Removing a CHECK

Relaxing a rule permits new values that older readers, reports, and integrations may not understand. Removing a constraint weakens protection for every writer. Neither change is automatically safe merely because current writes stop failing.

Investigate violations before dropping a rule. A failure can reveal an application defect, unit mismatch, unexpected migration, or changing business requirement. Update documentation and tests with the schema so the broader domain is intentional.

Version Control, Documentation, and Schema Drift

Constraint definitions should live in versioned migrations and be reviewed with the business rule they implement. Document units, NULL meaning, boundary inclusiveness, controlled values, and the owner who approves future changes.

Compare production metadata with expected schema to detect drift, disabled constraints, inconsistent names, or migrations skipped in one environment. A rule described only in application code or a wiki can diverge silently from the database.

Integrity Is Not the Same as Accuracy

A value can satisfy every CHECK and still be factually wrong. A valid date may be entered for the wrong person, a permitted status may be selected accidentally, and a positive price may use the wrong currency. Constraints prove structural and domain consistency, not human intent.

Reliable data quality also requires verification workflows, authorization, reconciliation, source lineage, review, and monitoring. CHECK is one strong preventive layer, not a complete quality system.

Truth Tables Improve Conditional Checks

For each conditional rule, list inputs and expected acceptance before writing SQL. Include every status, required and missing values, NULL combinations, and boundary cases. Translate the approved table into Boolean logic, then execute those rows against the actual DBMS.

This technique exposes missing branches and accidental unknown results. Keep the truth table in automated tests so future expression changes preserve intended behavior rather than merely compiling successfully.

Monitor CHECK Violations After Deployment

Track violations by constraint name, application, endpoint, import job, release, and supplied category of value. A sudden increase can reveal an outdated client, changed source format, incorrect units, or an incomplete rollout.

Monitor validation duration, lock waits, replication lag, and rejected ETL rows during migrations. Alerts should identify the responsible rule quickly, which is another reason to use stable descriptive names and focused constraints.

Common CHECK Constraint Mistakes

  • Assuming CHECK automatically rejects NULL.
  • Choosing the wrong inclusive or exclusive boundary.
  • Using CHECK instead of FOREIGN KEY, UNIQUE, or PRIMARY KEY.
  • Encoding frequently changing policy in table DDL.
  • Writing complex expressions without a truth table.
  • Duplicating conflicting rules across applications and schemas.
  • Ignoring existing invalid data and older application versions.
  • Blindly rewriting values without understanding their meaning.
  • Assuming functions and semantics are portable across databases.
  • Adding a large-table constraint without operational planning.

Production Design Checklist

  • State the invariant in plain language before writing SQL.
  • Choose the correct data type and units.
  • Define NULL meaning and add NOT NULL when required.
  • Specify inclusive and exclusive boundaries explicitly.
  • Choose CHECK versus reference data based on domain stability.
  • Use truth tables for conditional and cross-column rules.
  • Name constraints by their business purpose.
  • Profile existing data and investigate violations.
  • Inventory applications, ETL, scripts, and old deployment versions.
  • Plan validation, locks, logs, replication, monitoring, and rollback.
  • Test the exact production database engine.
  • Document ownership and future rule changes.

Interview Questions and Answers

What is a CHECK constraint? A database constraint that rejects a proposed row when its defined condition evaluates to false.

Does CHECK reject NULL? Not automatically. A comparison involving NULL often evaluates to unknown, which CHECK accepts; use NOT NULL when presence is required.

Can CHECK use multiple columns? Yes, table-level checks commonly enforce cross-column invariants.

CHECK versus FOREIGN KEY? CHECK validates a row expression; FOREIGN KEY validates existence of a referenced candidate key.

Can CHECK enforce workflow transitions? It can restrict current states, but normal CHECK constraints do not compare the previous state or actor.

Does CHECK improve query performance? Its primary purpose is integrity, not indexing or query acceleration.

Can it be added later? Yes, but existing data and production validation cost must be handled safely.

What makes a good CHECK? A deterministic, readable, stable invariant based on appropriately typed row data.

Key Takeaway

A CHECK constraint defines which row states the database will accept. Its strength comes from precision: correct types, intentional NULL semantics, exact boundaries, readable logic, and a rule stable enough to belong in the schema.

RELIABLE CHECK = CLEAR BUSINESS INVARIANT
               + CORRECT DATA TYPE
               + EXPLICIT NULL POLICY
               + TESTED BOUNDARIES
               + PRECISE LOGIC
               + MEANINGFUL NAME
               + CLEAN EXISTING DATA
               + SAFE VALIDATION

Combine CHECK with NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, defaults, and transactional business logic according to each mechanism's responsibility. Use application validation for experience and database constraints for authoritative protection.