Why is a database unique constraint important even if the application checks for duplicates?

Use unique constraints for business rules

Makes business uniqueness race-safe with the exact database key, while handling normalization, tenant scope, nulls, soft deletion, rollout cleanup, and stable API conflicts.

1Say itAnswer out loud first

2CompareCompare with Core answer

3RefineRefine with Stronger and Advanced

TL;DR

I encode the real uniqueness rule as a unique constraint or unique index. Two requests can both run SELECT and see no duplicate, so an application-only check loses the race; the database allows one insert and rejects the other. The API catches the specific constraint violation and maps it to a stable conflict response rather than returning a generic 500. I may still check earlier for a friendly message, but correctness does not depend on that check.

Full answer below

Practice focus

Uniqueness, race conditions, constraints, filtered indexes, friendly errors, and concurrent requests.

Answer first

Answer path

Core answer first, then checkpoints toward a stronger answer.

Start
Core answer
Start here. This is the direct spoken answer to practice first.
Why this question matters
A pre-check improves the user message but cannot serialize concurrent requests. The database constraint is the final arbiter that prevents two valid-looking attempts from committing the same business identity.
I encode the real uniqueness rule as a unique constraint or unique index. Two requests can both run SELECT and see no duplicate, so an application-only check loses the race; the database allows one insert and rejects the other. The API catches the specific constraint violation and maps it to a stable conflict response rather than returning a generic 500. I may still check earlier for a friendly message, but correctness does not depend on that check.
Checkpoint
Stronger answer
Optional upgrade with examples, trade-offs, and sharper phrasing.
For example, a slug that is unique only within a tenant uses (TenantId, NormalizedSlug), while normalized email follows the product's case and Unicode rules before persistence. Soft deletion needs an explicit policy: deleted values may remain reserved, or a filtered unique index can apply only to active rows. Null behavior is defined for the chosen database instead of assumed. I identify the violated constraint by a known name or provider error metadata, not by parsing a localized exception message, and I do not echo another user's protected record in the response.
Finish
Advanced answer
Optional deeper angle for production judgment and nuance.
In production I add uniqueness to existing data as a migration project. I measure duplicates, choose deterministic merge or remediation rules with a business owner, prevent new violations during the transition, and create or validate the index using an operationally safe deployment method for the platform and table size. The application version deployed before enforcement tolerates the new database error, so rolling instances do not convert expected conflicts into server failures. For workflows that perform an external effect before inserting the unique record, I move the guard earlier or add an idempotency boundary; a late constraint cannot undo a charge or email. I consider collation, normalization version changes, tenant moves, soft-delete restore, and null semantics because each can turn yesterday's valid rows into today's collision. Metrics track violation rate and affected operation without storing sensitive duplicate values; spikes may reveal double submits, retry bugs, abuse, or a changed normalization rule. Concurrent integration tests issue the same business operation through separate database contexts and prove exactly one durable winner. The constraint stays aligned with the promise in the API and domain model rather than becoming an accidental storage detail.

Review signals

Use these to catch weak phrasing, shallow reasoning, and risky claims.

Common mistakesCandidateCommon mistakesMistakes that weaken the answer or signal shallow understanding.Open details

Details

Relying on SELECT-then-INSERT without a unique database guard.
Making a tenant-scoped value globally unique.
Parsing exception text instead of mapping a known constraint or provider code.
Adding the constraint before remediating existing duplicates and rollout compatibility.

Red flagsCandidateRed flagsSignals that an interviewer may treat as risky.Open details

Details

Cannot explain the race between check and insert.
Has no plan for mapping constraint errors.
Does not model scoped uniqueness.
Ignores migration risk on existing tables.

Follow-up prompts

Quick interviewer prompts inside this drill.

Follow-up questionsCandidateFollow-up questionsLikely next questions after the main answer.Open details

Details

Treat these as the next interviewer turn. Answer one out loud, then save the gap in your practice notes.

How would you enforce unique slugs per tenant?
How would soft delete affect uniqueness?
How would you clean up duplicates before adding the constraint?

Continue the section