A table column is declared NOT NULL with a DEFAULT value. Explain the difference between an INSERT that omits that column entirely and one that explicitly supplies NULL for it.
answer
- mentioned vs omitted decides everything
- explicit NULL bypasses DEFAULT → NOT NULL rejects
- VALUES (DEFAULT) / SET col = DEFAULT requests it
- ORMs emit full column list → default never fires
- default evaluated per row at write time
basics
~20 sOmitting the column makes the engine apply the DEFAULT, so the insert succeeds. Explicitly writing NULL is a real value being supplied — the DEFAULT is not consulted, and the NOT NULL constraint rejects the row.
solid answer
~60 sDEFAULT and NOT NULL solve different problems, and the distinction turns on whether the column is *mentioned* in the statement. If the column is absent from the INSERT's column list, the engine substitutes the declared DEFAULT (and NULL if none is declared). If the column is present with the value NULL, the application has supplied a value; DEFAULT is not consulted at all, so a NOT NULL column rejects the row with a constraint violation. The same holds for UPDATE: `SET col = NULL` fails on a NOT NULL column, and there is no implicit fallback to the default — you need the explicit `DEFAULT` keyword to request it. This bites in practice with ORMs and generated SQL. Many ORMs emit every mapped column on insert, so an unset field becomes an explicit NULL and the column default never fires — you see "null value in column violates not-null constraint" on a column that has a perfectly good default. Fixes are to mark the field as insertable-excluded/generated so it is omitted, or to set the value in the application.
go deeper
State the mention rule clearly: omitted uses the default, explicit NULL is a supplied value and fails NOT NULL.
Add the explicit DEFAULT syntax, that UPDATE never implicitly defaults, and why ORM-generated full-column inserts break defaults.
Discuss the practical fixes (dynamic insert, insertable=false, triggers), loader null/default handling, and that changing a default is forward-only.
Decide where defaulting belongs — database, ORM, or service layer — so every write path is consistent, and treat NOT NULL DEFAULT as a modelling statement about whether an unset state is meaningful.
## Two constraints doing two jobs `NOT NULL` is a validity rule: the stored value may never be NULL, and any write attempting one fails. `DEFAULT` is a *substitution* rule: when a write does not supply a value for the column, use this expression instead. They are frequently declared together, but neither implies the other. A nullable column can have a default; a NOT NULL column can have none (in which case every insert must supply a value). ## The mention rule The engine's decision is mechanical: is the column named in the statement? - **Not mentioned** → the DEFAULT expression is evaluated and the result stored. If no DEFAULT is declared, the implicit default is NULL, which then meets the NOT NULL check head-on and fails. - **Mentioned with any value, including NULL** → that value is stored. NULL is a supplied value, not an absence, so DEFAULT is bypassed and NOT NULL rejects it. SQL provides explicit syntax for the substitution: `INSERT ... VALUES (DEFAULT)` and `UPDATE ... SET col = DEFAULT` request the default deliberately, and `INSERT INTO t DEFAULT VALUES` fills every column from its default. Multi-row inserts follow the same rule per row, so one row may use DEFAULT while another supplies a literal. UPDATE has no notion of "omitted means default" beyond the explicit keyword: a column not mentioned in `SET` simply keeps its current value; it is not reset to the default. ## Why this trips people up The common production symptom is a not-null violation on a column that obviously has a default — `created_at`, `status`, a tenant id. The cause is almost always generated SQL. ORMs and query builders frequently emit the full column list for every insert; a field left unset in the object becomes an explicit NULL in the statement, and the default never runs. The remedies are to exclude the column from the insert (JPA `@Column(insertable = false)` plus a re-read, or `@Generated`/`@DynamicInsert`-style options that omit unset columns), to populate the value in application code, or to move the value generation into the database in a way that runs regardless — for instance a BEFORE-INSERT trigger, which fires on the supplied NULL where DEFAULT would not. Bulk loaders have the mirror image of the problem: a CSV with an empty field may be interpreted as an empty string, as NULL, or as "use the default", depending on the loader's null/default options. Verify which, or you will store empty strings in a column you believed was defaulted. ## When the default expression is evaluated A default is evaluated at write time, per row, in the writing transaction — it is not a stored constant copied at DDL time (except in the metadata-only fast-fill case for existing rows). So a volatile default such as the current timestamp yields the time of the insert, and a default drawing from a sequence advances per row. Changing a column's default later affects only subsequent inserts; existing rows are untouched, because they were already materialised with the old value. ## Designing with them The pairing that usually reflects intent is `NOT NULL DEFAULT <value>` for columns with a meaningful "unset" state — a status of `'pending'`, a counter of `0`, a boolean flag of `false`. This keeps every row queryable without NULL-handling and lets writers ignore the column. Reserve nullable columns for genuinely unknown or inapplicable values, and remember that a default cannot rescue a caller who explicitly passes NULL. If NULLs must be tolerated but normalised, that is application or trigger work, not something DEFAULT will do.
- Your ORM keeps producing not-null violations on a column that has a database default. What is happening and how do you fix it?The ORM includes every mapped column in the INSERT, so an unset field is sent as an explicit NULL and the default is never consulted. Fix it by making the ORM omit the column — dynamic-insert or insertable=false mappings, with a re-read to pick up the generated value — or by setting the value in application code. A BEFORE-INSERT trigger also works because it fires even when NULL is supplied.
- If you change a column's DEFAULT with ALTER TABLE, what happens to rows already stored?Nothing. The default is applied at write time, so existing rows keep the values they were materialised with, whether from the old default or from explicit inserts. Only inserts after the change use the new default. Backfilling old rows requires an explicit UPDATE.
A form with a pre-filled field: leave it blank and the pre-filled value stands; erase it and write 'none' and you have overridden the pre-fill with your own answer.
saying these in an interview costs you the question
- Believing that passing NULL falls back to the column's DEFAULT
- Thinking NOT NULL and DEFAULT are the same declaration or that one implies the other
- Assuming an UPDATE that omits a column resets it to the default
- Expecting an ALTER of the default to retroactively change existing rows
- Saying a default is a constant fixed at DDL time even for volatile expressions like the current timestamp