Database Constraints: The Defense Layer We Often Forget

By /

When building applications, we spend a lot of time validating data in the application layer — checks in APIs, services, ORMs, and business logic. But there is another defense layer that is often overlooked: database constraints. Constraints are not just schema decorations. They are one of the last lines of defense protecting data integrity when application logic, concurrency, or unexpected behavior goes wrong.

1. Preventing negative balances

Imagine a wallet system with a balance of $100. Two requests arrive almost at the same time:

Request A: withdraw $80
Request B: withdraw $50

Both requests read:

balance = 100

Both may pass application validation before either transaction finishes. Depending on how the update is implemented, concurrency bugs can result in an invalid balance. A database constraint provides another protection layer:

CHECK (balance >= 0)

Even if the application makes a mistake, the database refuses to store an impossible state. The application asks "is this operation valid?" The database constraint says "this state must never exist." That is an important difference.

2. UNIQUE constraints in high-frequency systems

Duplicate data is another common example. Consider a payment processing system receiving thousands of requests. Each payment has an external transaction ID, e.g. TX-2026-000123. The application might check:

SELECT id
FROM payments
WHERE transaction_id = 'TX-2026-000123';

If nothing exists, it inserts the payment. But two requests can execute the check at almost exactly the same time:

Request A -> SELECT -> not found
Request B -> SELECT -> not found

Request A -> INSERT
Request B -> INSERT

Now the same payment exists twice. Application-level checking alone cannot guarantee uniqueness under concurrency. A simple constraint can:

UNIQUE (transaction_id)

Now the database becomes the final authority. One transaction succeeds, the other fails, and the database stays consistent.

3. Foreign keys prevent orphaned data

Suppose we have customers and orders tables, where an order references a customer via orders.customer_id. Without a foreign key, nothing technically prevents this:

order.customer_id = 999999

even if customer 999999 does not exist. Over time, these errors create orphaned records that are difficult to detect and clean up. A foreign key:

FOREIGN KEY (customer_id)
REFERENCES customers(id)

turns an assumption into an enforced rule.

4. CHECK constraints protect business invariants

Many business rules can be represented directly in the database. For example, CHECK (quantity > 0) prevents quantity = -5. For an order, CHECK (total_amount >= 0). For percentages, CHECK (discount_percent BETWEEN 0 AND 100). For date ranges, CHECK (end_date >= start_date). These constraints are inexpensive compared with discovering corrupted data months later.

5. NOT NULL is more important than it looks

Developers sometimes make columns nullable simply because it is convenient during development. But NULL introduces another possible state into the system. Instead of ACTIVE / INACTIVE, you may suddenly have ACTIVE, INACTIVE, and NULL. Every query and every piece of application logic now needs to understand what NULL means. If a value is required by the domain, say so:

status VARCHAR(20) NOT NULL

A good schema reduces the number of invalid states the application needs to handle.

6. Constraints protect against more than application bugs

Application validation is still important, but databases are often accessed from multiple places: web applications, REST APIs, background jobs, ETL processes, migration scripts, admin tools, a SQL console, third-party integrations. If validation exists only inside one service, another path can bypass it. Database constraints protect the data regardless of where the write originates — that makes them especially valuable in large systems.

7. Constraints and transactions work together

Constraints become even more powerful when combined with transactions, proper isolation, and atomic updates. Instead of reading the balance, checking it, calculating a new value, then writing it back, an application can execute an atomic statement:

UPDATE accounts
SET balance = balance - 50
WHERE id = 123
  AND balance >= 50;

Together with CHECK (balance >= 0), you now have multiple defensive layers:

Application validation
        ↓
Atomic SQL operation
        ↓
Transaction / locking
        ↓
Database constraint
        ↓
Valid database state

This is defense in depth.

Constraints are not a replacement for application validation

This does not mean all business logic should be moved into the database. Application validation is useful because it provides better user experience and clearer error messages — the application can say "insufficient balance" instead of exposing a raw check-constraint violation. Application validation and database constraints solve different problems: application validation protects the user experience, database constraints protect the data. Good systems usually need both.

Final thought

A database should not simply store whatever the application sends to it. It should define what valid data is allowed to exist. Constraints such as PRIMARY KEY, UNIQUE, FOREIGN KEY, NOT NULL, and CHECK may look simple, but they provide a surprisingly powerful protection layer. Especially in high-frequency and concurrent systems, a single UNIQUE (...) or CHECK (...) can prevent an entire class of production bugs.

Your application logic may fail someday. Your database schema should be ready when it does.

Constraint support and syntax also vary by database engine, which matters the moment you migrate or keep two environments in sync. A schema comparison will show you exactly which keys, checks, and constraints differ between source and target before you write a single line of migration SQL.

Scroll to Top