What do NOT NULL and DEFAULT actually do, and when do you need one without the other?
They do two different jobs, and one of them is about the value and the other one is about the column being there at all.
NOT NULL says that the column must always have a value. DEFAULT says what that value should be when you do not give one.
CREATE TABLE events (
id INTEGER PRIMARY KEY,
kind VARCHAR(40) NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'new',
retry_count INTEGER NOT NULL DEFAULT 0,
note VARCHAR(200)
);
So when do you need one without the other?
You need NOT NULL without a default when there is no sensible value to fall back on. The kind of an event has to be given every time, because "unknown event" is not a thing.
You need a default without NOT NULL when the column is genuinely optional and the missing value is a real state. The note column above can be missing, and there is no point in filling it with an empty string, because an empty note and no note are different.
And you often want both, which is the usual combination. The status column always has a value, and when you do not say which one, it is 'new'.
So the rule to remember is this. NOT NULL is about whether the value can be missing, and the default is about what to use when it is left out.
The next two questions in this topic look at when the default is worked out and at what happens with an explicit null.