A CHECK Constraint Can Accept NULL

Suppose a developer tool stores a per-project quota for requests to an AI model. A positive number means a custom limit; NULL means the project uses a separately defined default. The schema can allow both states, but a CHECK constraint alone cannot express “a positive number must be present.”

In PostgreSQL, this definition accepts NULL:

CREATE TABLE model_projects (
    project_id bigint PRIMARY KEY,
    custom_quota integer CHECK (custom_quota > 0)
);

The comparison NULL > 0 evaluates to SQL’s unknown value. As the PostgreSQL constraints guide explains, a CHECK constraint is satisfied when its expression is true or null; it rejects false. A stored quota of 0 or -1 violates the constraint. An absent quota does not. The database is enforcing the stated rule, even if an application developer expected the column to be required.

That behavior is useful when absence has a defined meaning. If NULL selects the default quota, keep the column nullable and document that meaning in the application and schema. If every project must carry its own quota, change the column declaration to custom_quota integer NOT NULL CHECK (custom_quota > 0). NOT NULL rejects absence; CHECK rejects present nonpositive values. PostgreSQL’s guide shows these constraints together and notes that an explicit NOT NULL is more efficient than expressing the same condition through a check.

Three-valued logic can also affect checks involving several columns. A condition such as CHECK (used_tokens <= custom_quota) evaluates to unknown when custom_quota is null. It therefore cannot enforce a relationship for projects using the default. If that relationship matters, apply it to the effective quota through the appropriate schema or application design; do not assume the nullable comparison enforces it. A CHECK is intended to validate the row being inserted or updated, and PostgreSQL warns against using it to reference other rows or tables.

Decide what NULL means before writing the constraint. Test inserts for a positive quota, zero, a negative quota, and NULL, then verify both database acceptance and the application’s default-selection behavior. That small matrix exposes the gap between a required positive value and an optional positive override.