Validation error after deleting a record: unique constraint

If adding a record fails with a bare “Validation error” after you have deleted another, the usual cause is a unique constraint: the deleted record is only marked as deleted, so it still holds its code, and the database refuses the new one. The first check is whether the code or name being refused belongs to a deleted record. The second is whether your system erases records or only hides them.

Here is how that looked at one business, and what fixing it took.

You retire a board from the list, because the lease ended. Next week you add a new one, and the screen says “Validation error”. That is the whole message. It does not say which field, which rule, or what to change.

This was live on production. It was found not by a user but while repairing the system’s test suite, and the log does not record that anyone had hit it. But the effect is plain: after any board had been deleted, adding another one failed, and nothing told the person why.

What was actually going on

Deleting a board in this system does not remove its row. It marks it deleted and keeps it, which is how you keep history for invoices and photographs. A deleted board therefore keeps its site code.

The site code has a rule that no two boards can share one. That rule knows nothing about deletion. And the two routines that hand out the next code looked only at boards that were still live. So they found the highest live code and gave out the next one, which a deleted board was still holding. The database refused, correctly.

The refusal then vanished into a bad error message. The database library reports every failed rule as the same two words, “Validation error”, naming neither the field nor the rule. That is why the first fix was wrong. We assumed the application’s own “deleted” flag was the cause and had to follow the evidence to how the deletion was actually stored.

The same test repair turned up fifty-six failures. Comparing the failing test names with a checkout from before the session showed every one of them predated it. Most were tests checking behaviour the product had deliberately moved past, and one was a live bug.

What we changed

Code generation now counts deleted boards as well as live ones, so a retired board’s code is never handed out again. The interface that creates a site also now names the field and the rule that failed, instead of the bare message.

Afterwards the suite passed 296 of 296, against 226 before. The two changes are small. The value was in noticing that a bug this visible had been sitting behind a wall of unrelated red tests.

What it did not fix

The clearer error message was wired into creating a site only. Other routes that can fail a rule still report the bare message until someone does the same for them.

Nothing here brings a deleted board’s code back for reuse. That was not asked for, and reusing a code that invoices and photographs still point to would create a different problem.

How to find and stop this in your own system

1. Delete a test record, then add a new one and check it succeeds.
2. Ask your supplier whether deleting removes the row or only hides it.
3. If it hides it, ask which codes, names and numbers must be unique, and whether hidden rows still count.
4. Ask whether the routine that hands out the next code counts hidden rows too.
5. Check that when a rule is broken the screen names the field and the rule, not only “something went wrong”.

Where this ends up

Adding a board is the first thing anybody does in AdBoard, the system that runs for Gold Sign Media, an outdoor media operator. It now works after a deletion, and when a rule is broken the screen says which.