Constraint Enforcement: Database vs Application Level

Database constraints define what data is allowed in your tables. Simple enough. But here’s where teams run into trouble: deciding where to enforce these rules. The database? Your application code? Both? The answer matters more than most developers realize until something goes wrong.

I’ll walk through each constraint type, explain when the database should be doing the enforcing, and show you when application-level checks actually make sense.

flowchart TD
    subgraph AppLayer["Application Layer"]
        A1[("Validate:<br/>check not null")]
        A2[("Validate:<br/>check unique")]
        A3[("Validate:<br/>check business rules")]
    end

    subgraph DBEnforce["Database Layer"]
        D1[("NOT NULL<br/>Constraint")]
        D2[("UNIQUE<br/>Index")]
        D3[("CHECK<br/>Constraint")]
        D4[("FOREIGN KEY<br/>Constraint")]
        D5[("EXCLUSION<br/>Constraint")]
    end

    subgraph WritePath["Write Path"]
        W1[("INSERT/UPDATE<br/>Request")] --> A1
        A1 -->|pass| A2
        A2 -->|pass| A3
        A3 -->|pass| Write[("Write to<br/>Database")]

        Write --> D1
        D1 --> D2
        D2 --> D3
        D3 --> D4
        D4 --> D5
        D5 --> Committed[("Data<br/>Committed")]
    end

    Write -.->|app bypass<br/>direct SQL| D1
    Write -.->|bypass possible<br/>without app| D1

    A1 -.->|redundant<br/>if DB enforces| D1
    A3 -.->|only here when<br/>DB cannot express| D3

Understanding Constraint Types

NOT NULL Constraints

The simplest constraint type, NOT NULL ensures a column always contains a value. At the database level, this is straightforward:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

The database enforces this rule regardless of how data enters the system—direct SQL inserts, application code, ETL processes, or any other pathway. This is the primary advantage of database-level enforcement: it provides a single source of truth that cannot be bypassed.

Application-level NOT NULL checks are typically redundant and slower. They require fetching data, checking values, and returning errors—all slower than the database handling it directly.

UNIQUE Constraints

UNIQUE constraints prevent duplicate values in a column or combination of columns:

CREATE TABLE organizations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE,
    domain VARCHAR(255) UNIQUE
);

The database maintains a unique index to enforce this constraint efficiently. Concurrent inserts attempting duplicate values will fail with a constraint violation, and the database handles race conditions automatically.

At the application level, you’d need to first query for existing values, then insert—but this creates a window where another process could insert the same value first. Database-level enforcement eliminates this race condition entirely.

CHECK Constraints

CHECK constraints validate data against a boolean expression:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id),
    total_amount DECIMAL(10,2) NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT positive_amount CHECK (total_amount > 0),
    CONSTRAINT valid_status CHECK (status IN ('pending', 'processing', 'shipped', 'delivered'))
);

Modern databases support sophisticated CHECK constraints. PostgreSQL evaluates them with table and row data:

ALTER TABLE orders ADD CONSTRAINT no_future_orders
    CHECK (created_at <= CURRENT_TIMESTAMP);

CHECK constraints are evaluated on a per-row basis during INSERT and UPDATE operations. The database guarantees atomic enforcement.

Exclusion Constraints

PostgreSQL’s exclusion constraints go beyond UNIQUE by ensuring that specified ranges or values do not overlap:

CREATE TABLE room_bookings (
    id SERIAL PRIMARY KEY,
    room_id INTEGER NOT NULL,
    booking_range TSTZRANGE NOT NULL,
    EXCLUDE USING GIST (room_id WITH =, booking_range WITH &&)
);

This prevents double-booking by ensuring no two bookings for the same room have overlapping time ranges. The && operator checks for range overlap.

Exclusion constraints are powerful for scheduling, reservation systems, and any scenario where temporal or spatial conflicts must be prevented.

Database-Level Enforcement: When It Makes Sense

Database constraints should be your first line of defense when:

1. Data enters through multiple pathways — If data can be inserted via direct SQL, migrations, ETL jobs, admin tools, or any path besides your application, database enforcement ensures consistency everywhere.

2. You need atomic, race-condition-free validation — The database handles concurrent operations without race windows that plague application-level checks.

3. Referential integrity is critical — FOREIGN KEY constraints are non-negotiable at the database level. Application-level referential checks are always inferior.

4. You want declarative, self-documenting schema — Constraints in the schema serve as documentation. They make the data model’s rules explicit and impossible to ignore.

5. You’re using an ORM or generic data access layer — ORMs often bypass application-level validation. Database constraints protect your data regardless of how it’s accessed.

Here’s the way I think about it: your application’s validation logic is a ceiling. Constraints are a floor. Application logic can have bugs, be bypassed, or be forgotten. Database constraints are always there.

Application-Level Enforcement: When to Use It

Application-level validation makes sense in specific scenarios:

1. Complex business rules that exceed constraint capabilities — Some rules require cross-table logic, external API calls, or stateful validation that CHECK constraints cannot express.

2. User experience optimization — Catching validation errors at the application layer lets you format user-friendly error messages, provide field-level feedback, and avoid database round-trips for simple checks.

3. Performance-critical paths — For bulk operations where every microsecond matters, application-level pre-validation can reduce transaction overhead. The trade-off is losing atomic enforcement.

4. Business logic that might change — If validation rules are configuration rather than data law, keeping them in application code allows faster iteration without schema migrations.

# Application-level validation example
def create_order(user_id: int, items: list[OrderItem]) -> Order:
    # Pre-validate business rules
    if not items:
        raise ValidationError("Order must have at least one item")

    total = sum(item.price * item.quantity for item in items)
    if total > user.credit_limit:
        raise ValidationError("Order exceeds credit limit")

    # Now create the order
    return Order.create(user_id=user_id, total_amount=total, items=items)

The rule of thumb: use database constraints for data integrity rules (the laws of your data universe) and application validation for business rules (the policies of your business domain).

Performance Implications

Constraint enforcement has measurable performance costs. Understanding these helps you make informed decisions.

NOT NULL Performance

Performance impact: Minimal. NULL checks are essentially free—they’re part of column storage and indexing decisions anyway.

UNIQUE Constraint Performance

Performance impact: Moderate. Each UNIQUE constraint requires maintaining an index. Insert and update operations must check and update this index.

For single-column UNIQUE constraints on auto-incrementing IDs, the overhead is negligible. For composite UNIQUE constraints on frequently-updated columns, consider the index size and write amplification.

-- Monitoring unique constraint index size
SELECT indexname, pg_size_pretty(pg_relation_size(indexname::regclass))
FROM pg_indexes
WHERE tablename = 'your_table';

CHECK Constraint Performance

Performance impact: Variable. Simple comparisons (value > 0, status IN (‘a’, ‘b’)) are optimized by query planners and add minimal overhead. Complex expressions involving functions, subqueries, or cross-column comparisons can slow inserts and updates noticeably.

-- Complex CHECK constraints can slow bulk inserts
ALTER TABLE large_table ADD CONSTRAINT complex_check
    CHECK (some_function(column_a, column_b) < threshold);

Test your constraints with realistic data volumes. A CHECK that seems innocuous can become a bottleneck during bulk operations.

EXCLUSION Constraint Performance

Performance impact: Moderate to significant. GIST indexes supporting exclusion constraints are larger and slower to update than B-tree indexes. For high-volume write workloads, benchmark before deploying.

Best Practices

1. Start with database constraints, optimize later — Default to database-level enforcement. Move validation to the application only when you have measurable evidence that constraints are a bottleneck.

2. Use constraint naming conventions — Named constraints make error messages and debugging easier:

   CONSTRAINT positive_price CHECK (price >= 0)  -- Good
   CHECK (price >= 0)  -- Poor - cryptic error messages

3. Defer constraint checking when needed — PostgreSQL’s DEFERRABLE option delays constraint validation until transaction end:

   CREATE TABLE self_referencing (
       id SERIAL PRIMARY KEY,
       parent_id INTEGER REFERENCES self_referencing(id) DEFERRABLE INITIALLY DEFERRED
   );

4. Combine approaches strategically — Use database constraints for integrity and application validation for UX. Don’t double-check everything, but don’t rely solely on one layer.

5. Document your constraint strategy — Whether in README files, ADR documents, or schema comments, explain the reasoning behind your enforcement decisions.

Database vs Application Enforcement Trade-offs

Dimension	Database Constraints	Application Validation
Enforcement guarantee	Strong — cannot be bypassed	Weak — bugs, bypasses possible
Race condition handling	Atomic — handled by DB	Complex — needs explicit locking
Performance impact	Minimal to moderate	None on DB side
Multiple data pathways	Covers all — SQL, ETL, migrations	Only covers application writes
Debugging visibility	Constraint violation messages	Custom error handling
Schema documentation	Declarative in schema	Scattered in code
Maintenance burden	Low — schema-level	High — code must stay in sync

Production Failure Scenarios

Failure	Impact	Mitigation
Race window in app-level uniqueness check	Duplicate key inserted despite check	Always use DB UNIQUE constraint as source of truth
Deferred constraint triggering on commit	Violation caught late — full transaction rolls back	Test with realistic multi-statement transactions
CHECK constraint with complex function	Slows bulk inserts significantly	Benchmark complex CHECKs, move logic to triggers if needed
Foreign key without index	Lock contention on parent table during deletes	Always index foreign key columns
Exclusion constraint on high-write table	GiST index updates create contention	Use before/after benchmarking, consider application-level exclusion

Capacity Estimation: Constraint Check Overhead Per Row

Constraint checking adds CPU overhead per row. The cost depends on the constraint type and expression complexity.

Per-row constraint cost formula:

constraint_check_time = expression_evaluation_time × rows_affected × constraint_complexity_factor

Simple constraints (NOT NULL, simple comparisons): negligible overhead, typically 1-5% of insert/update time. Complex CHECK constraints with functions, regex, or subqueries: can add 10-50% overhead.

For NOT NULL: essentially zero overhead — the null check is a single bit test. For CHECK (price > 0 AND price < 1000000): a few CPU cycles per row. For CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'): regex evaluation per row, 10-50 microseconds per row depending on regex complexity and string length.

UNIQUE constraint cost: B-tree lookup on the index. For 1M-row tables, unique check adds ~0.1-0.5ms per insert. For high-volume inserts (10K/second), this is manageable. For bulk inserts of 100M rows, the unique check against existing data becomes the bottleneck — consider using UNIQUE only where needed and using batch deduplication logic at the application layer instead.

EXCLUSION constraint cost: GiST index update is the dominant cost. For range exclusions (prevent overlapping bookings) or btree exclusion (prevent double-booking at the same timestamp), GiST index updates are 5-10× slower than btree inserts. On high-write tables (10K+ writes/second), exclusion constraints cause measurable contention. Benchmark before deploying on write-heavy tables.

Observability Hooks: Constraint Violation Logging

PostgreSQL does not log constraint violations by default. You need application-level or trigger-based logging to track violations.

-- Create a constraint violation log table
CREATE TABLE constraint_violations (
    id BIGSERIAL PRIMARY KEY,
    occurred_at TIMESTAMPTZ DEFAULT now(),
    constraint_name TEXT,
    table_name TEXT,
    schema_name TEXT,
    constraint_type TEXT,
    key_values JSONB,
    violation_query TEXT,
    application_name TEXT
);

-- Trigger to log unique constraint violations
CREATE OR REPLACE FUNCTION log_unique_violation()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO constraint_violations (
        constraint_name, table_name, schema_name, constraint_type, key_values, violation_query
    )
    SELECT
        'unique_' || TG_NAME,
        TG_TABLE_NAME,
        TG_TABLE_SCHEMA,
        'UNIQUE',
        to_jsonb(NEW),
        current_query()
    WHERE FALSE;  -- This will fail, but the error contains the info
    RETURN NEW;
EXCEPTION WHEN raise_exception THEN
    GET STACKED DIAGNOSTICS
        v_msg = MESSAGE_TEXT,
        v_constraint = CONSTRAINT_NAME;
    RAISE;
END;
$$ LANGUAGE plpgsql;

Key alerts for constraint violations:

# Alert on unexpected constraint violation spike
- alert: ConstraintViolationSpike
  expr: rate(constraint_violations_total[5m]) > 10
  for: 5m
  labels:
    severity: warning
  annotations:
    summary: "Spike in constraint violations on {{ $labels.table }}"

# Alert on unique constraint violations (duplicate key attempts)
- alert: DuplicateKeyAttempts
  expr: increase(postgresql_constraint_violations_total{type="unique"}[5m]) > 100
  for: 5m
  labels:
    severity: warning
  annotations:
    summary: "High duplicate key attempts on {{ $labels.table }}"

Real-World Case Study: Constraint Bypass at a Fintech Startup

A fintech company processing payment transactions had a PostgreSQL database with a UNIQUE constraint on (account_id, transaction_date) to prevent duplicate daily transactions. The application code checked for duplicates before insert — fast path — but direct SQL imports (for data corrections) bypassed the application layer and hit the database directly.

The problem: the direct SQL imports used INSERT ... ON CONFLICT DO NOTHING which silently dropped duplicate transactions instead of flagging them. The correction workflow expected these to be flagged, reviewed, and approved. Instead, duplicate transactions worth thousands of dollars were silently dropped.

The resolution: the company implemented a database trigger that logged all constraint violations to a constraint_violations table, regardless of whether the application handled the conflict. The trigger also sent a Slack alert for high-value transactions where a unique constraint was violated. This caught the next direct SQL import incident within seconds and prevented the duplicate from being silently dropped.

The lesson: if you have application-layer bypass paths (direct SQL, bulk imports, data corrections), those paths also need to respect constraint enforcement logic. If they cannot, implement constraint violation logging so violations are never silent.

Interview Questions

Q: A CHECK constraint on your orders table references a function that calls an external API. Insert performance degrades 100×. What do you do?

External API calls in CHECK constraints are an anti-pattern — they turn a per-row database operation into a per-row network call, which is orders of magnitude slower. The fix: remove the API call from the constraint. Instead, validate the external condition at the application layer before inserting, and use the database constraint only for data integrity that can be evaluated locally. If the external condition must be enforced at the database level, consider using a trigger that validates and rejects, with the API call handled asynchronously — accept the insert, validate in background, and flag/reject if validation fails.

Q: Your UNIQUE constraint is causing deadlocks under high concurrent insert load. How do you diagnose and fix it?

UNIQUE constraint deadlocks occur when multiple transactions try to insert conflicting keys simultaneously. With 10 transactions all inserting user_id = 123 at the same time, they queue at the unique index — but if they hold locks while waiting, they can deadlock with each other. Diagnosis: enable log_lock_waits and deadlock_timeout monitoring in PostgreSQL. The fix depends on pattern: if duplicates are rare (legitimate new users), the contention is transient. If your workload generates many legitimate duplicates that get rejected, consider switching to INSERT ... ON CONFLICT DO UPDATE which serializes conflicts differently, or partition by a shard key to reduce index contention.

Q: What is the difference between DEFERRABLE and NOT DEFERRABLE constraints?

NOT DEFERRABLE constraints are checked immediately after each statement. DEFERRABLE constraints can be deferred until the end of the transaction. This matters for bulk imports where you insert rows in an order that temporarily violates a foreign key but satisfies it by the end. Example: inserting an order before its customer. With NOT DEFERRABLE, the FK check fails immediately. With DEFERRABLE INITIALLY DEFERRED, the FK is checked only at transaction commit. Performance impact: NOT DEFERRABLE checks after each row for statement-level cascades; DEFERRABLE allows batching the check at commit — faster for multi-row operations with interdependent data.

Q: A complex CHECK constraint on a high-write table causes 40% insert overhead. How do you decide whether to keep it or move the validation to the application layer?

Benchmark both approaches: measure the actual overhead of the CHECK constraint under realistic write load. If 40% overhead is confirmed, the decision depends on who you trust more: the database or the application. Database constraints are enforced regardless of which application code path writes the data — including direct SQL, bulk imports, ORMs, and future applications. Application validation is only as reliable as the discipline of all code paths. The rule: use database constraints for data integrity that must never be violated, even by buggy code. Use application validation for business logic rules that belong in the application layer.

---

Common Pitfalls

Overusing CHECK constraints for business logic — If your constraint references business rules that might change, you’re coupling schema evolution to data integrity. Keep business rules in application code.

Ignoring constraint validation order — When multiple constraints exist, the order of evaluation matters. Foreign keys are checked before CHECK constraints. UNIQUE and EXCLUDE constraints interact in complex ways during bulk loads.

Forgetting that constraints can be disabled — In PostgreSQL, constraints can be marked NOT VALID or disabled with ALTER TABLE ... DISABLE TRIGGER ALL. This is sometimes necessary during data migrations but creates risk if misused.

Assuming constraint violations are atomic — In deferred constraint mode, violations aren’t detected until transaction commit. This can surprise developers expecting immediate feedback.

Conclusion

Database constraints are not optional scaffolding—they’re fundamental to data integrity. The database should be your default enforcement layer for any rule that governs what data can exist in your system.

Application-level validation complements but cannot replace database constraints. Use it for user experience, business policy iteration, and complex rules that exceed declarative constraint capabilities.

The performance overhead of proper constraint enforcement is almost always less than the cost of data corruption, consistency bugs, or integrity failures. Design your schema with constraints first, then optimize only when measurements prove it necessary.

Your database is the last line of defense for your data. Make it count.

---

Ready to dive deeper into schema design? Check out our guide on relational databases for foundational concepts, or explore schema design for practical normalization strategies.