Database Integrity and Uniqueness Constraints

Duplicate records are rarely an input validation problem. When two concurrent processes can attempt to create the identical record at the same instant, uniqueness becomes a data integrity problem—and the database is the only layer that observes every concurrent write simultaneously.

A workflow built around SELECT → "record does not exist" → INSERT often passes unit and integration tests under serial execution. But in production, two concurrent requests can easily execute the SELECT query before either transaction commits. Both queries conclude that the record does not exist yet, and both proceed to write valid duplicates into the table.

The Pitfall of Application-Level Checks

This category of race condition cannot be eliminated by adding more application-level conditionals or retries. Any guard rail enforced solely in application memory is vulnerable to another background worker, another API instance, or a separate service version executing concurrently against the same shared database.

When duplicate records are invalid by definition, the rule must be elevated to a fundamental storage invariant. A UNIQUE constraint or unique index establishes the invariant at the storage engine level, and atomic write operations transform non-deterministic concurrency into a predictable outcome.

Moving from "Check-Then-Write" to "Write-and-Handle"

In practice, the architectural paradigm shifts from "check first, then write" to "attempt write and handle conflicts explicitly." Depending on the intended semantics, systems can leverage constructs such as INSERT ... ON CONFLICT, upsert operations, or handle unique constraint violations as deliberate execution branches.

While this distinction seems subtle, it cleanly reassigns architectural responsibilities: the application decides how to handle the collision gracefully, while the database guarantees that an invalid or impossible state is never persisted, regardless of how many producers write simultaneously.

Idempotency as a Shared State Invariant

Robust idempotency never stems from an optimistic check executed prior to an insert. It emerges when uniqueness guarantees are enforced directly where shared state resides, and when application services treat concurrency conflicts not as unexpected exceptions, but as ordinary, expected branches of the system's operational lifecycle.