Difference on NULL when WITHOUT ROWID
(1) By anonymous on 2020-12-30 13:45:45 [link] [source]
.version create table a( id text primary key null ) without rowid; insert into a values('whatever'); insert into a values(null); create table b( id text primary key null ); insert into b values('whatever'); insert into b values(null);
Both tables the same except for
WITHOUT ROWID for table
I get following error for table
SQLite 3.34.0 2020-12-01 20:57:24 1c7e6243ec89b4fa3599eaebbe1021bdca5b0359825e1ce582db1b1f92067fac msvc-1800 Error: near line 6: NOT NULL constraint failed: a.id
Why do they behave differently when both are primary keys that accept nulls?
(2) By David Raymond (dvdraymond) on 2020-12-30 13:58:06 in reply to 1 [source]
Without rowid tables must have non-null primary key fields.
NOT NULL is enforced on every column of the PRIMARY KEY in a WITHOUT ROWID table. This is in accordance with the SQL standard. Each column of a PRIMARY KEY is supposed to be individually NOT NULL. However, NOT NULL was not enforced on PRIMARY KEY columns by early versions of SQLite due to a bug. By the time that this bug was discovered, so many SQLite databases were already in circulation that the decision was made not to fix this bug for fear of breaking compatibility. So, ordinary rowid tables in SQLite violate the SQL standard and allow NULL values in PRIMARY KEY fields. But WITHOUT ROWID tables do follow the standard and will throw an error on any attempt to insert a NULL into a PRIMARY KEY column.
(3) By Keith Medcalf (kmedcalf) on 2020-12-30 18:47:54 in reply to 1 [link] [source]
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.