What a Nullable Column Is Really Telling You

Here's a small exercise I like to run with teams. Pick any table in your schema, find a nullable column — say, subscriptions.canceled_at — and ask three engineers what NULL means there. I have literally never gotten three matching answers. "Not canceled." "Canceled before we added the column." "It was NULL for imported rows, I think?" One column, three theories, and all of them are load-bearing in some query somewhere.

That's the thing about NULL: it isn't a value, it's an absence — and an absence can mean not yet, not applicable, unknown, never, or someone forgot. The column itself can't tell you which. When I see a schema where most columns are nullable, I don't see flexibility. I see a stack of modeling decisions that got deferred, one add_column at a time.

Nullable by default is how the debt accrues

Nobody sits down and decides "let's make everything nullable." It happens because that's the path of least resistance. In Rails, add_column :users, :phone, :string produces a nullable column unless you say otherwise. The migration runs cleanly on existing rows (they all get NULL), no backfill needed, everyone moves on. Multiply by five years of feature work and you get a table where NOT NULL is the exception.

Each of those columns skipped a question at birth: what should this be for rows that existed before it? NULL let us not answer. The debt isn't the NULL itself — it's the unanswered question, and it compounds, because every future query against that column has to answer it instead, usually with a guess.

Generated code deserves a special mention here, because AI assistants have absorbed this exact habit from the corpus they learned on. Ask one for a migration and you'll typically get nullable columns unless you explicitly request constraints — the training data was mostly quick-start tutorials, and quick-start tutorials don't backfill. When you review a generated migration, the nullability of every column is a decision the tool didn't actually make. Somebody has to, and in review, that somebody is you.

Three-valued logic: where the bill arrives

SQL comparisons don't return true or false; they return true, false, or unknown, and NULL is how you buy a ticket to the third one. NULL = NULL is not true. NULL <> 5 is not true. WHERE clauses keep rows only when the condition is true — unknown gets dropped, silently.

The classic production incident looks like this:

-- "Show me users who aren't on the beta plan"
SELECT * FROM users WHERE plan <> 'beta';
-- Users with plan = NULL are excluded. They aren't on the beta plan.
-- They also aren't in your results. Nobody gets an error.

And its meaner sibling:

SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM bans);
-- If bans.user_id contains a single NULL,
-- this returns zero rows. Every time. By spec.

Add the quieter ones: COUNT(column) skips NULLs while COUNT(*) doesn't, so two "counts" of the same table disagree; AVG ignores NULLs, so your average price excludes exactly the rows with missing prices; GROUP BY lumps all NULLs into one bucket as if they were the same value, which is the one thing NULLs definitionally aren't.

None of these are bugs in the database. They're the semantics of "unknown" applied consistently. The bug is upstream, in a schema that used "unknown" to mean "no."

What the nullable column is trying to say

When I find a nullable column, I treat it as a message from past engineers and try to decode it. It's usually one of these:

"There's a state machine hiding here." shipped_at IS NULL meaning "not shipped" works — until you need "label printed" or "returned," and now NULL means several things. The honest model is an explicit status column (with a CHECK constraint), with the timestamps as supporting detail rather than the source of truth.

"There are two entities in this table." When columns travel in a nullable pack — company_name, tax_id, billing_contact, all NULL together for individual accounts — the table is two tables wearing a trench coat. Splitting business_profiles out gives every column a chance to be NOT NULL within its own home.

"We didn't backfill." The column is meaningful for new rows and NULL for history. Sometimes acceptable! But say so — a schema comment or a data dictionary note ("NULL = predates 2025-11 rollout") turns a trap into a footnote.

"Empty and unknown got conflated." For text especially: is NULL different from ''? Pick one representation, constrain the other out of existence, and a whole class of COALESCE(TRIM(...)) incantations disappears.

When NULL is the right answer

I don't want to leave you thinking NULL is a moral failing. It's the correct representation for genuinely optional or genuinely unknown facts: users.deactivated_at for a user who has never deactivated, a referrer_id for someone who arrived without a referral, a middle_name that truly doesn't exist. The test I use: can I finish the sentence "NULL in this column means ___" with exactly one clause, and would every engineer on the team finish it the same way? If yes, NULL is fine — write the sentence down. If the sentence needs an "or," you've found the modeling debt.

And when you do keep a nullable column, make your migrations honest about the ones that shouldn't be:

# Adding a column that must always have a value:
add_column :orders, :currency, :string, null: false, default: "USD"

# Or, on a big table: add nullable, backfill in batches,
# then lock the door behind you:
change_column_null :orders, :currency, false

The questions to ask in review

• For every new nullable column: "What does NULL mean here — not yet, not applicable, or unknown? Pick one."
• For every generated migration: remember that nullability was defaulted, not decided. Decide it.
• Grep your queries for <>, NOT IN, and aggregates touching nullable columns — that's where three-valued logic collects.
• Columns that are NULL together are a table trying to escape. Let it.
• NULL is legitimate when its meaning is singular and shared. One column, one sentence, no "or."

A schema where every NULL has a written, agreed meaning is one of those quiet luxuries — like good coffee in the office. Nobody puts it on the roadmap, and everybody feels it every day.