A check constraint made over-delivery impossible to record, and lorries do not read constraints
The purchase line carried a running total of how much had been delivered against it, and a check constraint saying that total may not exceed the ordered quantity. Order ninety kilograms, receive ninety, fine. Receive a hundred because the supplier sent a full sack rather than opening one, and the goods receipt fails with a constraint violation and no way forward.
The warehouse clerk’s options at that point are to record ninety and let ten kilograms of real stock exist nowhere, or to go and edit the purchase order so that it says a hundred — changing a document that was agreed with a supplier and may already have been invoiced — in order to make a receipt possible.
Both are wrong, and the second one is what people actually do, because the goods are on the floor and the job is to put them away.
Constraints encode facts, not policies
The check was written as if it were describing reality. It is describing a preference.
“You cannot receive more than you ordered” is not a property of the world. Suppliers over-ship, they round up to a whole packing unit, they send a replacement for a damaged item without a paper trail, and they occasionally deliver next month’s order early. Every one of those is a real event that a warehouse has to record, and a system that cannot record it stops being a record of what happened and becomes a record of what was supposed to happen.
A database constraint should encode something that cannot be otherwise, not something that should not be. The distinction is whether the violating state can exist in the physical world. Negative stock in a bin cannot — that is a fact and it deserves a constraint. Receiving more than ordered can, often does, and needs to be visible, which is a different mechanism: a flag, an exception report, a tolerance, an approval step. All of those let the truth into the database and then draw attention to it. A constraint just refuses the truth.
The test I now apply: if the rule were violated in the real world, would I want the system to record it or hide it? If record, it is not a constraint.
The rollup was the second problem
The delivered total on the purchase line is a cached sum. The real receipts live in a child table, one row per delivery, each with its own quantity, date and status.
That is the right structure — partial deliveries are normal and each one is an event with its own date — and the cached total is a reasonable optimisation for the listing screen, which needs to show outstanding quantities across hundreds of lines without a join and a group by.
The trouble is that the child table supports deletion. A receipt row can be removed, and when it is, nothing in the schema recomputes the parent’s total. There is no trigger, and the constraint on the parent cannot notice, because it only checks against the order quantity.
So there are two numbers for the same thing, one derived and one stored, and one of the operations the system allows silently makes them disagree. The failure is quiet: the line shows more delivered than the receipts add up to, the outstanding quantity is understated, and the buyer does not chase a delivery that never arrived.
Worse, the child table uses a boolean deleted flag while most of the schema uses a nullable deletion timestamp. Any generic helper that filters out deleted rows by looking for a null timestamp will include deleted receipts here, and any code that filters by the boolean will miss deletions elsewhere. Two conventions in one schema is not a style issue. It is a category of bug that only appears in code written to be reusable.
What to do with a rollup you cannot avoid
Three options, in order of how much I trust them.
Derive it and stop storing it. A sum over an indexed child table is fast enough far longer than people expect, and it cannot drift. Reach for this first and measure before rejecting it.
Store it and let the database maintain it. A trigger on insert, update and delete of the child recomputes the parent. Then the cache cannot disagree with the source, because the same statement that changes one changes the other. This costs write throughput and it is honest about what it is.
Store it, maintain it in the application, and add a reconciliation job that compares the stored value against the derived value and reports every difference. This is the weakest option and it is sometimes the only one available in an existing system. The job is not optional — a cache with no reconciliation is a number nobody can defend.
What was actually chosen here was the third, plus removing the check constraint and replacing it with an over-receipt flag on the line, so the receipt goes in and the line shows why it is unusual. The purchase order stays as it was agreed. The difference is visible to the buyer, who is the person with the authority to decide whether to argue with the supplier about it.
The status field that means two things
One more, because it is the same mistake at a smaller scale. The delivery status enum appears both on the purchase line and on each receipt row, with the same values.
On the receipt it means “did this particular delivery happen”. On the line it means “how much of this line is outstanding”. Those are different questions and the shared vocabulary invites a query that joins them and filters on the wrong one.
If two columns hold the same enum for different questions, at least one of them is named wrong. A line is partially received; a delivery is received or cancelled. Give them separate types, and the compiler and the database will both stop the confusion the naming created.
The rule
Before you add a check constraint, ask whether a person could walk into the building holding a counterexample. If they could, you are not writing a constraint. You are writing a policy, and policies belong somewhere they can be overridden by a human who signs their name to it.