Constraints enforce data invariants even when multiple callers write concurrently. Application validation improves messages but cannot replace enforcement.
Before you start
You should understand tables, keys, joins and SELECT queries. Write down a tiny dataset before reasoning about a query. SQL examples use PostgreSQL-style syntax where relevant; planner behavior, locking and available features must be checked for the database and version used in an application.
The practical goal is to reason through this situation: A unique email constraint handles two simultaneous signup attempts. Read the walkthrough first, then try the interview exercise before opening its answer. The important part is explaining the decision and its consequences, rather than remembering a definition alone.
Step-by-step walkthrough
Step 1: Validate for clear feedback
An availability check can help the user but is not the final concurrency guarantee.
Step 2: Enforce in storage
Keep a uniqueness constraint protecting simultaneous writes.
Step 3: Map specific conflicts
Return a stable duplicate outcome without exposing database internals.
Worked scenario
A unique email constraint handles two simultaneous signup attempts.
Two signup requests both see the email as available. The unique constraint allows only one insertion. The second request must catch that specific conflict and report the domain outcome; removing the constraint because the application already checked availability recreates the race.
Common mistake
Checking availability before insert creates a race window.
Verify the behavior
Submit concurrent duplicate records and verify one stored row plus controlled conflict handling.
Interview exercise
Return a useful duplicate response.
Answer and reasoning
Keep the constraint, catch the specific conflict and map it to a stable domain outcome without exposing database internals.
Continue learning
Compare the scenario with the SQL and PostgreSQL interview questions and test your understanding with the SQL and PostgreSQL MCQs. For terminology and implementation details, consult the reference material.