SQLite Forum

Difference on NULL when WITHOUT ROWID
Some RDBMS implementations interpret PRIMARY KEY to mean that the underlying columns should be declared NOT NULL and throw a hissy fit if they are not.  That is, enforcement occurs at declaration time and there is no need for further or special processing at runtime.

Some RDBMS treat the PRIMARY KEY as changing the default for the nullability constraint of those columns to NOT NULL and throw a hissy fit only if you argue with them.

Some RDBMS process the PRIMARY KEY as a if it were a separate constraint quite independent of the declaration of the underlying column constraints and that the PRIMARY KEY constraint "special characteristics" is to be evaluated only at runtime.

DB2 falls into the first category and SQLite3 into the last.  You are free to add a NOT NULL constraint on the columns even though no hissy-fit is thrown, however, and it will be respected.

The reason that SQLite3 behaves in this fashion is because in rowid tables the incantation PRIMARY KEY is merely syntactic sugar for the incantation UNIQUE and if you want those columns to be NOT NULL then you must declare it so yourself.  The real primary key is the rowid and the rowid is never null.  This behaviour has been explained as an oversight but is maintained to keep bug-for-bug compatibility, also known as backwards compatibility.

However, for WITHOUT ROWID tables the PRIMARY KEY is actually the PRIMARY KEY of the table.  As such the PRIMARY KEY must uniquely identify a particular row, and every row must have exactly one PRIMARY KEY.  This precludes any of the PRIMARY KEY columns from being NULL at runtime, no matter what you may think of the matter or may have declared.