I was staring at a flickering terminal at 3:00 AM, trying to figure out why a payment record had a null user ID in a system that—according to the Confluence page—forbade it. The documentation was a masterpiece of high-level diagrams and “intended” workflows, but it said absolutely nothing about the reality of the data. That’s the problem with most teams: they treat their schema like a private secret rather than the source of truth. We spend weeks polishing READMEs that go out of date the moment a PR is merged, completely ignoring the fact that using database constraints as documentation is the only way to ensure the rules actually stay enforced when the code inevitably fails.
I’m not here to give you a lecture on relational theory or a sanitized tutorial on how to write a perfect `CREATE TABLE` statement. Instead, I want to show you what happens when you rely on application logic to guard your gates and everything falls apart. I’m going to walk you through the messy, unglamorous reality of using constraints to build a self-documenting system that survives even the most junior developer’s midnight hotfix.
Table of Contents
Why Self Documenting Database Schemas Never Get Outdated

The problem with a Confluence page or a README is that it requires someone to actually care enough to update it. In my experience, that person is usually me, three years from now, trying to figure out why a service is throwing a 500 error. Documentation lives in a separate universe from the code; it’s a suggestion, not a law. But self-documenting database schemas don’t suffer from human forgetfulness. When you bake your logic into the schema via foreign keys, check constraints, and non-null requirements, the database becomes the single source of truth that refuses to drift.
I’ve spent enough nights fixing corrupted tables to know that enforcing business rules in SQL is the only way to ensure the data stays sane. If a business rule says an order cannot exist without a customer, a `NOT NULL` constraint and a foreign key aren’t just technical settings—they are the living documentation of that rule. You don’t need to read a manual to understand the relationship; the schema tells you exactly what is allowed and, more importantly, what isn’t. It’s the difference between a polite request and an absolute truth.
Enforcing Business Rules in Sql Before They Break Everything

Most tutorials suggest you handle your logic in the application layer. They tell you to write a clean service class, validate the input, and then persist it. This works perfectly in a local dev environment with a single instance of your service. But I’ve spent too many late nights debugging “impossible” states in production caused by a rogue script, a direct manual patch by a panicked DBA, or a secondary microservice that didn’t quite follow the same validation rules.
When you rely solely on code, your business logic is a suggestion; when you use enforcing business rules in SQL, it becomes a law. If a transaction must never result in a negative balance, a `CHECK` constraint is your final line of defense. It doesn’t matter if a junior dev bypasses the API or if a migration script goes sideways—the database will reject the write. This approach to declarative data validation ensures that your invariants remain intact regardless of how many different ways people try to touch your data. It’s not about being pedantic; it’s about making sure the system remains sane when the code inevitably fails to catch the edge case.
Five ways to stop lying to your future self
- Stop using “optional” columns for things that are actually required. If a field isn’t NULL, put a NOT NULL constraint on it. A comment in a README saying “this field is required” is just a promise that will be broken by the next junior dev or a rushed migration.
- Use Check Constraints to define what “valid” actually means. If a transaction amount can never be negative, `amount > 0` belongs in the schema, not in a layer of Python code that someone might bypass with a direct SQL script.
- Let Foreign Keys do the heavy lifting for your data relationships. When I look at a schema, I shouldn’t have to hunt through service layers to figure out how Table A connects to Table B. The constraints should tell me the story of the data’s lifecycle.
- Use Unique Constraints to prevent the “ghost duplicate” problem. Documentation often describes a user as “unique,” but without a unique index, your database will eventually host three different records for the same email address, and your reconciliation scripts will scream.
- Treat your migrations as the source of truth, not your wiki. If a business rule changes, update the constraint in the migration. If the constraint is in the code but not the database, you don’t have a rule; you have a suggestion.
The Hard Lessons Learned in Production
Stop treating your schema like a passive storage bin; if a business rule isn’t enforced at the database level, it’s just a suggestion that a junior dev or a rogue script will eventually ignore.
Documentation that lives in a Confluence page is a lie waiting to happen, but a `CHECK` constraint is a living, breathing truth that refuses to let your data drift into nonsense.
When the system eventually fails—and it will—you don’t want to be debugging a “ghost in the machine” caused by bad application logic; you want the database to throw a loud, clear error that tells you exactly which rule was violated.
The Cost of a Clean README
“A README tells you what the code is supposed to do, but a foreign key constraint tells you what the data is actually allowed to be. I’ve spent too many weekends fixing ‘ghost records’ that a simple CHECK constraint would have killed at the gate.”
Pernille Askeland
The Truth is in the Schema

At the end of the day, your Confluence pages and README files are just polite suggestions. They describe how the system should behave, but they don’t actually stop a rogue script or a junior dev’s rushed migration from corrupting your state. Constraints are different. They are the only form of documentation that is actually enforced by the engine itself. When you define a check constraint or a strict foreign key, you aren’t just writing a note for the next developer; you are building a hard boundary that prevents the “impossible” state from ever existing in your tables. I’ve spent too many nights untangling data that bypassed application-level validation because someone thought the code was enough.
Stop treating your database like a passive bucket of bits and start treating it like the authoritative source of truth it needs to be. Writing robust constraints might feel like extra friction during a sprint, but that friction is exactly what prevents the catastrophic midnight page. When you commit that schema change, you aren’t just adding a rule; you are leaving a legacy of stability for whoever has to maintain this mess in five years. Build it so that even when the application layer fails—and it will—the data remains unshakeable.
Frequently Asked Questions
How do I handle constraints in a legacy system where a single migration might lock a massive, high-traffic table for hours?
You don’t run a standard `ALTER TABLE`. In a high-traffic legacy environment, that’s a suicide mission. You have to do it in stages. First, add the constraint as `NOT VALID`. This tells the engine to enforce it for new writes without scanning the entire existing dataset. Once that’s live and the lock is released, you run a separate `VALIDATE CONSTRAINT` command. It takes longer, but it avoids the catastrophic table lock that kills your uptime.
At what point does a complex CHECK constraint become a maintenance nightmare that's harder to read than the application code it's replacing?
The moment you start querying other tables or pulling in external logic via UDFs, you’ve crossed the line. A `CHECK` constraint is meant to be a local truth—a simple rule about a single row. Once it starts acting like a mini-service, you’re in trouble. You’ll spend three hours debugging a failed transaction only to realize a constraint is failing because of a side effect in a completely different module. Keep it stateless. Keep it simple.
If I move business logic into the database layer, how do I prevent my unit tests from becoming a mess of database-level error assertions?
The trick is to stop trying to unit test the database. If you’re asserting against specific SQL error codes, your tests are already brittle. Instead, treat the database as a black box in your integration layer. Wrap those constraint violations in custom domain exceptions within your repository pattern. Your unit tests should check that your service layer handles a `PaymentValidationError`, not that Postgres threw error `23503`. Test the behavior, not the syntax.