Check Constraints Validation

You’ve built tables, written queries, and maybe even discovered that your database will happily accept a negative price, a future birth date, or an email address that looks like "not-an-email". Without layer‑of‑defense validation, bad data creeps in through application bugs, manual SQL, and overlooked edge cases. In this lesson, you’ll learn to validate data with check constraints in PostgreSQL — the database‑level guard that rejects invalid rows before they ever reach your tables. By the end, you’ll be able to define business rules directly in your schema, keep your data clean, and sleep better at night.

The problem this lesson solves

Every application that writes to a database eventually faces the same threat: invalid data. A stray NULL, a negative quantity, a string that doesn’t match a pattern — each one can break reports, crash downstream systems, or silently corrupt your analytics. You could handle validation in your application’s code, but that’s fragile: a new developer might forget to check, a direct SQL update from a DBA could bypass the ORM, and every microservice that writes to the same table would need its own copy of the rules.

Check constraints solve this by moving validation into the database itself. They are declarative rules that PostgreSQL enforces on every INSERT or UPDATE. If a row violates the rule, the database rejects the change with an error — no matter which client or tool made the attempt. This is your last line of defense for data integrity.

Core concept / mental model

Think of a check constraint as a gatekeeper at the entrance to your table. Before a row is allowed in, the gatekeeper evaluates a Boolean expression. If the expression evaluates to true (or NULL, for reasons we’ll cover later), the row passes. If it evaluates to false, the row is turned away with an error.

In SQL terms, a check constraint is a constraint that you attach to a table, either when you create the table or later with ALTER TABLE. It can reference one or more columns, and you can use any expression that returns a Boolean — comparisons, BETWEEN, IN, LIKE, and even functions.

Key definition: A check constraint is a rule that must hold for every row in a table. It’s evaluated on each write and, if violated, aborts the operation.

Diagram in words:

INSERT/UPDATE
    │
    ▼
┌─────────────┐
│  CHECK rule │→ if TRUE/NULL → row accepted
│  expression  │→ if FALSE    → ERROR → row rejected
└─────────────┘

How it works step by step

1. Decide on a business rule. Example: a products table must never have a negative price.
2. Write a Boolean expression that the data must satisfy, e.g. price >= 0.
3. Add the constraint either inline in a CREATE TABLE or with ALTER TABLE ... ADD CONSTRAINT.
4. PostgreSQL evaluates the expression for each new or updated row.
5. If the expression is false, the statement fails with a clear error message.
6. If the expression is true (or evaluates to NULL), the row is accepted.

The NULL nuance is crucial: in SQL, any comparison with NULL yields NULL, not true or false. A check constraint only rejects rows where the expression is false. So if the expression is price >= 0 and price is NULL, the expression is NULL, and the row is accepted. If you want to reject NULL values, add an explicit NOT NULL constraint or combine it in the check.

Hands-on walkthrough

Let’s put this into practice. We’ll create a users table that requires a valid email format and a products table that only allows positive prices.

Example 1: Create a table with a check constraint

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    price NUMERIC(10,2) CHECK (price >= 0)
);

Now try inserting a negative price:

INSERT INTO products (name, price) VALUES ('Broken item', -5.00);

Expected output:

ERROR:  new row for relation "products" violates check constraint "products_price_check"
DETAIL:  Failing row contains (1, Broken item, -5.00).

The insert is rejected — your data is safe.

Example 2: Add a named constraint later

You can give your constraint a descriptive name for easier debugging:

ALTER TABLE products
    ADD CONSTRAINT price_non_negative CHECK (price >= 0);

This is identical to the inline version but keeps the name price_non_negative, which appears in error messages instead of the auto‑generated name.

Example 3: Combine multiple columns

Check constraints can reference multiple columns. For example, ensure an end date is always after a start date:

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    starts_at TIMESTAMP NOT NULL,
    ends_at TIMESTAMP NOT NULL,
    CONSTRAINT valid_period CHECK (ends_at > starts_at)
);

Example 4: Validate email format with a pattern

Use a LIKE or regex pattern. PostgreSQL supports ~ for regex matching:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email TEXT NOT NULL,
    CONSTRAINT valid_email CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
);

This rejects obvious non‑emails like 'not-an-email'.

Compare options / when to choose what

When to choose check constraints: For simple, single‑table business rules — like positive prices, valid dates, status enums — they are the simplest, fastest, and most reliable option. Use triggers for cross‑table validations, and keep application validation as a friendly UX layer, but always back it up with database constraints.

Troubleshooting & edge cases

• Check constraint is ignored for NULL values. If you have CHECK (price >= 0) and a row has price = NULL, it passes because NULL >= 0 is NULL, not false. To reject NULL, add NOT NULL or use CHECK (price IS NOT NULL AND price >= 0).
• Error message is cryptic. PostgreSQL’s default error will tell you the constraint name but not what went wrong. Name your constraints descriptively and consider adding CONSTRAINT constraint_name in the definition.
• You can’t reference other tables in a check constraint. If you need to validate against values in another table, use a trigger or a foreign key instead.
• Adding a check constraint to a large table can take a long time, as PostgreSQL scans all existing rows to verify they satisfy the rule. Consider adding it during off‑peak hours or using NOT VALID with a later VALIDATE CONSTRAINT to avoid locking the table for too long.
• Altering a check constraint isn’t directly supported — you have to drop and re‑add it. For example, to change the condition, use ALTER TABLE ... DROP CONSTRAINT ... then ADD CONSTRAINT ....

What you learned & what's next

You now know how to validate data with check constraints in PostgreSQL: how to define them, what they do, and their limitations. You learned that they are a powerful tool for enforcing business rules at the database level, protecting your data from invalid entries no matter what client or tool writes to the table — a foundational step toward robust data integrity.

Next step: In the next lesson, you’ll build on this by exploring constraint validation and triggers, where you’ll learn how to enforce more complex cross‑table rules and automate data corrections. This will give you an even more complete toolkit for keeping your PostgreSQL data clean and reliable.