SQL Constraints: Enforcing Data Integrity at Write Time

SQL Constraints and Data Integrity

SQL constraints make the database enforce rules that must hold regardless of which application writes the data. Start with the invariant: every order needs an existing customer, quantities must be positive, and the chosen customer identifier must be unique. Then test an attempted violation directly.

Application validation still matters for helpful feedback. It cannot substitute for a database rule when another writer, an import, or a concurrent request can reach the same table.

TL;DR

Use NOT NULL for required values and CHECK for the permitted relationship among row values.
Use UNIQUE for the actual business key, including its scope.
Use foreign keys for relationships that must reference existing rows.
Test NULL, duplicates, invalid values, and orphan references separately.
Check the specific database's enforcement, NULL, collation, and migration behavior.
Clean up existing violations before enforcing a new rule on a populated table.

Quick Example

This complete Python example uses an in-memory SQLite database. It enables foreign keys before starting a transaction and checks that enforcement is active. The addresses are fictional reserved-domain fixtures. It creates no persistent database.

Expect five rejection messages and one surviving valid order. These checks demonstrate SQLite behavior; they do not validate a PostgreSQL migration or production connection pool. SQLite documents that foreign-key enforcement must be enabled for each connection, and changing that setting inside a transaction has no effect.

Core Concepts

A constraint expresses a durable invariant

A useful rule survives a change of writer. “The signup screen currently requires an email” describes one interface; “this table never stores a missing email” describes a database invariant. Decide whether background jobs, administrative imports, and older application versions must obey the same restriction before choosing its location.

NULL is a separate design decision

Do not read CHECK (quantity > 0) as “quantity is required.” SQL's unknown result can allow a NULL through a check, so combine the range rule with NOT NULL when absence is invalid. Uniqueness also needs explicit NULL semantics: PostgreSQL defaults to treating NULLs as distinct and supports NULLS NOT DISTINCT for a different policy. See PostgreSQL constraints. Do not assume that syntax or every edge case is portable.

Relationships need a deletion policy

A foreign key is part of lifecycle design. Ask what should happen when a referenced customer is removed: reject the deletion, retain a historical record through another model, or intentionally cascade a suitable dependent record. The example uses the default action. It does not endorse deleting purchase history or applying cascading deletion to every relationship.

Match the Rule to Its Boundary

This table is a design aid, not a complete schema. A tenant-scoped key may require a composite unique constraint. If references must also remain inside that tenant, review a corresponding composite relationship. A globally valid foreign key alone does not prove tenant isolation.

Keep cross-row calculations out of a simple row check. PostgreSQL warns against CHECK conditions that depend on other rows or tables. Model the invariant through suitable keys, transactions, locking, or another database-supported mechanism after reviewing the actual concurrency requirement. A payment balance rule deserves more than a clever expression pasted into a migration.

Plan a Change on Existing Data

For a proposed required field, first count missing and invalid values and identify every writer. Decide how to repair records with a business owner; an invented default can turn a visible absence into misleading data. Update writers and validate a representative dataset before applying the enforcement step.

Rehearse the chosen database's migration against realistic table sizes and concurrent writes. Record lock duration, failure behavior, and the rollback or roll-forward route. Database-specific online validation and index-building features need their own documented procedure; the SQLite fixture is deliberately not a production migration recipe.

A useful acceptance condition is that an old writer attempting invalid data receives a controlled failure and the application translates it into a stable response. Capture the constraint identity in internal diagnostics when appropriate, while avoiding raw SQL or sensitive record contents in user-visible errors.

Best Practices

Test the rejected write

Keep a valid control row, attempt one violation at a time, and verify the valid data remains. This reveals whether the rule exists at the database boundary and whether failure handling accidentally rolls back unrelated work.

Define normalization before uniqueness

Case, whitespace, collation, and application normalization can change what users mean by “the same.” The example compares stored email text; it does not solve real email identity policy. Agree the intended representation and test it in the target engine before promising deduplication.

Handle the concurrent loser

An application precheck can improve feedback, but two requests can both observe no existing record. Let the database enforce the key and handle the rejected write deliberately. Rehearse this with separate connections in the actual database; our single-connection example does not exercise concurrency.

Common Mistakes

Bad: Validate only in the form and assume imports follow the rule.

Correct: Enforce durable data invariants at the storage boundary and retain friendly application validation.

Bad: Assume a foreign key grants permission to access the referenced row.

Correct: Test authorization independently. Referential integrity establishes a relationship, not caller entitlement.

Bad: Disable constraints to get a failing import through and forget the repair.

Correct: Stage, inspect, and correct the input through an explicit reviewed process, preserving evidence of rejected rows.

FAQ

Do constraints prevent SQL injection?

No. Continue using parameterized statements and appropriate database permissions. A valid stored value says nothing about whether query text was constructed safely.

Why use both CHECK and NOT NULL?

They answer separate questions: whether an existing value is permitted and whether a value must exist. Test missing and out-of-range inputs independently.

Can an ORM replace database constraints?

An ORM can declare or generate them, but inspect the applied database schema. A model declaration that was never migrated cannot enforce anything against a direct write.

What does this fixture leave untested?

Production-engine typing, collation, concurrent writes, migration locks, deletion policy, and application error translation. Add checks for the rules and operational conditions your actual schema needs.