NOT NULL and DEFAULT Constraints

NOT NULL is a rule: the column must hold a value on every row. DEFAULT is a fill-in: if the INSERT does not supply a value for the column, the engine substitutes the expression you declared. They are independent, and the combinations are all meaningful.

NOT NULL without DEFAULT means the caller must supply the value on every insert, and the insert fails with a constraint error if they do not. That is right for a column whose value genuinely cannot be invented — a person's name, an order's total, an account's currency.

DEFAULT without NOT NULL means the column has a sensible fallback but a caller may still override it, and may still explicitly store NULL. That is right for a column where a default is helpful but NULL is a legitimate value — the tier of a customer, which defaults to 'standard' but might be recorded as unknown.

Both together are the most common combination, and they are subtly different things. The column always holds a value, and if the caller does not supply one, the default is used. This is what you want for a status column that starts at 'new' and must never be blank.

The important detail is that a DEFAULT only fires when the column is omitted from the INSERT or given the DEFAULT keyword. Supplying an explicit NULL is a different thing entirely, and how it behaves depends on the column's NOT NULL declaration — which is the question that trips people up.

A DEFAULT expression is evaluated at insert time, once per row, so every row gets its own value rather than a value frozen when the table was created.

CREATE TABLE events (
  id INTEGER PRIMARY KEY,
  kind VARCHAR(40) NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'new',
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

INSERT INTO events (kind) VALUES ('signup');
INSERT INTO events (kind, status) VALUES ('purchase', 'paid');

SELECT id, kind, status, created_at IS NOT NULL AS has_timestamp
FROM events
ORDER BY id;

Two rows come back. The first has status 'new' because the column was omitted and the default fired; the second has status 'paid' because the caller supplied it. Both have a timestamp, because datetime('now') was evaluated when each row was inserted.

Two consequences of the per-row evaluation. The two rows could have different timestamps, because they were separate statements. And within a single multi-row INSERT, every row gets its own evaluation of the expression — which is why a DEFAULT that calls a volatile function like random() gives you a different value per row rather than one shared value.

The other thing worth knowing is that changing the default later does not change the rows that are already there. If you ALTER the table to set the default to 'pending', existing rows keep the value they were written with, and only future inserts get the new default. That is the behaviour you want, and it is the reason a backfill is a separate, explicit step rather than something you can wave at with a schema change.

One syntax detail: in SQLite, a DEFAULT that calls a function must be wrapped in parentheses — DEFAULT (datetime('now')). A bare constant such as DEFAULT 'new' needs no parentheses. PostgreSQL and MySQL do not require the parentheses, though they accept them.

Yes, and the combination is the most useful one: the column always holds a value, and the database supplies that value when you do not.

What happens with an explicit NULL depends on the column's NOT NULL declaration, and this is the part people get wrong. If the column is NOT NULL, inserting an explicit NULL fails with a constraint violation — the default does not rescue it, because the default only applies when the value is absent, not when it is deliberately null. If the column allows NULL, an explicit NULL is stored as NULL and the default is ignored entirely.

There is a third behaviour in some engines. SQL Server's ANSI_WARNINGS setting decides whether an explicit NULL into a NOT NULL column raises an error or is silently converted to the default value. The ANSI-compliant behaviour, and the one PostgreSQL, MySQL and SQLite all implement, is to raise an error. That is the behaviour to rely on, because silently substituting a default hides a bug in the calling code.

For the interview, the clean summary is: DEFAULT fills in a value that was not supplied; NOT NULL forbids a value that was supplied as NULL. They operate on different things, and the combination means "never null, and here is what to use instead".

One practical note on how to write the insert. If your application is generating the SQL, prefer omitting the column entirely over passing the keyword DEFAULT — they have the same effect, but omitting it makes it obvious to a reader that the caller did not have a value, and it means the insert keeps working if you later add a second default-bearing column. And if your framework always sends every column, make sure it sends the DEFAULT keyword rather than NULL for columns it does not populate, or the NOT NULL constraint will reject the row.