liveBook · Manning

Unique constraint

As explained in Crack Any Codebase with AI

A unique constraint requires one or more database columns to contain a value combination that is not duplicated by another row.

A unique constraint makes the database reject any row whose constrained value combination already exists. It is a durable duplicate-write guard: instead of trusting application timing, let the database enforce the invariant atomically. section 8.5

Preventing a double booking

For a booking endpoint, the server derives a deterministic idempotency key from startTime, endTime, and userId. The first INSERT succeeds; a retried POST produces the same key, triggers the UNIQUE constraint, and lets the handler return the existing booking instead of charging twice. section 8.5

Use the constraint when:

  • A natural identifier can represent duplicates, such as an idempotency key. section 8.5
  • You want a fully atomic race resolution without holding a lock, while accepting duplicate-key handling in application code. section 8.5
Table 8.4 Strategies for resolving a write race
Mechanism
How it resolves the race
What it buys
What it costs
SELECT … FOR UPDATE (pessimistic)
First writer locks the row; the second blocks until the first commits
Strong, simple to reason about; no retry logic
Holds a lock across the critical section; serializes writers, lowering throughput
Version column (optimistic)
Both write with WHERE version = N; the loser's update affects 0 rows and retries
No lock held; high throughput when conflicts are rare
The app must detect the 0-row update and retry, more code, worse under contention
Unique constraint
Both INSERT; the database rejects the second with a duplicate-key error
Cheapest, fully atomic, leans on a guarantee the DB already enforces
Only works when there's a natural unique identifier to constrain on

Compare unique constraints with pessimistic locking and optimistic version checks when choosing a write-race strategy. section 8.5

Figure 8.8 A double-clicked "Confirm" sends two requests with the same idempotencyKey. A UNIQUE constraint lets the first INSERT succeed but rejects the second, creating one booking instead of a double charge.

Trace how two requests with the same idempotency key produce one successful booking. section 8.5

How Crack Any Codebase with AI covers it

How deep it goes
Explains the invariant, atomic duplicate rejection, and application response, but does not detail constraint declaration syntax.
Examples
Uses a double-clicked booking confirmation and a deterministic idempotency key derived from booking inputs.
What you can do after
Apply it to make a naturally identifiable write safe against duplicate insertion and request retries.
How it connects
Connects idempotency, concurrency control, database transactions, retries, and optimistic locking.

Also explained in

Compare how 8 books explain unique constraint →

Related concepts

This explainer was generated by Manning's knowledge graph from the text of Crack Any Codebase with AI. The section links point to the parts of the book it draws on.