Detect Unique Violations and Constraint Errors in Go

"That email is already registered" is the most common validation error in web applications and the one most often implemented wrongly. The common version selects the email first and inserts if none is found, which works until two requests arrive together and both pass the check. The correct version lets the database decide, using a unique constraint, and turns the violation into a clear answer. This article shows how to detect one from Go on PostgreSQL 18.6 and MySQL 8.4, how to tell several constraints apart, and what actually happens when requests race.

It belongs to the database cornerstone. It is the most important case of handling database errors, and the alternative that avoids the error, on conflict, is covered in upserts.

What the server tells you

package main

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"log"
	"sync"

	"github.com/go-sql-driver/mysql"
	"github.com/jackc/pgx/v5/pgconn"
	_ "github.com/jackc/pgx/v5/stdlib"
)

var ErrEmailTaken = errors.New("email already registered")
var ErrHandleTaken = errors.New("handle already taken")

// byConstraint maps the name of a violated constraint to the error the caller should see.
var byConstraint = map[string]error{
	"users659_email_key":  ErrEmailTaken,
	"users659_handle_key": ErrHandleTaken,
}

func register(ctx context.Context, db *sql.DB, email, handle string) error {
	_, err := db.ExecContext(ctx, "insert into users659 (email, handle) values ($1, $2)", email, handle)
	var pg *pgconn.PgError
	if errors.As(err, &pg) && pg.Code == "23505" {
		if mapped, ok := byConstraint[pg.ConstraintName]; ok {
			return fmt.Errorf("register: %w", mapped)
		}
	}
	return err
}

func main() {
	ctx := context.Background()
	db, err := sql.Open("pgx", "postgres://postgres:[email protected]:55432/lab?sslmode=disable")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
	for _, q := range []string{
		"drop table if exists users659",
		"create table users659 (id serial primary key, email text not null unique, handle text not null unique, tenant int not null default 1, slug text, unique (tenant, slug))",
	} {
		if _, err := db.ExecContext(ctx, q); err != nil {
			log.Fatal(err)
		}
	}

	// 1. What the server reports for a duplicate.
	_ = register(ctx, db, "[email protected]", "ada")
	_, err = db.ExecContext(ctx, "insert into users659 (email, handle) values ('[email protected]', 'other')")
	var pg *pgconn.PgError
	if errors.As(err, &pg) {
		fmt.Printf("1. SQLSTATE %s, constraint %q, table %q\n   detail: %s\n", pg.Code, pg.ConstraintName, pg.TableName, pg.Detail)
	}

	// 2. Two different constraints, two different answers for the caller.
	e1 := register(ctx, db, "[email protected]", "new-handle")
	e2 := register(ctx, db, "[email protected]", "ada")
	fmt.Printf("2. same email: %v | is ErrEmailTaken %v\n   same handle: %v | is ErrHandleTaken %v\n", e1, errors.Is(e1, ErrEmailTaken), e2, errors.Is(e2, ErrHandleTaken))

	// 3. A composite unique constraint names itself too.
	_, _ = db.ExecContext(ctx, "insert into users659 (email, handle, tenant, slug) values ('[email protected]', 'h1', 1, 'home')")
	_, err = db.ExecContext(ctx, "insert into users659 (email, handle, tenant, slug) values ('[email protected]', 'h2', 1, 'home')")
	if errors.As(err, &pg) {
		fmt.Printf("3. composite key: constraint %q, detail: %s\n", pg.ConstraintName, pg.Detail)
	}

	// 4. A race: two registrations of the same new email at the same moment. Exactly one wins; the other is a clean conflict.
	var wg sync.WaitGroup
	results := make([]error, 8)
	for i := range results {
		wg.Add(1)
		go func() {
			defer wg.Done()
			results[i] = register(ctx, db, "[email protected]", fmt.Sprintf("racer-%d", i))
		}()
	}
	wg.Wait()
	ok, taken, other := 0, 0, 0
	for _, e := range results {
		switch {
		case e == nil:
			ok++
		case errors.Is(e, ErrEmailTaken):
			taken++
		default:
			other++
		}
	}
	fmt.Printf("4. eight concurrent registrations of one email: %d succeeded, %d reported email taken, %d other\n", ok, taken, other)

	// 5. Checking first is not a substitute: select then insert races.
	var n int
	_ = db.QueryRowContext(ctx, "select count(*) from users659 where email = '[email protected]'").Scan(&n)
	fmt.Println("5. a select before the insert saw", n, "row; the constraint is still what decides when two requests overlap")

	// 6. INSERT ... ON CONFLICT instead of an error, when a duplicate is fine.
	res, _ := db.ExecContext(ctx, "insert into users659 (email, handle) values ('[email protected]', 'again') on conflict (email) do nothing")
	affected, _ := res.RowsAffected()
	fmt.Println("6. on conflict do nothing on an existing email: rows affected", affected, "and no error")

	// 7. MySQL says the same thing with a number.
	my, err := sql.Open("mysql", "root:secret@tcp(127.0.0.1:33099)/lab")
	if err != nil {
		log.Fatal(err)
	}
	defer my.Close()
	for _, q := range []string{"drop table if exists users659", "create table users659 (id int auto_increment primary key, email varchar(100) not null, unique key users659_email (email))", "insert into users659 (email) values ('[email protected]')"} {
		if _, err := my.ExecContext(ctx, q); err != nil {
			log.Fatal(err)
		}
	}
	_, err = my.ExecContext(ctx, "insert into users659 (email) values ('[email protected]')")
	var me *mysql.MySQLError
	if errors.As(err, &me) {
		fmt.Printf("7. MySQL: number %d, SQLSTATE %s, message: %s\n", me.Number, me.SQLState, me.Message)
	}
}

1. SQLSTATE 23505, constraint "users659_email_key", table "users659"
   detail: Key (email)=([email protected]) already exists.
2. same email: register: email already registered | is ErrEmailTaken true
   same handle: register: handle already taken | is ErrHandleTaken true
3. composite key: constraint "users659_tenant_slug_key", detail: Key (tenant, slug)=(1, home) already exists.
4. eight concurrent registrations of one email: 1 succeeded, 7 reported email taken, 0 other
5. a select before the insert saw 1 row; the constraint is still what decides when two requests overlap
6. on conflict do nothing on an existing email: rows affected 0 and no error
7. MySQL: number 1062, SQLSTATE 23000, message: Duplicate entry '[email protected]' for key 'users659.users659_email'

The program creates a users659 table with unique constraints on email and handle, and a composite one on (tenant, slug). Inserting a duplicate email gave the first line of output:

SQLSTATE 23505, constraint "users659_email_key", table "users659"
detail: Key (email)=([email protected]) already exists.

Everything useful is in the *pgconn.PgError: the code 23505 (unique_violation), the constraint name, the table, and a detail line with the column and the value. Get it with errors.As:

var pg *pgconn.PgError
if errors.As(err, &pg) && pg.Code == "23505" { ... }

Check the code, never the message. The message depends on the server's language setting and has changed between versions; the five character code is part of the SQL standard and does not.

Which constraint failed

A table usually has more than one unique rule, and the user needs to know which. The constraint name answers it. PostgreSQL names them table_column_key unless you name them yourself, so users659_email_key is the email rule and users659_handle_key the handle rule. The program keeps a map from constraint name to the error it should return, and the second block shows the result: registering with an existing email returned register: email already registered, and with an existing handle register: handle already taken, each true for its own errors.Is.

Two cautions. Constraint names are part of your schema, so name them explicitly in migrations (constraint users_email_unique unique (email)), or a rename or a different tool's default will change the name and silently break the mapping. And keep the map in the same package as the SQL, so a change to one is seen with the other. An unknown constraint should fall through to the generic path, not be reported as a user error.

A composite constraint works the same way. The third block inserts a second home slug in the same tenant and gets the constraint users659_tenant_slug_key with the detail Key (tenant, slug)=(1, home) already exists. One check covers single and multi column rules, and the detail shows the values for the log. Do not return the detail to the user: it contains the data that clashed, which may belong to someone else.

The race

The fourth block is why the constraint is the right tool. Eight goroutines each try to register the same new email at once, with different handles. The result:

1 succeeded, 7 reported email taken, 0 other

Exactly one insert won, and the others received a clean, classified conflict. No lock, no retry loop and no special isolation level was needed. PostgreSQL's unique index is checked at insert time and makes concurrent attempts take turns.

The fifth block shows the alternative that does not work. A select count(*) before the insert returns 1 for an existing row, and nothing stops two requests from both seeing 0 and then both inserting. Without the constraint the result would be two rows; with it, one request would fail anyway, with the same error, now arriving as a surprise from the insert. So the check before the insert can be a courtesy for a better message, and the constraint must still be there, and the code must still handle the violation. Treat the pre-check as an optimisation at most.

When a duplicate is fine

Sometimes a duplicate is not an error but a no-op: "make sure this user exists", "record that this event was seen". There the right tool is not an error check but insert ... on conflict (email) do nothing. The sixth block shows it: rows affected 0 and no error. Use RowsAffected to tell whether you inserted. And when the existing row should be updated, on conflict do update; see upserts with ON CONFLICT. Which one to use depends on whether the caller cares that the row already existed: registration does, an idempotent event log does not.

MySQL

The last block does the same for MySQL, where a duplicate is error number 1062 with SQLSTATE 23000 and the message Duplicate entry '[email protected]' for key 'users659.users659_email'. The driver's type is *mysql.MySQLError, with fields Number, SQLState and Message, found with errors.As:

var me *mysql.MySQLError
if errors.As(err, &me) && me.Number == 1062 { ... }

Two differences matter. MySQL's SQLSTATE 23000 covers all integrity violations, so the number is what separates a duplicate (1062) from a foreign key failure (1452 or 1451), and it is the thing to check. And the constraint name appears only inside the message, as the for key part, in a form that changed between MySQL 5.7 and 8.0, so extracting it means parsing text, which is fragile. If you need to tell two unique keys apart on MySQL, give the unique keys distinct, stable names and match the name in the message with a pattern you test against your server version, or check the specific condition with a query after the failure.

Putting it together

A registration flow that holds up under load:

1. Insert directly. Do not select first.
2. If the error is a unique violation, look up the constraint name and return the matching domain error.
3. Map that error to a 409 with a message that names the field and nothing else: "that email is already registered".
4. Log the detail and the constraint, at info level, since it is the user's input and not a fault.
5. For idempotent operations, use on conflict do nothing and check RowsAffected.
6. Test with concurrency: a test that fires a few dozen goroutines at the same key and asserts one success, as the lab does, finds missing constraints and mapping gaps before production does.

There is one more point about enumeration. Telling a stranger that an email is registered tells them who your users are. For public sign up, the standard answer is to respond the same way whether or not the address existed ("check your email") and send the information by mail. The constraint still does the work; only the message to the requester changes. PostgreSQL documents the codes in its error codes appendix and the unique constraint behaviour in constraints, and MySQL lists its numbers in the server error reference.