Fix: 'Duplicate key value violates unique constraint' in Supabase

You’re building a signup flow or a "save profile" feature in Supabase. A user clicks the button once, then clicks it again. Or maybe you're importing data from an API.

Suddenly, your app crashes, and Supabase returns a red error: "Error: duplicate key value violates unique constraint 'users_email_key'." Or perhaps it’s your Primary Key: "duplicate key value violates unique constraint 'posts_pkey'."

This is the "Race Condition Trap."

In the world of "Vibe Coding," where we move fast and rely on AI assistants like Cursor or v0 to build our backends, we often forget about Concurrency. AI tools are great at writing the code to "Create a user," but they often forget to handle the scenario where that user already exists, or where two simultaneous clicks create two identical requests.

In this guide, we’ll explain what unique constraints are, why they are your best friend (even when they crash your app), and how to master the Supabase .upsert() pattern to resolve conflicts without errors.

What is a Unique Constraint? (The Postgres Guardian)

To fix the error, you have to appreciate why it exists.

A Unique Constraint is a rule in your database that says: "In this column, no two rows can have the same value."

• Primary Keys (id): Every table has one. It ensures every row is unique.
• Emails/Usernames: You don't want two users with the same email.
• Slugs: You don't want two blog posts with the same URL.

When you try to insert a duplicate value, PostgreSQL doesn't "silently fail"—it raises an alarm. It stops the operation to prevent your data from becoming a corrupted mess.

Common Scenario: Double-Signups and Double-Submissions

The most common cause of this error in a Next.js app is a Missing Button Disable.

1. User clicks "Submit."
2. Request is sent to Supabase.
3. User clicks "Submit" again before the first request finishes.
4. Two identical requests hit the database. The first one succeeds; the second one triggers the "Unique Constraint" error.

The Solution: Mastering Supabase .upsert()

Instead of using .insert(), which only knows how to create new rows, you should almost always use .upsert().

Upsert stands for "Update or Insert." It tells the database: "Look for a row with this ID. If you find it, update its values. If you DON'T find it, create a new one."

The Basic Pattern:

const { data, error } = await supabase
  .from('profiles')
  .upsert({ 
    id: user.id, 
    full_name: 'Jake', 
    updated_at: new Date() 
  })

By default, .upsert() uses the Primary Key to determine if a row already exists. If the user clicks twice, the second request simply updates the row created by the first request, and no error is thrown.

Handling Conflicts: onConflict and ignoreDuplicates

What if you want to use a column other than the primary key to check for duplicates? For example, an email column.

const { data, error } = await supabase
  .from('profiles')
  .upsert(
    { email: 'test@example.com', username: 'jake' },
    { onConflict: 'email' } // Tell Supabase to check the email column
  )

If you want the database to simply ignore the second request instead of updating it, use ignoreDuplicates:

const { data, error } = await supabase
  .from('logs')
  .upsert(
    { event_id: '123' },
    { onConflict: 'event_id', ignoreDuplicates: true }
  )

Database-Level Fix: Cleaning Up Duplicate Rows

If you are getting this error on a column that should be unique but you forgot to set the constraint in the dashboard, you might already have duplicate data.

You cannot add a "Unique Constraint" to a table that already contains duplicates.

The Workflow:

1. Identify Duplicates: Use the SQL Editor to find them.
2. Delete Duplicates: Keep only the most recent version of each row.
3. Add the Constraint: Go to the Supabase Dashboard -> Table Editor -> Edit Column -> Check "Unique."

Frontend Best Practices: Optimistic UI vs. Error Handling

While .upsert() fixes the backend, you should still fix the frontend UX.

• Disable Buttons: Always disable your "Submit" button immediately after the first click.
• Try/Catch Blocks: Always wrap your Supabase calls in a try/catch or check the error object.
• User-Friendly Messages: Don't show the raw "violates unique constraint" error to the user. Translate it to something helpful like: "That email is already registered."

FlowQL: Backend Hardening for High-Concurrency Apps

Database architecture is the "last 20%" where simple apps become professional products. AI assistants are great at building "Table A" and "Table B," but they struggle with Concurrency, Constraints, and Data Integrity.

They will often suggest insert logic that works fine for a single developer testing locally but breaks the moment you have 100 users clicking buttons.

At FlowQL, we help companies harden their Supabase backends. We provide the senior database expertise to audit your constraints, optimize your upsert logic, and ensure your app stays stable under pressure.

If your Supabase logs are filled with unique constraint violations, it's time for a professional backend audit.

Conclusion

The "Duplicate key" error is not a bug; it is a feature. It is your database protecting you from yourself. By using upserts and properly configuring your onConflict rules, you can handle race conditions and duplicate submissions gracefully.

Your Action Plan:

1. Identify which constraint is being violated (check the error message).
2. Switch from .insert() to .upsert().
3. Add an onConflict clause if you aren't using the Primary Key.
4. Disable your frontend buttons on click.

Don't let a race condition crash your launch. [Book a session with FlowQL] and let’s get your Supabase database rock-solid.

---

FAQ: Supabase and Unique Constraints

Q: Does upsert work with RLS? A: Yes, but you must ensure your RLS policy allows both INSERT and UPDATE operations for the user, otherwise the upsert will fail.

Q: What is the 'onConflict' syntax? A: It is a string containing the column name(s) that should be checked for uniqueness. For multiple columns, use a comma-separated string: onConflict: 'user_id,post_id'.

Q: Why does my upsert still fail? A: Check if you have Required (NOT NULL) columns that you aren't providing in the upsert object. Even if the row already exists, the database may still check for those values.