Data Integrity Constraints as Code

In this lesson, you will learn how to shift data integrity from application-level logic into the database schema by defining constraints as version-controlled code within your migration lifecycle.

From Application Logic to Schema Definition

While ORMs often provide validation helpers, relying solely on application-level checks leaves your database vulnerable to "dirty data" from manual scripts, legacy systems, or direct SQL access. By defining FOREIGN KEY, CHECK, and UNIQUE constraints as part of your migration files, you ensure the database serves as the ultimate source of truth for business rules.

When these constraints are treated as code, they become immutable parts of your deployment pipeline. If a migration defines a CHECK constraint, it is evaluated by the database engine at the time of insertion, providing a guarantee that no application bug can bypass.

Implementing Constraints in Migrations

Consider a requirement where a user_balance must never be negative. Instead of just validating this in your Repository, define it in your migration:

-- Migration file: 202310271200_add_balance_constraints.sql
ALTER TABLE accounts 
ADD CONSTRAINT chk_positive_balance 
CHECK (balance >= 0);

ALTER TABLE profiles 
ADD CONSTRAINT uq_user_email 
UNIQUE (email);

ALTER TABLE orders 
ADD CONSTRAINT fk_order_user 
FOREIGN KEY (user_id) REFERENCES users(id) 
ON DELETE CASCADE;

Why this matters

By treating constraints as code within your migration lifecycle, you achieve three critical outcomes:

1. Atomicity: Constraints are applied or rolled back alongside your schema changes, keeping the state of the database and the migration history perfectly in sync.
2. Declarative Reliability: You move from imperative validation (writing code to check conditions) to declarative enforcement (telling the database what the data must look like).
3. Auditability: Every constraint becomes visible in your version control history. You can trace when a business rule was added, who requested it, and why, just like any other feature.

When constraints exist in your code, they are no longer just "rules in your head" or "logic in your services"—they are hard, observable boundaries built into the structure of your data.

Key Takeaway: Moving constraints into your migration scripts transforms data integrity from a volatile application-layer assumption into a permanent, versioned foundation of your database schema.