Lorixen.
EN

Check constraints: let the database refuse impossible data

Short answer: A CHECK constraint is a rule stored on the table, such as "price must be greater than zero" or "end date can't be before start date", that the database enforces on every insert and update, whichever code or tool is writing. App validation still matters for friendly error messages, but it only protects the paths that use it; imports, admin scripts, new services and bugs slip past. Add named check constraints for the simple rules your business depends on, find and fix existing bad rows first, and on large tables add them with NOT VALID and validate afterwards to avoid long locks.

Why app validation isn't enough

Most apps validate forms: the price field must be a positive number, the status must come from a dropdown. But data reaches your tables by other routes:

  • a CSV import script written in a hurry
  • a support person fixing an order directly in the database
  • a second service, or a new API endpoint, that forgets one rule
  • a bug in a background job

Each route needs to remember every rule. A constraint in the database doesn't rely on anyone remembering.

What check constraints look like

PostgreSQL's documentation calls the check constraint the most generic type: the value must satisfy a true-or-false expression. Its examples include requiring positive prices:

price numeric CONSTRAINT positive_price CHECK (price > 0)

and rules across several columns, written as table constraints:

CONSTRAINT valid_discount CHECK (price > discounted_price)

The docs recommend naming constraints, because names make error messages clearer and let you refer to the constraint when changing it.

Rules worth enforcing

Good candidates are simple, permanent facts about your business data:

  • prices, quantities and stock levels are not negative
  • a booking's end is after its start
  • a discount percentage is between 0 and 100
  • a status is one of a known list, such as pending, paid, shipped, cancelled
  • a refund amount isn't more than the order total
  • either an email or a phone number is present for a contact

Rules that change often, like "minimum order is £20 this month", belong in application settings, not constraints. A useful test: if the rule would still be true in five years, it's a candidate for the database.

Mind NULLs

The documentation points out a subtle detail: a check constraint is satisfied if its expression is true or null. Since most expressions involving a null give null, a check doesn't stop missing values. If a column must have a value, add NOT NULL as well.

Know what checks can't do

PostgreSQL's documentation is explicit that check constraints shouldn't refer to other rows or other tables. Such a check might seem to work in tests, but it can't guarantee consistency when the other rows change, and it can break restoring a backup. For rules involving other rows, use the right tool:

  • UNIQUE for "only one active subscription per customer"
  • FOREIGN KEY for "the order's customer must exist"
  • EXCLUDE for "no overlapping bookings for the same room"
  • a trigger for one-off checks at insert time

The docs also note that check constraints are assumed to always give the same result for the same row. Don't put a function in a check whose behaviour you might change later.

Adding constraints to an existing table

Two steps make this safe.

First, find violations. Run a query for rows breaking the rule, for example SELECT id FROM products WHERE price <= 0;. Fix them with whoever owns the data. Otherwise adding the constraint fails.

Second, consider locking. PostgreSQL's ALTER TABLE reference explains that adding a check constraint scans the table to verify existing rows, and on a large table that can take a while, with other updates blocked until it finishes. The NOT VALID option adds the constraint without scanning, enforcing it for new and updated rows immediately. You can then run VALIDATE CONSTRAINT later to check the existing rows, with less impact on traffic.

Test the constraints

Add a few automated tests that try to write invalid rows directly and confirm the database rejects them, one per rule. They document the rules for the next developer and catch the day someone drops a constraint during a migration. Run the same tests against a copy of production before major releases, since that's where unexpected data tends to live.

Map errors to helpful messages

When a constraint rejects a write, your app receives an error naming the constraint. Catch it and show a meaningful message, such as "Discounted price must be lower than the regular price", rather than a generic error. Named constraints make this mapping straightforward. Log these errors too: they often reveal an import or integration sending bad data.

Other databases

Most current relational databases support check constraints, though details vary, and some older versions accepted the syntax without enforcing it. Test that your database actually rejects a violating row before relying on it.

A worked example

An illustrative Manchester furniture retailer's stock system kept producing negative stock and orders with discounts above 100%. The web forms validated correctly, but a nightly supplier import and a staff bulk-edit tool didn't. The developer listed the core rules, cleaned up existing bad rows, and added named check constraints for stock, discounts and order statuses, using NOT VALID and a later validation on the large order lines table. The import job now fails loudly on bad rows instead of silently corrupting stock.

FAQ

Do constraints slow down writes?

Simple checks add negligible overhead compared with the cost of bad data.

Should I remove app validation?

No. Keep it for user-friendly messages; the constraint is the safety net.

Can I change a constraint later?

Yes. Drop and re-add it, ideally with the same two-step approach on big tables.

How do I handle status lists that grow?

Update the constraint when adding a status, or use a lookup table with a foreign key.

If bad data keeps slipping into your app's database, tell us where it comes from and we'll help you decide which rules the database should enforce.

Sources

WhatsAppBook a call