A Duplicate Check Is A Courtesy, Not A Rule

If your system claims that two records can't be the same thing, the database has to be the one saying so. A duplicate check in application code, where you look for a match and insert if you don't find one, is a courtesy to the user. It produces a pleasant message in the common case. It is not a guarantee, and the gap between those two things is where duplicates quietly get made. The fix is small: store the value you compare on in its normalised form, put a unique index across it, and keep the application check only as the friendly front door.

I watched this play out in a code review this week on a feature that stored contact people against suppliers. The requirement was perfectly reasonable: reject a new contact if the same name already exists for the same supplier, and point the user at the existing record so they edit it instead. The first implementation did exactly that. It looked up the name, found nothing, and inserted. In a demo it was flawless. It would also pass every test written by someone who tests one request at a time.

Two requests can both be innocent

The trouble with check-then-insert is that the check and the insert are two separate moments. Between them, the world can change. A user double-clicks a save button. Two people on a shared screen add the same contact within a second of each other. A mobile client retries a request it thinks failed. Each request does its check, sees an empty result, and proceeds. Neither is wrong, because at the moment each one looked, no duplicate existed. Together they create one.

This is the kind of bug that doesn't announce itself. It doesn't throw. It doesn't fail a test. It shows up months later as two near-identical rows and a support conversation that begins with "why does this appear twice?" By then nobody can say which request was the extra one, and the people who have to clean it up are rarely the people who wrote the check.

The instinct of many developers, mine included on a bad day, is to reach for a lock. Wrap the check and the insert together so nothing can sneak between them. That helps, but it's a narrower fix than it looks. A lock only protects the code paths that remember to take it. The next import job, the next admin script, the next developer adding a second way to create the same record will not necessarily know the lock exists. A rule that depends on every writer behaving politely isn't a rule. The database is the only place every writer has to pass through, so it's the only place where the guarantee can honestly live.

The second problem hiding behind the first

Making the database enforce the rule exposed something I find more interesting, because it's the kind of thing a review catches and a demo never does. The comparison itself was case and whitespace tolerant. "Smith Co" and "SMITH CO " were meant to count as the same supplier. The original code achieved that by trimming and upper-casing the column inside the query.

That sounds harmless until you think about what the database does with it. An index is a sorted structure over the stored values. The moment you wrap a column in a function, the database can no longer use that ordering to jump to a match, because the sorted values and the thing you're asking about are no longer the same thing. It has to compute the function for every row and compare. On a small table nobody notices. On a table that grows, a rule that was meant to be a quick lookup becomes a scan that gets slower every month, and it's slow in exactly the place users feel it, on save.

The two problems share a root. Both come from treating normalisation as something you do at query time rather than something that's true of the data. If the trimmed, upper-cased form is what identity actually means, then it should be stored as its own column, set whenever the original value is set. Now the unique index has something real to index, the lookup compares like with like, and the database can enforce the rule and serve the question from the same structure. One decision fixed both the correctness problem and the performance problem, which is rare enough to be worth noticing when it happens.

Keep the check anyway

None of this means deleting the application-level check. The index will happily refuse a duplicate, but what it says when it does is an error, and an error isn't a kind thing to hand a person who simply tried to add a contact that was already there. The check is what lets you say, "this one exists, did you want to edit it instead?" That's a better experience, and it costs one cheap indexed read.

What changes is the role each part plays. The check handles the polite, common case and explains itself. The index handles the impolite, rare case, the race, the retry, the second code path, and the application should catch that failure, roll back cleanly, and show the same friendly message. If both layers speak with one voice, the user never learns which of them did the work. I like that outcome. Good safety nets are invisible until they're needed.

The migration is where the honesty happens

The part of this that deserves the most respect is the one that gets the least attention: you can't create a unique index over data that already contains duplicates. The moment you decide the database should enforce the rule, you have to confront every time the application failed to. That means someone has to decide, deliberately, which of two supposedly identical records survives.

There's no neutral answer. Keep the active one over the inactive one? Keep the oldest? Merge them? Each choice has consequences for whoever was relying on the record that loses. In this case the system wasn't yet live for real customers, so the duplicates were test data and the choice was cheap: keep the active record, then the earliest, and retire the rest. Had the same table been holding years of production history, the same decision would have needed a conversation with the people who owned the data, a way to reverse it, and a good deal more caution. This is the cheapest it will ever be, which is the practical argument for putting the constraint in early rather than adding it after launch.

It's also a quiet reminder that a rule you've never enforced is a rule you've never tested against reality. Teams are often surprised by how many exceptions turn up the first time the database is asked to be strict.

What I'd carry forward

When someone tells me a thing must be unique, I've come to ask three questions. Where is that enforced if two requests arrive together? Is the thing being compared stored in the form it's compared in, or recomputed on every lookup? And what happens to the duplicates that already exist? If the answers are "in the application", "recomputed" and "we haven't looked", the feature works today and will disappoint someone later.

The wider point is about where you put trust. Application code is written by people who are careful, usually, and in a hurry, sometimes. A constraint in the database doesn't get tired, doesn't forget, and doesn't know about the clever shortcut someone added on a Friday afternoon. For anything where being wrong means a person has to untangle the mess by hand, I'd rather the system be strict on my behalf than polite on its own.