"INTEGER PRIMARY KEY" declares an alias for the rowid. It is always NOT NULL (it is the record number) and it is always and integer (it is not ducky-typed). Specifying NULL for the rowid causes the generation of the "next" rowid/record number. AUTOINCREMENT modifies "next" to mean "one greater than any rowid/record number that has ever been used in the table" rather than the normal "one greater than the current max rowid/record number".
You CANNOT insert a null into an INTEGER PRIMARY KEY (that is, obtain an actual NULL value in the table for that column), nor can you insert any value that is not an integer (that is, have the value of the rowid/record number for an inserted record be anything other than an integer). > -----Original Message----- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of Adam Devita > Sent: Monday, 18 April, 2016 13:45 > To: SQLite mailing list > Subject: Re: [sqlite] Primary key values can be NULL > > I had a similar thought, until I imagined > > Program uses sqlite DLL > The program creates dbs, and sometimes does inserts with null primary > keys. (Why is beyond the scope, it just does sometimes.) > > Upgrading the DLL would start making files in the new format, but the > program using the dll doesn't know that. It just starts failing. > > regards, > Adam > > On Mon, Apr 18, 2016 at 10:29 AM, David Raymond > <David.Raymond at tomtom.com> wrote: > > I don't mean to poke a busy thread with a possibly stupid newbie > question, but here goes. > > > > How is this that much different than say, the SQLITE_DEFAULT_FILE_FORMAT > compile option? (Pasting it here then continuing comment below) > > > > ----Text pasted here---- > > SQLITE_DEFAULT_FILE_FORMAT=<1 or 4> > > > > The default schema format number used by SQLite when creating new > database files is set by this macro. The schema formats are all very > similar. The difference between formats 1 and 4 is that format 4 > understands descending indices and has a tighter encoding for boolean > values. > > > > All versions of SQLite since 3.3.0 (2006-01-10) can read and write > any schema format between 1 and 4. But older versions of SQLite might not > be able to read formats greater than 1. So that older versions of SQLite > will be able to read and write database files created by newer versions of > SQLite, the default schema format was set to 1 for SQLite versions through > 3.7.9 (2011-11-01). Beginning with version 3.7.10, the default schema > format is 4. > > > > The schema format number for a new database can be set at runtime > using the PRAGMA legacy_file_format command. > > ----End quoted section---- > > > > The key point when introducing something new seems to be "as long as old > versions will know they shouldn't mess with it, then it's ok." So things > like CTE's can be added to the language as the old parser will gag on them > and not try to do something wrong with them and fail. But just straight up > changing the enforcement here would be bad, because the old version > wouldn't know that something new is going on. So although the above file > format option is intended for the physical structure of the file, could > for example we call file format 5 to be "same format, but will not null > primary key enforced." Then old versions would open it up, see file format > 5, and throw their hands up saying "I can't deal with this." And with new > versions it wouldn't be a changeable option, it would be "hey, once you > create this database file with this option, then you're stuck with that > enforcement forever." Looking at the dates above, format 4 was optional > for 5 years before it got a promotion to default, > si > > milarly a new value would have to be explicitly specified for n years > before anyone would have to worry about there being a "default build" that > would make something that could not be read by old versions. > > > > I know that actually using SQLITE_DEFAULT_FILE_FORMAT for this would be > very bad and not what it's intended for. But for example, there are 20 > bytes of "Reserved for expansion. Must be zero." in the header at the > moment. Do past or current versions throw up an error if those aren't zero > at the moment? Might it be time to appropriate a byte of reserved space > for new flags or an additional number? Or would that be the start of a > slippery slope? > > > > (As a newbie I apologize if this is just plain wrong, if I just created > the sound of hundreds of foreheads smacking into their desks in unison, or > if I just re-stirred a hornets' nest) > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > -- > -------------- > VerifEye Technologies Inc. > 151 Whitehall Dr. Unit 2 > Markham, ON > L3R 9T1 > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
