A not-null foreign key is a business rule, and this one was not true

Every invoice in the system was required to reference a booking, and so was every line on it. The column was not-null with a foreign key behind it, and the form would not submit without a booking selected.

That constraint had been written by somebody reasoning correctly from the common case. Most invoices do come from a booking. It took a requirements conversation to establish that the business also raises invoices that have no booking at all — a partner invoicing for their share, a supplier billing for mounting work, an advance, a credit. None of those are a booking, and the software’s answer to all of them was that they could not be recorded.

What a not-null constraint actually asserts

It says: this thing cannot exist without that thing. That is a claim about the world, not about the database, and it is enforced with more force than any other rule in your system. Application rules get worked around, permission checks get bypassed by an administrator, validation gets skipped by an import. A not-null foreign key stops everything, including your own migration scripts and your own support team at three in the afternoon when a client is waiting.

So it is worth being deliberate about which of your relationships get that treatment. The test I now use is simple: can you name a real situation, however rare, in which this record exists and that one does not? If you can, the column is nullable. If you cannot think of one but the domain expert has not been asked, the column is nullable until they have been.

The change here was three lines of migration — drop the not-null on the invoice’s booking reference, drop it on the line’s, and change the deletion behaviour on the line’s foreign key from restrict to set-null so that removing a booking leaves its historical invoice lines intact rather than refusing. That last detail matters more than it looks: with restrict, a booking created in error could never be deleted once anything had been billed against it, and the support answer becomes “mark it cancelled and ignore it forever”.

The same decision, made well, elsewhere

The purchase order module is the counter-example, and it was designed with this lesson already learned. Purchase orders are central to how this client bills — their samples were full of them — and the obvious design is to make the invoice hang off the purchase order.

The rule written into the specification instead was that the purchase order is optional everywhere. A convenience, never a gate. An invoice can be raised against one if it exists, or directly if it does not. No workflow is blocked by its absence.

That was not a technical preference. It came from the business: a client may release a purchase order, or may agree by email, or may agree verbally, and the work starts either way. Encoding “there must be a purchase order” would have been factually false about the business and would have produced exactly the same class of dead end as the booking constraint, but on the document their billing actually runs on.

The principle behind it is stated in the business flow document as a single sentence: build for the simple project, and make everything else optional. Multi-partner deals, purchase orders, agents, expenses and settlements are capabilities layered on top, never mandatory friction on the common case.

Where I would still get this wrong

The failure mode is not carelessness. It is that the constraint is written at the moment you understand the domain least, on the day you create the table, from a description of the common case. Everything you know at that point says the relationship is mandatory, because every example you have been shown has both halves.

I do not have a way to be smarter at that moment. What I do now instead is treat the first relaxation request as information rather than as a change request. When someone asks for a required field to be made optional, the useful question is not “can we” but “what else did we assume from the same description of the common case?” In this system that question found the same shape twice more — an approval workflow on expenses that no expense could ever reach, because the creating code set every expense as approved, and a site record that could be hard-deleted despite carrying photographs, bookings and money.

The cost of getting it right late

Relaxing a constraint after data exists is cheap. Everything already in the table satisfies a stricter rule than the new one, so the migration is additive, backwards compatible, and needs no backfill. The expensive direction is the other one — tightening a constraint later, when you have to find and repair every row that violates it.

That asymmetry is a reason to lean permissive at the schema level and strict at the application level. The application can refuse an invoice without a booking on the screens where that genuinely should not happen, and be persuaded otherwise next month. The schema cannot be persuaded of anything.

Put the rule where you can change it. A constraint in the database is the right home for something that must be true of every row for the life of the system — identity, tenancy, referential integrity. It is the wrong home for a claim about how a business usually works.