Two requests check whether a booking reference exists. Both find nothing, and both insert it. The application performed a check, but the gap between checking and writing allowed the duplicate.
Put the invariant where the rows meet
A database uniqueness constraint can enforce that a column or combination of columns does not repeat. PostgreSQL’s constraint documentation describes unique constraints and their treatment of null values. The exact semantics matter: a uniqueness rule should express the business invariant you actually need.
For example, a reservation reference might be unique within an organisation rather than across every customer using the application. A combined constraint on organisation and reference represents that boundary. Making only the reference globally unique could reject legitimate records from another organisation.
Handle the losing request deliberately
Keep the early application check if it improves feedback, but do not rely on it as the final protection. When a concurrent insert conflicts, return an appropriate response or apply a carefully designed idempotent operation. Do not turn every database error into “already exists”; a connection failure is a different problem.
Consider normalisation before choosing the key. If the business treats letter case or surrounding spaces as equivalent, the storage and constraint design must match that rule. Also decide whether missing values are allowed, rather than discovering null behaviour through production duplicates.
Before adding a constraint to an existing table, inspect current duplicates and plan the migration safely. Test two genuinely concurrent requests, not only two sequential inserts. Verify both the stored row count and the response received by each caller.
A unique constraint settles one invariant. It does not replace authorisation, transaction design or limits on other related records. Its value is precise: when competing requests reach the same table, the database enforces the rule even if both application checks looked clear.