Back to Practical SQL Queries
Q21Question 21 of 34

Duplicate Customer Emails and Why App-Level Checks Need a DB UNIQUE Constraint

Key Concepts
  • GROUP BY email HAVING COUNT(*) > 1 with LOWER and TRIM normalization finds existing duplicates.
  • The check-then-insert pattern is a TOCTOU race: two threads can both see "not exists" and both insert.
  • A UNIQUE constraint enforces uniqueness atomically at write time, catching the concurrent race.
  • For case-insensitive uniqueness, use a functional unique index on LOWER(email) or citext.
  • The app validates for a friendly message; the database enforces for correctness under concurrency.
  • INSERT ON CONFLICT (upsert) avoids the check entirely for an optimistic approach.
  • Any invariant that must never be violated belongs as a database constraint, not just application logic.

Definition

`GROUP BY email HAVING COUNT(*) > 1` finds dupes, but the app's check-then-insert races under concurrency: only a DB `UNIQUE` constraint truly prevents duplicates.
  • Detection query – SELECT LOWER(TRIM(email)), COUNT(*) FROM customers GROUP BY LOWER(TRIM(email)) HAVING COUNT(*) > 1 finds existing duplicates with normalization.
  • Check-then-insert race – the app's if (!emailExists) insert is a time-of-check-to-time-of-use race; two threads can both see "not exists" and both insert.
  • UNIQUE constraint – the database enforces uniqueness atomically at write time; the second concurrent insert fails with a unique-violation error.
  • App validates, DB enforces – the application check gives a friendly message in the common case; the constraint guarantees correctness under concurrency.
  • Case-insensitive uniqueness – use a functional unique index on LOWER(email) or PostgreSQL's citext type.

Two people walk up to two different counters at National Bank of India at the same second, both wanting to register rajesh@x.com. Each teller checks "is this email taken?" and both see "no" because neither registration is saved yet. Both say "go ahead." The email now exists twice. The only fix is a rule stamped on the ledger itself that physically refuses a second identical email.

Understanding the Concepts

How do you find duplicate emails?

Group by a normalized email and keep groups with a count above one.

SQL
-- Find duplicate emails at National Bank of India
SELECT LOWER(TRIM(email)) AS norm_email, COUNT(*) AS cnt
FROM customers
GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) > 1
ORDER BY cnt DESC;
TEXT
norm_email         | cnt
-------------------+----
rajesh@nbi.com     | 3
priya@hdfc.com     | 2

LOWER(TRIM(email)) normalizes so that Rajesh@NBI.com and rajesh@nbi.com are treated as the same.

How do you see the actual duplicate rows?

Join back to the original table to see which rows are duplicated.

SQL
SELECT c.*
FROM customers c
JOIN (
    SELECT LOWER(TRIM(email)) AS norm_email
    FROM customers
    GROUP BY LOWER(TRIM(email))
    HAVING COUNT(*) > 1
) dupes ON LOWER(TRIM(c.email)) = dupes.norm_email
ORDER BY LOWER(TRIM(c.email)), c.id;

Why is check-then-insert a race condition?

The application's check-then-act pattern is broken under concurrency.

JAVA
package com.bank.service;

public class CustomerService {

    // WRONG: race condition under concurrency
    public void registerBad(String email) {
        if (!customerRepo.existsByEmail(email)) {   // Step 1: check
            customerRepo.insert(new Customer(email)); // Step 2: insert
        }
    }
}
DIAGRAM
  Thread A                          Thread B
  ────────                          ────────
  existsByEmail("rajesh@nbi.com")   existsByEmail("rajesh@nbi.com")
  → false (not found)               → false (not found, A not committed)
  insert("rajesh@nbi.com")          insert("rajesh@nbi.com")
  → success                         → success
  RESULT: two rows with same email!

This is a time-of-check-to-time-of-use (TOCTOU) race. It happens regularly at scale with retries and double-clicks.

How does a UNIQUE constraint fix the race?

The database enforces uniqueness atomically at write time. If two concurrent inserts race, the first to commit wins and the second fails.

SQL
-- Add the UNIQUE constraint
ALTER TABLE customers
    ADD CONSTRAINT uq_customers_email UNIQUE (email);

-- For case-insensitive uniqueness:
CREATE UNIQUE INDEX uq_customers_email_ci
    ON customers (LOWER(email));
JAVA
package com.bank.service;

import java.sql.SQLException;

public class CustomerService {

    // CORRECT: database enforces uniqueness
    public void registerGood(String email) {
        try {
            customerRepo.insert(new Customer(email.toLowerCase().trim()));
        } catch (SQLException e) {
            if (e.getSQLState().equals("23505")) { // unique violation
                throw new EmailAlreadyRegisteredException(email);
            }
            throw new RuntimeException(e);
        }
    }
}

Why keep the application check if the constraint is the guarantee?

The app check gives a fast, friendly response in the common non-racing case and avoids an exception on every normal duplicate attempt. But it is an optimization, not the guarantee.

JAVA
package com.bank.service;

import java.sql.SQLException;

public class CustomerService {

    // Best practice: validate in app, enforce in DB
    public void registerBest(String email) {
        String normalized = email.toLowerCase().trim();

        // App validation: friendly message in the common case
        if (customerRepo.existsByEmail(normalized)) {
            throw new EmailAlreadyRegisteredException(normalized);
        }

        // DB enforcement: catches the concurrent race
        try {
            customerRepo.insert(new Customer(normalized));
        } catch (SQLException e) {
            if (e.getSQLState().equals("23505")) {
                throw new EmailAlreadyRegisteredException(normalized);
            }
            throw new RuntimeException(e);
        }
    }
}

How do you use INSERT ON CONFLICT for upserts?

The optimistic approach avoids the check entirely.

SQL
-- PostgreSQL: upsert with ON CONFLICT
INSERT INTO customers (email, name)
VALUES ('rajesh@nbi.com', 'Rajesh')
ON CONFLICT (email) DO NOTHING;
-- Returns 0 rows affected if the email already exists

-- Or update on conflict
INSERT INTO customers (email, name)
VALUES ('rajesh@nbi.com', 'Rajesh')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

How do you clean up existing duplicates?

Keep the oldest row and delete the rest.

SQL
-- Delete duplicates, keeping the row with the lowest id
DELETE FROM customers
WHERE id NOT IN (
    SELECT MIN(id)
    FROM customers
    GROUP BY LOWER(TRIM(email))
);

-- Then add the UNIQUE constraint
ALTER TABLE customers ADD CONSTRAINT uq_customers_email UNIQUE (email);

What other invariants should be database constraints?

The principle generalizes beyond emails.

SQL
-- Unique account numbers
ALTER TABLE accounts ADD CONSTRAINT uq_account_number UNIQUE (account_number);

-- Non-negative balance
ALTER TABLE accounts ADD CONSTRAINT chk_balance_positive
    CHECK (balance >= 0);

-- One active loan per customer per product
CREATE UNIQUE INDEX uq_active_loan
    ON loans (customer_id, product_id)
    WHERE status = 'ACTIVE';

Any invariant that must never be violated belongs as a database constraint.

What should a senior engineer know?

  • The application validates for UX; the database enforces for correctness.
  • Case-insensitive uniqueness requires a functional index on LOWER(email) or PostgreSQL's citext.
  • The robust write pattern is optimistic: try the insert, catch the unique violation.
  • Even SERIALIZABLE isolation does not reliably prevent duplicate inserts without a constraint.
  • Monitor unique-violation error rates to detect retry storms or frontend bugs.

Visual Overview

DIAGRAM
  Thread A                              Thread B
  ────────                              ────────
  SELECT (email exists?) ──▶ No         SELECT (email exists?) ──▶ No
       │                                     │
       ▼                                     ▼
  INSERT rajesh@nbi.com ──▶ ✓           INSERT rajesh@nbi.com
       │                                     │
       │                                     ▼
       │                          ┌──────────────────────┐
       │                          │ UNIQUE constraint:   │
       │                          │ REJECT (23505 error) │
       │                          └──────────────────────┘
       ▼
  COMMIT ✓

  Without constraint: both succeed ──▶ DUPLICATE!
  With constraint: second fails  ──▶ SAFE

Key Takeaway

To find duplicate emails, group by a normalized email and keep groups with a count above one. But the more important point is why they exist. An application check like if (!emailExists) insert is a time-of-check-to-time-of-use race: under concurrency two requests both see "not present" and both insert, producing a duplicate.

The only robust fix is a database UNIQUE constraint, because the database is the single serialization point that every concurrent client funnels through, and it enforces uniqueness atomically at write time. The application check remains useful for a fast, friendly response in the common non-racing case, but it is an optimization, not the guarantee.

At the National Bank of India a customer double-clicked submit on a slow connection, two requests both passed the check-then-insert, and two accounts ended up sharing one email, which later broke password reset. Rajesh cleaned up the existing duplicates and added a case-insensitive unique index so it can never recur.

The principle generalizes: any invariant that must never be violated, such as unique account numbers, one active loan per customer, or a non-negative balance, belongs as a database constraint. The application validates, the database enforces.

Remember one sentence: the app's check-then-insert races, so back every uniqueness rule with a DB UNIQUE constraint.

---

Share
Use ← → arrow keys to navigate