Validation error after deleting a record: unique constraint
After you delete a record, adding a new one can fail because the deleted row still holds its unique code. How to check yours, and how we fixed it.
Topic
Data Modelling
Schemas, constraints and keys, and the rules they are really enforcing.
35 articles
About this topic
Most of the expensive bugs in an operational system are modelling decisions that were made quickly and then lived for years. Whether a balance is stored or derived, whether a soft delete has a defined answer for every child row, whether a foreign key is a business rule or a convenience — none of these announce themselves at the time, and all of them are discovered later by an accountant.
The recurring theme here is that a constraint is a claim about the world, and it is worth checking the claim before enforcing it. A check constraint that made over-delivery impossible to record did not stop lorries arriving with more than the order; it stopped the system being able to say so. That is worse than the problem it was written to prevent.
Attendance report missing an employee who marked daily
An employee with 404 attendance records was missing from the depot report because he was not on the staff-depot list. How to check yours.
Why a notice sent to one region reached the wrong staff
A region notice reached 11 of 500 people because two region lists shared one name. How to test yours, and what is still open.
Former staff still on your team list? The offboarding check
Leavers still show in team screens and sales reports when offboarding removes them from one record but not the other. How to check, and what the fix took.
Photo tagging app: relabelling one photo deleted another
Relabelling a handover photo deleted the photo that held the label, and the next click failed six times in a row. How to check your own photo tagging.
Billboard campaign report with photos in the wrong slots
A campaign report printed the close-up in the wide-shot slot because one field did three jobs. What to check in your reports, and what we changed.
Reward points app for kids: stopping repeat payouts
A kids' rewards app paid points for every retake of the same quiz. Pay for the work, not the request: how to design rewards that cannot be farmed.
Reconcile Excel with your system: billboard locations
Two Excel files and a database disagreed on 34 of 47 board locations, with no site code to join them. How to reconcile them safely, and what we did.
Dealer scheme closed a day early? The last-day bug
A booking scheme told a dealer it had closed on its last day because the end date was compared as text. How to check yours and what we fixed.
Keeping a legal register current when laws are replaced
A legal register reads as confident after the law beneath it is replaced. How to keep one current, and what happened to our 184-obligation register.
Same item listed dozens of times and the app is slow
A product screen repeated each crop 36 times and took 2.5 seconds to open, because a table held every combination. How to check yours for duplicates.
Phone number already exists, but nobody has it
The portal said a number was in use though no one had it. Blank accounts made at login held it, and a stray space locked an employee out. How to check.
WhatsApp Business API sent to a stranger: phone formats
Foreign phone numbers were cut to ten digits and given an Indian prefix, so a UK customer's booking reached a stranger. How to check your own numbers.
Invalid phone numbers in your CRM: how to find and fix
9,300 of 753,994 contacts held unusable phone numbers because validation tested only one end. How we counted them, fixed them, and how to check yours.
AI chatbot for company data: can you trust the answers?
An AI chatbot over company data guesses wherever the database hides how records connect. 2,667 approved orders would have been reported as cancelled.
A database migration that passes every test can fail live
A database migration can pass every test and still fail in production when development runs a newer database version. How we caught it, and what to check.
Can invoice numbers have gaps? Stop drafts leaving holes
Invoice numbers get gaps when a draft takes a number and is then abandoned. How to check yours, and how we number at the moment of issue instead.
What a compliance register holds beyond staff certificates
A compliance register is a rule that produces a list of obligations, not a tick-list. What it should hold beyond certificates, from a register of 184 obligations.
E-invoice portal down? Does your shop have to stop billing?
When a tax portal is slow or down, a till built the obvious way freezes. How e-invoicing should work at the counter, and what to ask your supplier.
Immutable audit log: proving what a record said in March
An auditor asks what a record said in March and who changed it. A rule staff follow is a promise; a database that refuses edits is evidence.
Duplicate invoice number error: why the till won't bill
A till refused to bill because invoice numbers came from a clock and repeated every 16 minutes across customers. Causes, and how to check yours.
Low stock alert not working: the screen was empty
A low-stock screen showed nothing while stock ran down, because no item had a reorder level. Why blank is not zero, and how to check yours.
Remove a device from an account: why it still works
Removing a child from an app left her tablet signed in, because removal only hid the record. How to check your own system, and what the fix was.
Inventory locations: one building as factory, store and shop
Stock software that gives each location one type forces paper transfers inside one building. Describe a site by what it can do instead.
How to discontinue a product without losing sales history
Retiring a product left orphaned prices and variants. Settings and totals follow the product; records of what happened never do. How to retire one safely.
Quote breakdown: how to show why a price was what it was
A customer asks why a quote came to that price weeks later, and rates have moved. Storing the working lets you show the breakdown; keeping only the total cannot.
Over delivery: what to do when the system won't receive it
Systems that refuse to receive more than the purchase order push clerks to edit the order. Handle over-delivery with a flag, tolerance or approval.
Manufacturing yield: which pass is losing you product?
Yield recorded per batch hides a bad pass. Record output per run to see which pass loses product, and expect the report to start from a date.
Promotion ended at the wrong hour? UTC versus local time
A promotion and free trials ended five and a half hours off because the software used UTC, not the business clock. How to check which clock yours uses.
Tenant isolation: keeping one customer's data apart
To keep one customer's data from mixing with another's in a shared database, enforce it in the database, not only the app. Two designs and the gaps of each.
Reward points credited twice: why the balance is unclear
A child was paid points twice for one task and nobody could say why. Why a stored total can't be trusted, and how a ledger of entries fixes it.
How to record a booking that runs until further notice
A booking with no end date breaks availability if the date is left blank. How to record a rolling contract so every date question still works.
Invoice without a booking: advances, credits, partner bills
Software that insists on a booking cannot record an advance, a credit, or a partner's or supplier's bill. What to check, and how we relaxed the rule.
Soft delete vs hard delete: a removed driver kept showing
Eighteen trips still showed a driver who had been removed, and seven lost the removed driver's identity. Why a soft delete caused it, and what to check.
Revenue share vs commission: partner and agent payouts
A partner's profit share and an agent's commission both go out monthly but differ in basis, period and scope. Why they must never be totalled together.